SQL owa_util.get_procedure and $$PLSQL_UNIT

In 10g and 11g I use the “owa_util.get_procedure” function. I normally use this in packages as it will also return the name of an internal procedure or function as part of the package name, i.e. (package_name).(procedure name). I use this to provide a generic EXCEPTION template for identifying where an exception occured.

CREATE OR REPLACE procedure some_procedure is
    v_procedure_name varchar2(32);
begin
    v_procedure_name := owa_util.get_procedure;
end;

CREATE OR REPLACE PACKAGE some_package
AS
    FUNCTION v_function_name
    RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY some_package
AS
    FUNCTION v_function_name
    RETURN DATE
    IS
    BEGIN
        RETURN SYSDATE;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR IN '||owa_util.get_procedure);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
END;
/

Source: https://stackoverflow.com/questions/286549/find-out-name-of-pl-sql-procedure#286569

alter session set plscope_settings='identifiers:all'
/
Session altered.

SQL> set serverout on

SQL> create or replace package pkg
as
   procedure p1;

   procedure p2;
end pkg;
/
Package created.

SQL> create or replace package body pkg
as
   procedure get_scope (obj varchar2, line int)
   as
   begin
      for c in (select rpad (lpad (' ', 2 * (level - 1)) || name, 20, '.') || ' ' || rpad (type, 20) || rpad (usage, 20) identifier_usage_contexts
                from user_identifiers t
                where level != 1
                start with line = get_scope.line and object_name = obj
                connect by usage_id = prior usage_context_id)
      loop
         dbms_output.put_line (c.identifier_usage_contexts);
      end loop;
   end get_scope;

   procedure p1
   as
   begin
      null;
   end p1;


   procedure p2
   as
   begin
      get_scope ($$plsql_unit, $$plsql_line);
   end p2;
end pkg;

Return information about witch module call this code.

