This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 7, 2013 5:25 AM by Umesh P RSS

How to get SP body inside Database Trigger

AliBudak Newbie
Currently Being Moderated

Hi,

I have a Database Trigger (AFTER CREATE) and I wish to retrieve newly created Stored Procedure's body. I tried to use DBMS_METADATA.GET_DDL(DICTIONARY_OBJ_TYPE,DICTIONARY_OBJ_NAME,DICTIONARY_OBJ_OWNER) function, but I got this error: ORA-31603 object is not found.

The trigger is created in SYS schema, so there is no problem with SELECT_CATALOG_ROLE privilage. Is there another way to get SP body?

 

Thanks in advance.

  • 1. Re: How to get SP body inside Database Trigger
    Hoek Guru
    Currently Being Moderated

    You should NOT create objects in SYS/SYSTEM schema's.

    Getting Started with Database Administration

     

    What are you trying to accomplish? The trigger is a bad idea as well. Perhaps we can give you other ideas.

  • 2. Re: How to get SP body inside Database Trigger
    Karthick_Arp Guru
    Currently Being Moderated

    Do not use SYS or SYSTEM accounts. Please read Ask Tom "using the SYSTEM account" What do you mean by GET sp body. Where do you want to get it and when do you want to get it. What is the objective behind such a requirement.

  • 3. Re: How to get SP body inside Database Trigger
    Hoek Guru
    Currently Being Moderated

    I guess op wants to automagically capture DDL when a new DB-object is created....

  • 4. Re: How to get SP body inside Database Trigger
    Karthick_Arp Guru
    Currently Being Moderated

    Hoek wrote:

     

    I guess op wants to automagically capture DDL when a new DB-object is created....

     

    That's was puzzles me. Oracle already does that and why one would want to reinvent it

  • 5. Re: How to get SP body inside Database Trigger
    AliBudak Newbie
    Currently Being Moderated

    I need to store every version of th SPs and functions in a table. So I made a trigger that logs every DDL changes for procedures and functions. The trigger works well if object is replaced or dropped, but it fails when a new object is created.

  • 6. Re: How to get SP body inside Database Trigger
    SomeoneElse Guru
    Currently Being Moderated

    > I need to store every version of th SPs and functions in a table.

     

    Use a proper source safe system.

  • 7. Re: How to get SP body inside Database Trigger
    Hoek Guru
    Currently Being Moderated

    Is this for one specific schema only? Or more than one schema?

    And what is the outcome of: select * from v$version; ?

  • 8. Re: How to get SP body inside Database Trigger
    AliBudak Newbie
    Currently Being Moderated

    Trigger listens all schemas.

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

     

    The only thing that I need is finding the SQL Text of a procedure at the creation time.

  • 9. Re: How to get SP body inside Database Trigger
    Hoek Guru
    Currently Being Moderated

    "In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled.

    Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema.

    If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights."

    DBMS_METADATA

  • 10. Re: How to get SP body inside Database Trigger
    AliBudak Newbie
    Currently Being Moderated

    But as I wrote before, the trigger works fine for other schema's objects, if they are replaced or dropped. I can get the procedure's source SQL by using DBMS_METADATA.GET_DDL.

    Do you mean that SYS schema does not the privilage "SELECT_CATALOG_ROLE"  or I misunderstood something?

  • 11. Re: How to get SP body inside Database Trigger
    Hoek Guru
    Currently Being Moderated

    SYS has SELECT_CATALOG_ROLE ofcourse ( amongst others ):

    SQL> select *

      2  from   dba_role_privs

      3  where  grantee = 'SYS'

      4  and    granted_role like '%CATALOG%';

     

    GRANTEE                        GRANTED_ROLE                   ADM DEF

    ------------------------------ ------------------------------ --- ---

    SYS                            RECOVERY_CATALOG_OWNER         YES YES

    SYS                            DELETE_CATALOG_ROLE            YES YES

    SYS                            EXECUTE_CATALOG_ROLE           YES YES

    SYS                            SELECT_CATALOG_ROLE            YES YES

     

    4 rows selected.

     

     

     

    Anyway, did you test making de program invokers-rights?

    And, for a better understanding from my part: assuming you're using CREATE OR REPLACE PROCEDURE, you just compiled a procedure more than once, and the trigger didn't fire the first time, but it did fire for the subsequent times?

  • 12. Re: How to get SP body inside Database Trigger
    AliBudak Newbie
    Currently Being Moderated

    Hoek,  thanks for your patience at first ,

    I wrote the trigger below. I can explain myself better with this way. Trigger has 2 parts. Part 1 fires when a SP is replaced or dropped (Ok), Part 2 fires when a SP is created(failed).

     

     

     

    CREATE OR REPLACE TRIGGER SYS.TRG_OBJECT_LOG AFTER CREATE OR DROP ON DATABASE
    DECLARE

        IS_REPLACE      INTEGER; -- This will show whether the object is [just created] or [replaced/dropped]
        OBJ_TYPE        INTEGER;
        OBJ_NAME        VARCHAR2(30);
        OBJ_OWNER       INTEGER;
        UPDATE_OR_DROP  CHAR(1);

    BEGIN

        IF NVL(DICTIONARY_OBJ_TYPE,'-') IN
        (
            'PROCEDURE',
            'FUNCTION',
            'PACKAGE',
            'PACKAGE_BODY',
            'TYPE'
        )
        THEN

            IF ORA_SYSEVENT='DROP' THEN
                UPDATE_OR_DROP:='D';
            ELSE -- UPDATE
                UPDATE_OR_DROP:='U';
            END IF;

            OBJ_NAME:=DICTIONARY_OBJ_NAME;

            IF DICTIONARY_OBJ_TYPE='PROCEDURE' THEN OBJ_TYPE:=7;
            ELSIF DICTIONARY_OBJ_TYPE='FUNCTION' THEN OBJ_TYPE:=8;
            ELSIF DICTIONARY_OBJ_TYPE='PACKAGE' THEN OBJ_TYPE:=9;
            ELSIF DICTIONARY_OBJ_TYPE='PACKAGE_BODY' THEN OBJ_TYPE:=11;
            ELSIF DICTIONARY_OBJ_TYPE='TYPE' THEN OBJ_TYPE:=13;
            END IF;


            SELECT USER_ID INTO OBJ_OWNER
            FROM ALL_USERS
            WHERE USERNAME=DICTIONARY_OBJ_OWNER;
           

            SELECT
                DECODE((
                        SELECT
                            COUNT(1)
                        FROM ALL_SOURCE
                        WHERE OWNER=DICTIONARY_OBJ_OWNER
                        AND NAME=DICTIONARY_OBJ_NAME
                        AND TYPE=DICTIONARY_OBJ_TYPE),0,0,1)
            INTO IS_REPLACE
            FROM DUAL;

            ----------------------------------------------------------------------------------------
            -- [PART 1] REPLACE OR DROP (Works fine)
            ----------------------------------------------------------------------------------------
            IF IS_REPLACE=1 THEN

                INSERT INTO OTHER_SCHEMA.AUDIT_ALL_SOURCE
                (
                    OWNER,
                    NAME,
                    TYPE,
                    SOURCE,
                    CREATION_DATE,
                    UPDATE_DATE,
                    ENTRY_DATE,
                    OS_USER,
                    TERMINAL,
                    SESSION_USER,
                    UPDATE_OR_DROP
                )
                VALUES
                (
                    DICTIONARY_OBJ_OWNER,
                    DICTIONARY_OBJ_NAME,
                    DICTIONARY_OBJ_TYPE,
                    (SELECT DBMS_METADATA.GET_DDL(DICTIONARY_OBJ_TYPE,DICTIONARY_OBJ_NAME,DICTIONARY_OBJ_OWNER) FROM DUAL), -- SOURCE
                    (SELECT CTIME FROM OBJ$ WHERE OWNER#=OBJ_OWNER AND NAME=OBJ_NAME AND TYPE#=OBJ_TYPE),
                    (SELECT MTIME FROM OBJ$ WHERE OWNER#=OBJ_OWNER AND NAME=OBJ_NAME AND TYPE#=OBJ_TYPE),
                    SYSDATE,
                    (SELECT sys_context('USERENV', 'OS_USER') FROM DUAL),
                    (SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL),
                    (SELECT sys_context('USERENV', 'SESSION_USER') FROM DUAL),
                    UPDATE_OR_DROP
                );
            ----------------------------------------------------------------------------------------
            -- [PART 2] CREATE (failed at line 111)
            ----------------------------------------------------------------------------------------
            ELSE

                INSERT INTO OTHER_SCHEMA.AUDIT_ALL_SOURCE
                (
                    OWNER,
                    NAME,
                    TYPE,
                    SOURCE,
                    CREATION_DATE,
                    UPDATE_DATE,
                    ENTRY_DATE,
                    OS_USER,
                    TERMINAL,
                    SESSION_USER,
                    UPDATE_OR_DROP
                )
                VALUES
                (
                    DICTIONARY_OBJ_OWNER,
                    DICTIONARY_OBJ_NAME,
                    DICTIONARY_OBJ_TYPE,
                    DBMS_METADATA.GET_DDL(DICTIONARY_OBJ_TYPE,DICTIONARY_OBJ_NAME,DICTIONARY_OBJ_OWNER),/*LINE 111 Throws ORA-31603*/
                    SYSDATE,
                    SYSDATE,
                    SYSDATE,
                    (SELECT sys_context('USERENV', 'OS_USER') FROM DUAL),
                    (SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL),
                    (SELECT sys_context('USERENV', 'SESSION_USER') FROM DUAL),
                    'C' -- CREATE
                );

            END IF;

        END IF ;

    END;
    /

  • 13. Re: How to get SP body inside Database Trigger
    Hoek Guru
    Currently Being Moderated

    Thanks for the example, but I cannot test with it at this time (but will do later today, if your problem still exists then),

    Getting back to one of my previous remarks (make the program invokers rights) , what you could investigate is this:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1464804639878#28729949069626

    But still I oppose to the fact that you're using SYS for this purpose. SYS is special and not meant for these kind of purposes.

    It can lead to unexpected behaviour/results.

     

    What you also could try is to create a trigger for each schema.

    But actually, perhaps it's not possible to capture the create, see:

    Ask Tom "Writting DDL_EVENT Triggers"

  • 14. Re: How to get SP body inside Database Trigger
    AliBudak Newbie
    Currently Being Moderated

    If I find a solution, I will write here. Thanks again.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points