Skip to Main Content
  • Questions
  • How to solve the conflict: Need logging including SQLCODE & SQLERRM, while controlling side effects at the same time

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Henning.

Asked: April 04, 2017 - 7:20 am UTC

Last updated: April 22, 2017 - 1:49 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Here are two closely related questions regarding SQLCODE, SQLERRM, PRAGMA_RESTRICT_REFERENCES and its deprecation, and the need for a logging framework.

We developed a logging framework in PL/SQL which allows us to use simple statement like log.info('some text'); to write log messages and exception messages including context to a database table, similar to Log4J and other logging frameworks.
The framework uses autonomous transactions when it has to actually write the messages of course, because usually the log can be very helpful for analysis when an exception caused a rollback.

For exception messages, the framework uses SQLCODE and SQLERRM (well, actually not SQLERRM but the various helpers DBMS_UTILITY.FORMAT% on 11g or UTL_CALLSTACK.% when on 12c).

The documentation says that when using SQLCODE or SQLERRM regarding PRAGMA RESTRICT_REFERENCES: Restriction on WNPS: You cannot specify WNPS if the subprogram invokes the SQLCODE or SQLERRM function (and the same for RNPS).

Q1: Why do these functions violate RNPS and WNPS? It's not obvious.

Anyway, we worked around this by specifying TRUST for the logging procedures.
That way the database accepts the lie that the logging procedures are free from side-effects.
Well, apart from the desired side effect of inserting into the log table, the procedures are free from side-effects - they do not read or write any other table.

It is clear that we'd get into trouble once we should want to call log.info(...); from inside a trigger on the logging table itself, but we won't do that of course.

Apart from that, we can use the logging procedures anywhere in triggers, eben in functions called from SQL, without any problem.
Actually writing log messages could slow down queries a lot of course, but the framework can switch logging on or off for different routines and the decision code is cheap, based on a PL/SQL table inside the logging package.

Starting with 12c, the documentation says that RESTRICT_REFERENCES is deprecated.

Q2: How would one code a logging procedure which uses SQLCODE and SQLERRM/UTL_CALL_STACK and can be called from inside functions in SQL on 12c?

and Connor said...

Apologies for taking so long to respond. But the easter break lets us catch up a bit :-)

If you are using autonomous transactions, you should have no dramas using logging style procedures from anywhere


SQL> @drop log_table

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table log_table(s number generated as identity, m varchar2(1000));

Table created.

SQL>
SQL> create or replace
  2  procedure logger(p_msg varchar2) is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into log_table (m) values (p_msg);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  function func(p_force_fail varchar2) return int is
  3  begin
  4    logger('start of function');
  5
  6    if p_force_fail = 'Y' then
  7       raise_application_error(-20000,'Oh no...I died');
  8    end if;
  9
 10    logger('end of function');
 11    return 1;
 12  exception
 13    when others then
 14      logger(sqlerrm);
 15  end;
 16  /

Function created.

SQL>
SQL>
SQL> variable n number
SQL> exec :n := func('N');

PL/SQL procedure successfully completed.

SQL> exec :n := func('Y');
BEGIN :n := func('Y'); END;

*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at line 1


SQL> select * from log_table;

         S
----------
M
----------------------------------------------------------------------------------------------------
         1
start of function

         2
end of function

         3
start of function

         4
ORA-20000: Oh no...I died


4 rows selected.

SQL>
SQL> select func('N') from dual;

 FUNC('N')
----------
         1

1 row selected.

SQL> select func('Y') from dual;
select func('Y') from dual
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14


SQL> select * from log_table;

         S
----------
M
----------------------------------------------------------------------------------------------------
         1
start of function

         2
end of function

         3
start of function

         4
ORA-20000: Oh no...I died

         5
start of function

         6
end of function

         7
start of function

         8
ORA-20000: Oh no...I died


8 rows selected.

SQL>
SQL> col m format a50
SQL> @drop log_table

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table log_table(s number generated as identity, m varchar2(1000));

