Cannot reference package/module/global variables from an autonomous transaction

Technote (FAQ)


Question

Why is SQL0727N error encountered if an attempt is made to access a package variable, package constant, module variable, or global variable from an autonomous transaction?

Cause

This is caused by a limitation of DB2's current implementation of autonomous transactions. PL/SQL package variables (known as module variables in DB2 SQL PL) and global variables cannot be referenced from an autonomous transaction. Therefore, the SQL0727N error is returned when these variables are referenced in an autonomous transaction:


The error message for SQL0727N indicates that an SQL20430N error occurred in the implicit system action:
  • SQL20430N Global variable "<variable-name>" cannot be set or referenced in this context.
    ( http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.messages.sql.doc/doc/msql20430n.html )

    Explanation:
    In some contexts a global variable cannot be set and in other contexts it cannot be referenced. This message can be returned in the following situations:

    * A statement attempted to set the named global variable in one of the following contexts:
    - compound SQL (inline) statements
    - functions where the body is not a compound SQL (compiled) statement
    - methods
    - triggers where the body is not a compound SQL (compiled) statement

    * A global variable is being referenced in a procedure that is defined as AUTONOMOUS

    The statement cannot be processed.

    User response:
    Remove the unsupported setting or reference of the global variable.

In addition, DBMS_OUTPUT cannot be used in an autonomous transaction because the DBMS_OUTPUT package uses package/global variables internally.

Answer

This limitation has been removed in DB2 V9.7 Fix Pack 3 and above. The preferred recommendation is to apply the latest DB2 V9.7 Fix Pack. You may also use the workarounds listed below.


An autonomous transaction may be used as an exception transaction routine to ensure that error logs are recorded when the transaction is rolled back as the result of an error. Difficulties arise when DBMS_OUTPUT.PUT_LINE is called for a notification or a debug message.

For example, let's assume the following sample program, TESTPKG package, a MSG01 package constant, PROC_MAIN and PROC_ERROR SQL procedures for usage scenarios below.

The PROC_ERROR SQL procedure provides error handling for the PROC_MAIN SQL procedure. In this case, the PROC_ERROR routine is used in an autonomous transaction, to make sure that error records inserted into an log table will remain, even if the PROC_MAIN SQL procedure transaction fails and is rolled back.

Sample of PROC_MAIN, PROC_ERROR, MSG01 constant:

----------------------------------------
-- TESTPKG package
----------------------------------------
create or replace package TESTPKG
as
    MSG01    constant  varchar2(64) := 'MSG01 - NO_DATA_FOUND';

    procedure PROC_MAIN (
        IN_ID       in   int ,
        OUT_SRCDATA out  varchar2
    ) ;

    procedure PROC_ERROR (
        IN_ID       in  int
    ) ;
end ;
/

create or replace package body TESTPKG
as

----------------------------------------
--  PROC_MAIN : main routine
----------------------------------------
    procedure PROC_MAIN (
        IN_ID       in   int ,
        OUT_SRCDATA out  varchar2
    )
    is

    begin
        select SRCDATA into OUT_SRCDATA from TEST.SRCTAB where ID = IN_ID ;
        insert into TEST.OUTTAB values ( IN_ID, OUT_SRCDATA || ' is changed' );

    exception
        when NO_DATA_FOUND then
            PROC_ERROR( IN_ID ) ;

    end PROC_MAIN ;

----------------------------------------
--  PROC_ERROR : error handling routine
----------------------------------------
    procedure PROC_ERROR (
        IN_ID   in   int
    )
    is
    pragma autonomous_transaction ;

    begin
        insert into TEST.LOGTAB values ( TESTPKG.MSG01 || ' in arg ID = ' || IN_ID );
        DBMS_OUTPUT.PUT_LINE ( 'Exception : ' || TESTPKG.MSG01 || ' in arg ID = ' || IN_ID );

    end PROC_ERROR ;

end
/

However, the following error message will be returned at execution time, because of the AUTONOMOUS_TRANSACTION pragma:

  • DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
  • SQL0727N An error occurred during implicit system action type "10". Information returned for the error includes SQLCODE "-20430", SQLSTATE "428GX" and message tokens "MSG01". SQLSTATE=56098

The error occurs because a package constant (such as TESTPKG.MSG01) cannot be referenced in an autonomous transaction.

Furthermore, the use of DBMS_OUTPUT.PUT_LINE would also cause difficulties in this autonomous routine.

In this case, these error situations can be avoided by making a separate autonomous sub-procedure to handle the logging. By passing the value of the package constant into an input parameter, the autonomous procedure need not access the package constant directly. The PUT_LINE call will also work now, because PROC_ERROR no longer needs to be autonomous itself.

Sample workaround:

----------------------------------------
--  PROC_ERROR : error handling routine
----------------------------------------
    procedure PROC_ERROR (
        IN_ID   in   int
    )
    is
    -- pragma autonomous_transaction ;    -- comment out for a restriction of DB2 PL/SQL
    begin
        -- insert into TEST.LOGTAB values ( TESTPKG.MSG01 || ' in arg ID = ' || IN_ID ) ;
        PROC_LOG( TESTPKG.MSG01 || ' in arg ID = ' || IN_ID ) ;
        DBMS_OUTPUT.PUT_LINE ( 'Exception : ' || TESTPKG.MSG01 || ' in arg ID = ' || IN_ID ) ;

    end PROC_ERROR ;

