Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Function fails without an exception (Oracle 12c)
PL/SQL Function fails without an exception [message #655994] Mon, 19 September 2016 11:08 Go to next message
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 #655995 is a reply to message #655994] Mon, 19 September 2016 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 25853
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We have no idea what OS name or version for the problem system.

Something is different!
Do you have error of omission (something needed is missing)?
Do you have error of commission (something done wrongly)?

use COPY & PASTE to show us what you do & see!
Re: PL/SQL Function fails without an exception [message #655996 is a reply to message #655994] Mon, 19 September 2016 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65384
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The exception handler looks for a specific exception and then does a WHEN OTHERS.
This is most likely a bug.
Read WHEN OTHERS.

And it is very difficult to debug some code without it, isn't it?

Re: PL/SQL Function fails without an exception [message #656006 is a reply to message #655994] Mon, 19 September 2016 17:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8804
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 Go to previous messageGo to next message
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 #656795 is a reply to message #656793] Wed, 19 October 2016 08:51 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Presumably you've made a mistake somewhere in some of the code that we can't see.
Re: PL/SQL Function fails without an exception [message #656796 is a reply to message #656795] Wed, 19 October 2016 08:54 Go to previous messageGo to next message
moverdear
Messages: 3
Registered: September 2016
Junior Member
I 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.
Re: PL/SQL Function fails without an exception [message #656797 is a reply to message #656796] Wed, 19 October 2016 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25853
Registered: January 2009
Location: SoCal
Senior Member
I suspect a data dependent bug.
When EVERYTHING is REALLY< Really, really the same, then the results are the same.
When results are different, then something BY DEFINITION is different

It is your challenge to find what is different.
When you assume something is the same, then you won't look for any difference there & problem will persist.
Re: PL/SQL Function fails without an exception [message #656798 is a reply to message #656797] Wed, 19 October 2016 09:20 Go to previous messageGo to next message
cookiemonster
Messages: 13014
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 #656799 is a reply to message #656798] Wed, 19 October 2016 09:21 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
And lets be clear, any sort of permissions issue will result in an oracle error being thrown
Re: PL/SQL Function fails without an exception [message #656800 is a reply to message #656793] Wed, 19 October 2016 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65384
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 19 September 2016 18:45

Quote:
The exception handler looks for a specific exception and then does a WHEN OTHERS.
This is most likely a bug.
Read WHEN OTHERS.
...
Re: PL/SQL Function fails without an exception [message #656802 is a reply to message #656796] Wed, 19 October 2016 09:54 Go to previous message
BlackSwan
Messages: 25853
Registered: January 2009
Location: SoCal
Senior Member
moverdear wrote on Wed, 19 October 2016 06:54
I 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!
Previous Topic: Insert a space in the middle of a six characther string
Next Topic: sql query with combination of row values
Goto Forum:
  


Current Time: Mon Feb 19 20:50:42 CST 2018

Total time taken to generate the page: 0.10430 seconds