PLSQL , how to store ORA-06512 into some variable?ORA-04091 on Create or Replace Trigger xxx Before Insert of Update on xxxHow does line numbering work in an Oracle trigger?Parse Oracle Exception Error Message in PHPDB Query Error: java.sql.SQLException: ORA-01422 exact fetch returns more than requested number of rows ORA-06512:PL SQL trigger not functioningORA-20001, ORA-06512: at line 59 ORA-06512 exception errorTrigger insert errorsORA-00933: SQL command not properly ended in stored procedureORA-04088: error during execution of trigger 'OES2.T_UPDATE_ORDERS_GROSS'Oracle OCI jdbc driver traps ORA-01403: no data found
Can a US President, after impeachment and removal, be re-elected or re-appointed?
Why is this photograph shot with Delta 400 and developed with D76 1+1 so grainy
How to efficiently shred a lot of cabbage?
What is the source of this clause, often used to mark the completion of something?
Circle symbol compatible with square and triangle
Boots or trail runners with reference to blisters?
What does "in official capacity" mean?
Why would anyone ever invest in a cash-only etf?
How do I make my photos have more impact?
Was the Psych theme song written for the show?
What are the closest international airports in different countries?
On what tickets or flights are Flying Blue XP earned?
How can Paypal know my card is being used in another account?
Security measures that could plausibly last 150+ years?
Why does the Rust compiler not optimize code assuming that two mutable references cannot alias?
Exploiting the delay when a festival ticket is scanned
What were the first chips with hardware support for SPI?
How do you deal with characters with multiple races?
What is my clock telling me to do?
Why tantalum for the Hayabusa bullets?
Does Ubuntu reduce battery life?
left ... right make different sizes in numerator and denominator
How do discovery writers hibernate?
What is a good example for artistic ND filter applications?
PLSQL , how to store ORA-06512 into some variable?
ORA-04091 on Create or Replace Trigger xxx Before Insert of Update on xxxHow does line numbering work in an Oracle trigger?Parse Oracle Exception Error Message in PHPDB Query Error: java.sql.SQLException: ORA-01422 exact fetch returns more than requested number of rows ORA-06512:PL SQL trigger not functioningORA-20001, ORA-06512: at line 59 ORA-06512 exception errorTrigger insert errorsORA-00933: SQL command not properly ended in stored procedureORA-04088: error during execution of trigger 'OES2.T_UPDATE_ORDERS_GROSS'Oracle OCI jdbc driver traps ORA-01403: no data found
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm using flyway for data migration using Jenkins. I want to enable and disable triggers when data correction code is executing.
I managed to handle exception and my enable disable triggers functionality working fine. However I want to print exact line number when exception occurs.
But line number is printed where RAISE_APPLICATION_ERROR is called.
How can I store Line number like v_errorMsg is stored? I want to see Exact Line number in script in Jenkins console.
Below is my Sample PLSQL Script executed using flyway.
DECLARE
v_errFlag BOOLEAN := FALSE;
v_errorMsg VARCHAR(400);
v_dealId NUMBER;
BEGIN
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('disable');
--Data Correction starts
-- Success Test
--SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE ROWNUM=1;
-- Error Test
SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE DEAL_ID=-1;
--Data Correction ends
EXCEPTION
WHEN OTHERS THEN
v_errFlag := TRUE;
v_errorMsg := SQLERRM;
END;
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('enable');
SYS.DBMS_OUTPUT.PUT_LINE('Triggers Enabled');
IF v_errFlag THEN
RAISE_APPLICATION_ERROR(-20001,'Data Correction Failed :'||v_errorMsg);
ELSE
SYS.DBMS_OUTPUT.PUT_LINE('Data Correction Completed Successfully');
END IF;
END;
END;
/
Sample output for error scenario
ORA-20001: Data Correction Failed :ORA-01403: no data found
ORA-06512: at line 25
oracle plsql
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
add a comment |
I'm using flyway for data migration using Jenkins. I want to enable and disable triggers when data correction code is executing.
I managed to handle exception and my enable disable triggers functionality working fine. However I want to print exact line number when exception occurs.
But line number is printed where RAISE_APPLICATION_ERROR is called.
How can I store Line number like v_errorMsg is stored? I want to see Exact Line number in script in Jenkins console.
Below is my Sample PLSQL Script executed using flyway.
DECLARE
v_errFlag BOOLEAN := FALSE;
v_errorMsg VARCHAR(400);
v_dealId NUMBER;
BEGIN
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('disable');
--Data Correction starts
-- Success Test
--SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE ROWNUM=1;
-- Error Test
SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE DEAL_ID=-1;
--Data Correction ends
EXCEPTION
WHEN OTHERS THEN
v_errFlag := TRUE;
v_errorMsg := SQLERRM;
END;
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('enable');
SYS.DBMS_OUTPUT.PUT_LINE('Triggers Enabled');
IF v_errFlag THEN
RAISE_APPLICATION_ERROR(-20001,'Data Correction Failed :'||v_errorMsg);
ELSE
SYS.DBMS_OUTPUT.PUT_LINE('Data Correction Completed Successfully');
END IF;
END;
END;
/
Sample output for error scenario
ORA-20001: Data Correction Failed :ORA-01403: no data found
ORA-06512: at line 25
oracle plsql
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
add a comment |
I'm using flyway for data migration using Jenkins. I want to enable and disable triggers when data correction code is executing.
I managed to handle exception and my enable disable triggers functionality working fine. However I want to print exact line number when exception occurs.
But line number is printed where RAISE_APPLICATION_ERROR is called.
How can I store Line number like v_errorMsg is stored? I want to see Exact Line number in script in Jenkins console.
Below is my Sample PLSQL Script executed using flyway.
DECLARE
v_errFlag BOOLEAN := FALSE;
v_errorMsg VARCHAR(400);
v_dealId NUMBER;
BEGIN
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('disable');
--Data Correction starts
-- Success Test
--SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE ROWNUM=1;
-- Error Test
SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE DEAL_ID=-1;
--Data Correction ends
EXCEPTION
WHEN OTHERS THEN
v_errFlag := TRUE;
v_errorMsg := SQLERRM;
END;
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('enable');
SYS.DBMS_OUTPUT.PUT_LINE('Triggers Enabled');
IF v_errFlag THEN
RAISE_APPLICATION_ERROR(-20001,'Data Correction Failed :'||v_errorMsg);
ELSE
SYS.DBMS_OUTPUT.PUT_LINE('Data Correction Completed Successfully');
END IF;
END;
END;
/
Sample output for error scenario
ORA-20001: Data Correction Failed :ORA-01403: no data found
ORA-06512: at line 25
oracle plsql
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
I'm using flyway for data migration using Jenkins. I want to enable and disable triggers when data correction code is executing.
I managed to handle exception and my enable disable triggers functionality working fine. However I want to print exact line number when exception occurs.
But line number is printed where RAISE_APPLICATION_ERROR is called.
How can I store Line number like v_errorMsg is stored? I want to see Exact Line number in script in Jenkins console.
Below is my Sample PLSQL Script executed using flyway.
DECLARE
v_errFlag BOOLEAN := FALSE;
v_errorMsg VARCHAR(400);
v_dealId NUMBER;
BEGIN
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('disable');
--Data Correction starts
-- Success Test
--SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE ROWNUM=1;
-- Error Test
SELECT DEAL_ID INTO v_dealId FROM USR.DEAL WHERE DEAL_ID=-1;
--Data Correction ends
EXCEPTION
WHEN OTHERS THEN
v_errFlag := TRUE;
v_errorMsg := SQLERRM;
END;
BEGIN
USR.ENABLE_DISABLE_TRIGGERS('enable');
SYS.DBMS_OUTPUT.PUT_LINE('Triggers Enabled');
IF v_errFlag THEN
RAISE_APPLICATION_ERROR(-20001,'Data Correction Failed :'||v_errorMsg);
ELSE
SYS.DBMS_OUTPUT.PUT_LINE('Data Correction Completed Successfully');
END IF;
END;
END;
/
Sample output for error scenario
ORA-20001: Data Correction Failed :ORA-01403: no data found
ORA-06512: at line 25
oracle plsql
oracle plsql
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
asked Mar 26 at 21:24
RajendraRajendra
9792 gold badges9 silver badges23 bronze badges
9792 gold badges9 silver badges23 bronze badges
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can add dbms_utility.format_error_backtrace after exception when others then
exception when others then
begin
dbms_output.put_line( dbms_utility.format_error_backtrace );
v_errFlag := true;
v_errorMsg := sqlerrm;
end;
to display the line number where error occured.
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55366413%2fplsql-how-to-store-ora-06512-into-some-variable%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can add dbms_utility.format_error_backtrace after exception when others then
exception when others then
begin
dbms_output.put_line( dbms_utility.format_error_backtrace );
v_errFlag := true;
v_errorMsg := sqlerrm;
end;
to display the line number where error occured.
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
add a comment |
You can add dbms_utility.format_error_backtrace after exception when others then
exception when others then
begin
dbms_output.put_line( dbms_utility.format_error_backtrace );
v_errFlag := true;
v_errorMsg := sqlerrm;
end;
to display the line number where error occured.
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
add a comment |
You can add dbms_utility.format_error_backtrace after exception when others then
exception when others then
begin
dbms_output.put_line( dbms_utility.format_error_backtrace );
v_errFlag := true;
v_errorMsg := sqlerrm;
end;
to display the line number where error occured.
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
You can add dbms_utility.format_error_backtrace after exception when others then
exception when others then
begin
dbms_output.put_line( dbms_utility.format_error_backtrace );
v_errFlag := true;
v_errorMsg := sqlerrm;
end;
to display the line number where error occured.
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
answered Mar 26 at 22:30
Barbaros ÖzhanBarbaros Özhan
19.9k7 gold badges16 silver badges35 bronze badges
19.9k7 gold badges16 silver badges35 bronze badges
add a comment |
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55366413%2fplsql-how-to-store-ora-06512-into-some-variable%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