----------------------------------------
--  PROC_LOG : error logging routine
----------------------------------------
    procedure PROC_LOG (
        IN_DATA in   varchar2(64)
    )
    is
    pragma autonomous_transaction ;         -- an autonomous transaction for the error logging on DB2 PL/SQL

    begin
        insert into TEST.LOGTAB values ( IN_DATA ) ;

    end PROC_ERROR ;

Sample test scenario for a workaround against the DB2 PL/SQL restriction (cannot refer to package/global variables in an autonomous transaction)

----------------------------------------
-- test scenario of test.sql
----------------------------------------

-- db2set DB2_COMPATIBILITY_VECTOR=ORA
-- db2set -all
-- db2 create db ORADB
-- db2 connect to ORADB
-- db2 -c- -tvf test.sql

----------------------------------------
-- test.sql
----------------------------------------
drop table TEST.SRCTAB ;
drop table TEST.OUTTAB ;
drop table TEST.LOGTAB ;
drop schema TEST restrict ;
create schema TEST ;
create table TEST.SRCTAB( ID int, SRCDATA varchar2(16) ) ;
insert into TEST.SRCTAB values ( 0, 'Makuhari' ), ( 1, 'Toyosu' ), ( 2, 'Yamato' ), ( 3, 'Toronto' ) ;
create table TEST.OUTTAB( ID int, CNVDATA varchar2(64) ) ;
create table TEST.LOGTAB( TEXT varchar2(64) ) ;


set sqlcompat plsql ;

create or replace package TESTPKG
as
    MSG01    constant  varchar2(64) := 'MSG01 - NO_DATA_FOUND';

    procedure PROC_MAIN (
        IN_ID       in   int ,
        OUT_SRCDATA out  varchar2
    ) ;

    procedure PROC_ERROR (
        IN_ID       in  int
    ) ;

    procedure PROC_LOG (
        IN_DATA     in  varchar2
    ) ;
end ;
/

create or replace package body TESTPKG
as

----------------------------------------
--  PROC_MAIN : main routine
----------------------------------------
    procedure PROC_MAIN (
        IN_ID       in   int ,
        OUT_SRCDATA out  varchar2
    )
    is

    begin
        select SRCDATA into OUT_SRCDATA from TEST.SRCTAB where ID = IN_ID ;
        insert into TEST.OUTTAB values ( IN_ID, OUT_SRCDATA || ' is changed' );

    exception
        when NO_DATA_FOUND then
            PROC_ERROR( IN_ID ) ;

    end PROC_MAIN ;

----------------------------------------
--  PROC_ERROR : error handling routine
----------------------------------------
    procedure PROC_ERROR (
        IN_ID   in   int
    )
    is
    -- pragma autonomous_transaction ;      -- comment out for a restriction of DB2 PL/SQL

    begin
        -- insert into TEST.LOGTAB values ( TESTPKG.MSG01 || ' in arg ID = ' || IN_ID ) ;
        PROC_LOG( TESTPKG.MSG01 || ' in arg ID = ' || IN_ID ) ;
        DBMS_OUTPUT.PUT_LINE ( 'Exception : ' || TESTPKG.MSG01 || ' in arg ID = ' || IN_ID ) ;

    end PROC_ERROR ;

----------------------------------------
--  PROC_LOG : error logging routine
----------------------------------------
    procedure PROC_LOG (
        IN_DATA in   varchar2(64)
    )
    is
    pragma autonomous_transaction ;         -- an autonomous transaction for the error logging on DB2 PL/SQL

    begin
        insert into TEST.LOGTAB values ( IN_DATA ) ;

    end PROC_ERROR ;

end
/

commit ;

-- source table contents
select * from TEST.SRCTAB ;


----------------------------------------
-- test execution
----------------------------------------
set serveroutput on ;

declare
    RET_DATA   varchar2(64) ;

begin
    TESTPKG.PROC_MAIN( 2, RET_DATA ) ;
    DBMS_OUTPUT.PUT_LINE( 'PROC_MAIN returns : ' || RET_DATA ) ;

    TESTPKG.PROC_MAIN( 8, RET_DATA ) ;
    DBMS_OUTPUT.PUT_LINE( 'PROC_MAIN returns : ' ||  RET_DATA ) ;
end ;
/

-- rollback ;

-- converted table contents
select * from TEST.OUTTAB ;

-- log table contents
select * from TEST.LOGTAB ;

----------------------------------------
-- end of test scenario of test.sql
----------------------------------------
-- db2 connect to ORADB

Product Alias/Synonym

SQL0727N
SQL20430N

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows
Compiler- Query Parser

Software version:

9.7

Operating system(s):

AIX, HP-UX, Linux, Linux iSeries, Linux pSeries, Linux xSeries, Linux zSeries, Solaris, Windows

Software edition:

Enterprise Server, Express, Personal, Personal Developer's, Workgroup Server

Reference #:

1424306

Modified date:

2011-06-13

Translate my page

Machine Translation

Content navigation