| PL/SQL Function fails without an exception [message #655994] |
Mon, 19 September 2016 11:08  |
 |
moverdear
Messages: 3
Registered: September 2016
|
Junior Member |
|
|
We have the exact code running at several sites and it has been running for years. New machines and whatnots have happened at one site. Now we are having a failure on running a stored procedure at that one site. I do believe they have a permission problem, but we have to convince them.
Anyway, my procedure fails and there is no exception being thrown. Here is the layout.
We have a shell script that runs a stored procedure which in turn calls a function.
#1 The very first thing the function does is log a message to a trace table that the function has been initiated. This works great.
#2 The code does a delete from the trace table with a specific where clause ( module = 'MISSING DATA' )
#3 The function logs a message to the TRACE table that the deletion was completed ----------- This message never appears.
The first insertion into the trace table works and the 3rd never shows up. It seems the deletion is failing with an exception.
We have exception handling in place to catch the exception.
The exception handler looks for a specific exception and then does a WHEN OTHERS.
Both of the exceptions have a statement that logs to the TRACE table. These messages do not get inserted either.
It seems like the delete from the trace table causes it to crash. The shell script does not hang, it ends with a success. Success because the return variable it set to success and will be unless an error come up. We are not throwing any kind of error anywhere.
What could make the function just die and pass thru the exception handling? Any ideas would be great as we are stumped on this. We cannot reproduce it at work, just at that one site.
|
|
|
|
|
|
|
|
| Re: PL/SQL Function fails without an exception [message #656006 is a reply to message #655994] |
Mon, 19 September 2016 17:27  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Insufficient privileges is a likely cause, specifically the lack of privilege to delete from the trace table. If, for example, the function and procedure are created with AUTHID CURRENT_USER and the user executing the procedure lacks the privilege to delete from the trace table or has been granted such a privilege through a role that does not apply to stored procedures and functions and the error is obfuscated by your exception handling, then you could get the results that you are seeing. If you were to modify the exception handling or comment it out, then that would show the error, then you could correct it by granting the appropriate privilege. Please see the example below that first reproduces the problem, then shows modification of the exception handling that displays the error, then corrects the error by granting appropriate privileges directly.
-- environment:
SCOTT@orcl_12.1.0.2.0> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
-- reproduction of problem:
SCOTT@orcl_12.1.0.2.0> create table scott.trace_table
2 (id number generated always as identity,
3 module varchar2(12),
4 trace_col varchar2(30))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert into scott.trace_table (module, trace_col) values ('MISSING DATA', 'test data')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> create or replace function scott.test_func
2 return varchar2
3 authid current_user
4 as
5 v_result varchar2(10) := 'success';
6 begin
7 insert into scott.trace_table (module, trace_col) values ('#1', 'function initialized');
8 delete from scott.trace_table where module = 'MISSING DATA';
9 insert into scott.trace_table (module, trace_col) values ('#3', 'deletion completed');
10 return v_result;
11 exception
12 when others then
13 return v_result;
14 end test_func;
15 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> create or replace procedure scott.test_proc
2 authid current_user
3 as
4 v_result varchar2(10);
5 begin
6 v_result := test_func;
7 dbms_output.put_line (v_result);
8 end test_proc;
9 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> create user test_user identified by test_user
2 /
User created.
SCOTT@orcl_12.1.0.2.0> grant create session to test_user
2 /
Grant succeeded.
SCOTT@orcl_12.1.0.2.0> grant execute on scott.test_proc to test_user
2 /
Grant succeeded.
SCOTT@orcl_12.1.0.2.0> grant select, insert on scott.trace_table to test_user
2 /
Grant succeeded.
SCOTT@orcl_12.1.0.2.0> connect test_user/test_user@orcl
Connected.
TEST_USER@orcl_12.1.0.2.0> execute scott.test_proc
success
PL/SQL procedure successfully completed.
TEST_USER@orcl_12.1.0.2.0> select * from scott.trace_table order by id
2 /
ID MODULE TRACE_COL
---------- ------------ ------------------------------
1 MISSING DATA test data
2 #1 function initialized
2 rows selected.
-- modification of exception section to display error:
TEST_USER@orcl_12.1.0.2.0> connect scott/tiger@orcl
Connected.
SCOTT@orcl_12.1.0.2.0> create or replace function scott.test_func
2 return varchar2
3 authid current_user
4 as
5 v_result varchar2(10) := 'success';
6 begin
7 insert into scott.trace_table (module, trace_col) values ('#1', 'function initialized');
8 delete from scott.trace_table where module = 'MISSING DATA';
9 insert into scott.trace_table (module, trace_col) values ('#3', 'deletion completed');
10 return v_result;
11 exception
12 when others then
13 -- add line below or comment out exception section:
14 raise;
15 return v_result;
16 end test_func;
17 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> connect test_user/test_user@orcl
Connected.
TEST_USER@orcl_12.1.0.2.0> execute scott.test_proc
BEGIN scott.test_proc; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TEST_FUNC", line 14
ORA-06512: at "SCOTT.TEST_PROC", line 6
ORA-06512: at line 1
TEST_USER@orcl_12.1.0.2.0> select * from scott.trace_table order by id
2 /
ID MODULE TRACE_COL
---------- ------------ ------------------------------
1 MISSING DATA test data
2 #1 function initialized
2 rows selected.
-- correction of problem:
TEST_USER@orcl_12.1.0.2.0> connect scott/tiger@orcl
Connected.
SCOTT@orcl_12.1.0.2.0> grant delete on scott.trace_table to test_user
2 /
Grant succeeded.
SCOTT@orcl_12.1.0.2.0> connect test_user/test_user@orcl
Connected.
TEST_USER@orcl_12.1.0.2.0> execute scott.test_proc
success
PL/SQL procedure successfully completed.
TEST_USER@orcl_12.1.0.2.0> select * from scott.trace_table order by id
2 /
ID MODULE TRACE_COL
---------- ------------ ------------------------------
2 #1 function initialized
4 #1 function initialized
5 #3 deletion completed
3 rows selected.
|
|
|
|
| Re: PL/SQL Function fails without an exception [message #656793 is a reply to message #656006] |
Wed, 19 October 2016 08:43 |
|
moverdear
Messages: 3
Registered: September 2016
|
Junior Member |
|
|
sorry for the delay, the customer shelved this until the end of year processing was done.
I have some more info. From my first post, I said this:
#1 The very first thing the function does is log a message to a trace table that the function has been initiated. This works great.
#2 The code does a delete from the trace table with a specific where clause ( module = 'MISSING DATA' )
#3 The function logs a message to the TRACE table that the deletion was completed ----------- This message never appears.
I thought #2 failed and was not caught in the exception. But, #2 in fact gets run correctly. It removes the rows from the table, but it does not log #3 at all. Processing stops after the delete, nothing else logged or anymore code ran. It just comes back as a successful completion. Our exception handling looks like this. If the delete above caused an exception, shouldn't I at least get a message in the trace table? How could this setup not log something?
EXCEPTION
WHEN gcon_stop_processing_excep THEN
trace(0,' ** PROCESS FAILED ** ',
' !! ERROR MAIN STOP_PROCESSING ');
RETURN g_failure_txt;
WHEN OTHERS THEN
g_failure_txt := SQLERRM;
trace(0,' ** PROCESS FAILED ** ',
' !! ERROR MAIN OTHERS SQLCODE = ' || SQLCODE ||
' SQL MSG = ' || SQLERRM);
RETURN g_failure_txt;
|
|
|
|
|
|
|
|
|
|
| Re: PL/SQL Function fails without an exception [message #656798 is a reply to message #656797] |
Wed, 19 October 2016 09:20 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well if it fails silently when run from the scheduler that doesn't prove there's not a mistake in the code. It just proves that if there is a mistake it's not issue most of the time.
Best guess is that it is failing with an error but the error handling code is swallowing the error.
But unless you show us the code you're really not going to get any more useful answers than that.
|
|
|
|
|
|
|
|
| Re: PL/SQL Function fails without an exception [message #656802 is a reply to message #656796] |
Wed, 19 October 2016 09:54 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
moverdear wrote on Wed, 19 October 2016 06:54I do not think its a problem in the code. This code has been in production for 15 years and runs at 3 different sites. This one site just started having issues. We have an application that runs this code and it works great. When running from a scheduled job, it fails like this.
The last thing that happens is it deletes rows from the table, and it does that successfully. There is a commit after the delete, then it is supposed to put a row in the trace table saying the rows were removed. That trace message never appears.
I still think it is some sort of permission issue with the user running the scheduled job, but that same user can manually run the delete statement and it works with no errors shown.
Oracle RDBMS runs on tens of thousands of systems worldwide error free.
Does this mean or prove that Oracle software has ZERO bugs in it?
The observation that code has run for years & on multiple different without error does not imply that the code is free of bugs.
You should accept the premise that the most likely root cause of your problem is the application code itself!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