Table created.

SQL>
SQL> create or replace
  2  procedure logger(p_msg varchar2) is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into log_table (m) values (p_msg);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  function func(p_force_fail varchar2) return int is
  3  begin
  4    logger('start of function');
  5
  6    if p_force_fail = 'Y' then
  7       raise_application_error(-20000,'Oh no...I died');
  8    end if;
  9
 10    logger('end of function');
 11    return 1;
 12  exception
 13    when others then
 14      logger(sqlerrm);
 15  end;
 16  /

Function created.

SQL>
SQL>
SQL> variable n number
SQL> exec :n := func('N');

PL/SQL procedure successfully completed.

SQL> exec :n := func('Y');
BEGIN :n := func('Y'); END;

*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at line 1


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died

4 rows selected.

SQL>
SQL> select func('N') from dual;

 FUNC('N')
----------
         1

1 row selected.

SQL> select func('Y') from dual;
select func('Y') from dual
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died
         5 start of function
         6 end of function
         7 start of function
         8 ORA-20000: Oh no...I died

8 rows selected.

SQL>
SQL> @drop log_table

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table log_table(s number generated as identity, m varchar2(1000));

Table created.

SQL>
SQL> create or replace
  2  procedure logger(p_msg varchar2) is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into log_table (m) values (p_msg);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  function func(p_force_fail varchar2) return int is
  3  begin
  4    logger('start of function');
  5
  6    if p_force_fail = 'Y' then
  7       raise_application_error(-20000,'Oh no...I died');
  8    end if;
  9
 10    logger('end of function');
 11    return 1;
 12  exception
 13    when others then
 14      logger(sqlerrm);
 15      raise;
 16  end;
 17  /

Function created.

SQL>
SQL>
SQL> variable n number
SQL> exec :n := func('N');

PL/SQL procedure successfully completed.

SQL> exec :n := func('Y');
BEGIN :n := func('Y'); END;

*
ERROR at line 1:
ORA-20000: Oh no...I died
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at "MCDONAC.FUNC", line 6
ORA-06512: at line 1


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died

4 rows selected.

SQL>
SQL> select func('N') from dual;

 FUNC('N')
----------
         1

1 row selected.

SQL> select func('Y') from dual;
select func('Y') from dual
       *
ERROR at line 1:
ORA-20000: Oh no...I died
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at "MCDONAC.FUNC", line 6


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died
         5 start of function
         6 end of function
         7 start of function
         8 ORA-20000: Oh no...I died

8 rows selected.

SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A useful answer, but not quite the answer to my questions...

Henning, April 21, 2017 - 3:19 pm UTC

To put things short, you say that everything will work as expected as long as the application doesn't use the PRAGMA_RESTRICT_REFERENCES at all.

That's certainly true and it would be a sufficient answer in many cases.

However, our application is still using PRAGMA_RESTRICT_REFERENCES in many places, including customer-specific code, in some cases written by our customers.

This leads to a different question:

Is there a fool-proof way to replace PRAGMA_RESTRICT_REFERENCES(..., WNDS, WNPS) with a newer syntax?

I think the answer is "no" here.

I mean, DETERMINISTIC is not the same (because it implies RNPS, RNDS in addition) and PARALLEL_ENABLE is a bit like (RNPS, RNDS) only.

Without the RESTRICT_REFERENCES, the purity condition checking for functions called from SQL or DML occurs at run-time, not at compile-time, AFAIK.

So, just removing the RESTRICT_REFERENCES from the code completely will certainly work, but it gives up a certain amount of safety for the developers, similar to removing keywords like
final
from Java or
const
from C++ or replacing static SQL with dynamic SQL).

Connor McDonald
April 22, 2017 - 1:49 am UTC

"Is there a fool-proof way to replace PRAGMA_RESTRICT_REFERENCES(..., WNDS, WNPS) with a newer syntax? I think the answer is "no" here. "

Correct.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library