CREATE OR REPLACE FUNCTION MIBP_OWNER.FN_WHO_AM_I (p_lvl NUMBER DEFAULT 0)
    RETURN VARCHAR2 IS
    /***********************************************************************************************
    FN_WHO_AM_I returns the full ORACLE name of your object including schema and package names
    --
    FN_WHO_AM_I(0) - returns the name of your object
    FN_WHO_AM_I(1) - returns the name of calling object
    FN_WHO_AM_I(2) - returns the name of object, who called calling object
    etc., etc., etc.... Up to to he highest level
    -------------------------------------------------------------------------------------------------
    Copyrigth GARBUYA 2010
    *************************************************************************************************/
    TYPE str_varr_t IS VARRAY (2) OF CHAR (1);

    TYPE str_table_t IS TABLE OF VARCHAR2 (256);

    TYPE num_table_t IS TABLE OF NUMBER;

    v_stack        VARCHAR2 (2048) DEFAULT UPPER (DBMS_UTILITY.format_call_stack);
    v_tmp_1        VARCHAR2 (1024);
    v_tmp_2        VARCHAR2 (1024);
    v_pkg_name     VARCHAR2 (32);
    v_obj_type     VARCHAR2 (32);
    v_owner        VARCHAR2 (32);
    v_idx          NUMBER := 0;
    v_pos1         NUMBER := 0;
    v_pos2         NUMBER := 0;
    v_line_nbr     NUMBER := 0;
    v_blk_cnt      NUMBER := 0;
    v_str_len      NUMBER := 0;
    v_bgn_cnt      NUMBER := 0;
    v_end_cnt      NUMBER := 0;
    it_is_comment  BOOLEAN := FALSE;
    it_is_literal  BOOLEAN := FALSE;
    v_literal_arr  str_varr_t := str_varr_t ('''', '"');
    v_blk_bgn_tbl  str_table_t
                       := str_table_t (' IF '
                                      ,' LOOP '
                                      ,' CASE '
                                      ,' BEGIN ');
    v_tbl          str_table_t := str_table_t ();
    v_blk_bgn_len_tbl num_table_t := num_table_t ();
BEGIN
    v_stack     := SUBSTR (v_stack, INSTR (v_stack, CHR (10), INSTR (v_stack, 'FN_WHO_AM_I')) + 1) || 'ORACLE'; -- skip myself

    FOR v_pos2 IN 1 .. p_lvl LOOP -- advance to the input level
        v_pos1      := INSTR (v_stack, CHR (10));
        v_stack     := SUBSTR (v_stack, INSTR (v_stack, CHR (10)) + 1);
    END LOOP;

    v_pos1      := INSTR (v_stack, CHR (10));

    IF v_pos1 = 0 THEN
        RETURN (v_stack);
    END IF;

    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1); -- get only current level
    v_stack     := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut object handle
    v_line_nbr  := TO_NUMBER (SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1)); -- get line number
    v_stack     := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut line number
    v_pos1      := INSTR (v_stack, ' BODY');

    IF v_pos1 = 0 THEN
        RETURN (v_stack);
    END IF;

    v_pos1      := INSTR (v_stack, ' ', v_pos1 + 2); -- find end of object type
    v_obj_type  := SUBSTR (v_stack, 1, v_pos1 - 1); -- get object type
    v_stack     := TRIM (SUBSTR (v_stack, v_pos1 + 1)); -- get package name
    v_pos1      := INSTR (v_stack, '.');
    v_owner     := SUBSTR (v_stack, 1, v_pos1 - 1); -- get owner
    v_pkg_name  := SUBSTR (v_stack, v_pos1 + 1); -- get package name
    v_blk_cnt   := 0;
    it_is_literal := FALSE;

    --
    FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
        v_blk_bgn_len_tbl.EXTEND (1);
        v_blk_bgn_len_tbl (v_blk_bgn_len_tbl.LAST) := LENGTH (v_blk_bgn_tbl (v_idx));
    END LOOP;

    --
    FOR src IN (
                   SELECT ' ' || REPLACE (TRANSLATE (UPPER (text), ';(' || CHR (10), '   '), '''''', ' ') || ' ' text
                   FROM all_source
                   WHERE     owner = v_owner
                         AND name = v_pkg_name
                         AND TYPE = v_obj_type
                         AND line < v_line_nbr
                   ORDER BY line
               ) LOOP
        v_stack     := src.text;

        IF it_is_comment THEN
            v_pos1      := INSTR (v_stack, '*/');

            IF v_pos1 > 0 THEN
                v_stack     := SUBSTR (v_stack, v_pos1 + 2);
                it_is_comment := FALSE;
            ELSE
                v_stack     := ' ';
            END IF;
        END IF;

        --
        IF v_stack != ' ' THEN
            --
            v_pos1      := INSTR (v_stack, '/*');

            WHILE v_pos1 > 0 LOOP
                v_tmp_1     := SUBSTR (v_stack, 1, v_pos1 - 1);
                v_pos2      := INSTR (v_stack, '*/');

                IF v_pos2 > 0 THEN
                    v_tmp_2     := SUBSTR (v_stack, v_pos2 + 2);
                    v_stack     := v_tmp_1 || v_tmp_2;
                ELSE
                    v_stack     := v_tmp_1;
                    it_is_comment := TRUE;
                END IF;

                v_pos1      := INSTR (v_stack, '/*');
            END LOOP;

            --
            IF v_stack != ' ' THEN
                v_pos1      := INSTR (v_stack, '--');

                IF v_pos1 > 0 THEN
                    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1);
                END IF;

                --
                IF v_stack != ' ' THEN
                    FOR v_idx IN v_literal_arr.FIRST .. v_literal_arr.LAST LOOP
                        v_pos1      := INSTR (v_stack, v_literal_arr (v_idx));

                        WHILE v_pos1 > 0 LOOP
                            v_pos2      := INSTR (v_stack, v_literal_arr (v_idx), v_pos1 + 1);

                            IF v_pos2 > 0 THEN
                                v_tmp_1     := SUBSTR (v_stack, 1, v_pos1 - 1);
                                v_tmp_2     := SUBSTR (v_stack, v_pos2 + 1);
                                v_stack     := v_tmp_1 || v_tmp_2;
                            ELSE
                                IF it_is_literal THEN
                                    v_stack     := SUBSTR (v_stack, v_pos1 + 1);
                                    it_is_literal := FALSE;
                                ELSE
                                    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1);
                                    it_is_literal := TRUE;
                                END IF;
                            END IF;

                            v_pos1      := INSTR (v_stack, v_literal_arr (v_idx));
                        END LOOP;
                    END LOOP;

                    --
                    IF v_stack != ' ' THEN
                        WHILE INSTR (v_stack, '  ') > 0 LOOP
                            v_stack     := REPLACE (v_stack, '  ', ' ');
                        END LOOP;

                        v_stack     := REPLACE (v_stack, ' END IF ', ' END ');
                        v_stack     := REPLACE (v_stack, ' END LOOP ', ' END ');

                        --
                        IF v_stack != ' ' THEN
                            v_stack     := ' ' || v_stack;
                            v_pos1      := INSTR (v_stack, ' FUNCTION ') + INSTR (v_stack, ' PROCEDURE ');

                            IF v_pos1 > 0 THEN
                                v_obj_type  := TRIM (SUBSTR (v_stack, v_pos1 + 1, 9)); -- get object type
                                v_stack     := TRIM (SUBSTR (v_stack, v_pos1 + 10)) || '  '; -- cut object type
                                v_stack     := SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1); -- get object name
                                v_tbl.EXTEND (1);
                                v_tbl (v_tbl.LAST) := v_obj_type || ' ' || v_owner || '.' || v_pkg_name || '.' || v_stack;
                            END IF;

                            --
                            v_pos1      := 0;
                            v_pos2      := 0;
                            v_tmp_1     := v_stack;
                            v_tmp_2     := v_stack;

                            FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
                                v_str_len   := NVL (LENGTH (v_tmp_1), 0);
                                v_tmp_1     := REPLACE (v_tmp_1, v_blk_bgn_tbl (v_idx), NULL);
                                v_bgn_cnt   := NVL (LENGTH (v_tmp_1), 0);
                                v_pos1      := v_pos1 + (v_str_len - v_bgn_cnt) / v_blk_bgn_len_tbl (v_idx);
                                v_str_len   := NVL (LENGTH (v_tmp_2), 0);
                                v_tmp_2     := REPLACE (v_tmp_2, ' END ', NULL);
                                v_end_cnt   := NVL (LENGTH (v_tmp_2), 0);
                                v_pos2      := v_pos2 + (v_str_len - v_end_cnt) / 5; --- 5 is the length(' END ')
                            END LOOP;

                            IF v_pos1 > v_pos2 THEN
                                v_blk_cnt   := v_blk_cnt + 1;
                            ELSIF v_pos1 < v_pos2 THEN
                                v_blk_cnt   := v_blk_cnt - 1;

                                IF     v_blk_cnt = 0
                                   AND v_tbl.COUNT > 0 THEN
                                    v_tbl.DELETE (v_tbl.LAST);
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END LOOP;

    RETURN CASE v_tbl.COUNT
               WHEN 0 THEN
                   'UNKNOWN'
               ELSE
                   v_tbl (v_tbl.LAST)
           END;
END;
/

Show errors;

 

Other good example use new features Oracle 10g

DECLARE
	local_exception EXCEPTION;
	FUNCTION nested_local_function
	RETURN BOOLEAN IS
		retval BOOLEAN := FALSE;
BEGIN
	RAISE local_exception;
	RETURN retval;
	END;
BEGIN
IF nested_local_function THEN
	dbms_output.put_line('No raised exception');
END IF;
EXCEPTION
WHEN others THEN
	dbms_output.put_line('DBMS_UTILITY.FORMAT_CALL_STACK');
	dbms_output.put_line('------------------------------');
	dbms_output.put_line(dbms_utility.format_call_stack);
	dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE');
	dbms_output.put_line('-----------------------------------');
	dbms_output.put_line(dbms_utility.format_error_backtrace);
	dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_STACK');
	dbms_output.put_line('-------------------------------');
	dbms_output.put_line(dbms_utility.format_error_stack);
END;
/
This script produces the following output:
DBMS_UTILITY.FORMAT_CALL_STACK
------------------------------
----- PL/SQL Call Stack -----
object line object
handle number
name
20909240 18 anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
-----------------------------------
ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
-------------------------------
ORA-06510: PL/SQL: unhandled user-defined exception

Oracle SQL: LAG Oracle 11g is analytic function

Very usefully new features in Oracle 11g is analytic Oracle SQL function LAG.

SQL query LAG

This code find change in address post code in journal table:

  SELECT  *
    FROM  (
              SELECT  t1.SITE_NUMBER
                     ,t1.JN_DATETIME
                     ,t1.ADR_POSTCODE
                     ,LAG (t1.ADR_POSTCODE, 1) OVER (PARTITION BY t1.SITE_NUMBER ORDER BY t1.jn_datetime) AS PREV_ADR_POSTCODE
                FROM  t1 
          )
   WHERE      adr_postcode = '00-001'
          AND PREV_ADR_POSTCODE <> '00-001'
          AND PREV_ADR_POSTCODE <> '00-000'
ORDER BY  SITE_NUMBER;

Test query data

CREATE TABLE T1
(
  SITE_NUMBER   VARCHAR2(16 BYTE),
  JN_DATETIME   DATE,
  ADR_POSTCODE  VARCHAR2(20 BYTE)
)
LOGGING 
NOCOMPRESS 
MONITORING;
SET DEFINE OFF;
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('08/01/2017 15:01:31', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20002', TO_DATE('04/20/2017 11:59:29', 'MM/DD/YYYY HH24:MI:SS'), '00-002');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('06/15/2017 04:02:41', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20004', TO_DATE('06/20/2017 20:15:13', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('12/10/1929 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/25/2091 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/14/1914 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('03/25/1911 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('11/22/2029 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
COMMIT;

SQL result

site_number jn_datetime adr_postcode prev_adr_postcode
20001 2017-08-01 15:01:31 00-001 00-003

How it works together

http://sqlfiddle.com/#!4/9a676b/1

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