9 Replies Latest reply: Feb 10, 2013 3:59 AM by 986422 RSS

    Error while creating tables in schema since trigger already enabled

    986422
      Hi All,

      I am trying to insert newly created objects in userA,but am not able to insert "ora_dict_obj_type" type value in target table,
      Please see my error and script below:

      Error:
      Error report:
      SQL Error: ORA-00604: error occurred at recursive SQL level 1
      ORA-01858: a non-numeric character was found where a numeric was expected
      ORA-06512: at line 2
      00604. 00000 - "error occurred at recursive SQL level %s"
      *Cause:    An error occurred while processing a recursive SQL statement
      (a statement applying to internal dictionary tables).
      *Action:   If the situation described in the next error on the stack
      can be corrected, do so; otherwise contact Oracle Support.


      Script:I need to insert newly created objects into this table,here Object type is important because based object type i am going to create one more trigger to grant access automatically to other users using trigger.

      create table access_table
      (owner_name varchar2(30),
      object_name varchar2(30),
      object_type varchar2(30),
      created_time timestamp default current_timestamp);

      create or replace
      trigger access_trigger
      after create on schema
      begin
      insert into access_table values
      (ora_dict_obj_owner,
      ora_dict_obj_name,
      ora_dict_obj_type,
      current_timestamp);
      end access_trigger;

      Thanks

      Edited by: 983419 on Feb 9, 2013 7:20 AM
        • 1. Re: Error while creating tables in schema since trigger already enabled
          sb92075
          983419 wrote:
          Hi All,

          I am trying to insert newly created objects in userA,but am not able to insert "ora_dict_obj_type" type value in target table,
          Please see my error and script below:

          Error:
          Error report:
          SQL Error: ORA-00604: error occurred at recursive SQL level 1
          ORA-01858: a non-numeric character was found where a numeric was expected
          ORA-06512: at line 2
          00604. 00000 - "error occurred at recursive SQL level %s"
          *Cause:    An error occurred while processing a recursive SQL statement
          (a statement applying to internal dictionary tables).
          *Action:   If the situation described in the next error on the stack
          can be corrected, do so; otherwise contact Oracle Support.


          Script:I need to insert newly created objects into this table,here Object type is important because based object type i am going to create one more trigger to grant access automatically to other users using trigger.

          create table access_table
          (owner_name varchar2(30),
          object_name varchar2(30),
          object_type varchar2(30),
          created_time timestamp default current_timestamp);

          create or replace
          trigger access_trigger
          after create on schema
          begin
          insert into access_table values
          (ora_dict_obj_owner,
          ora_dict_obj_name,
          ora_dict_obj_type,
          current_timestamp);
          end access_trigger;

          Thanks

          Edited by: 983419 on Feb 9, 2013 7:20 AM
          since we don't know exactly what you do, we can't say what you do wrongly.

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          is COPY & PASTE broken for you?
          • 2. Re: Error while creating tables in schema since trigger already enabled
            986422
            1. I created one trigger as below
            create or replace
            trigger access_trigger
            after create on schema
            begin
            insert into access_table values
            (ora_dict_obj_owner,
            ora_dict_obj_name,
            ora_dict_obj_type,
            current_timestamp);
            end access_trigger;

            2.i need to insert newly created objects into "access_table".
            3.i am trying to create one table in this schema with below script.

            CREATE TABLE orders_audit
            ( order_id number(5),
            quantity number(4)
            );

            4.But am unable create ORDERS_AUDIT table in this schema since access_trigger is enabled.
            Error while creating table:

            Error starting at line 1 in command:
            CREATE TABLE orders_audit
            ( order_id number(5),
            quantity number(4)
            )
            Error at Command Line:1 Column:0
            Error report:
            SQL Error: ORA-00604: error occurred at recursive SQL level 1
            ORA-01858: a non-numeric character was found where a numeric was expected
            ORA-06512: at line 2
            +00604. 00000 - "error occurred at recursive SQL level %s"+
            *Cause:    An error occurred while processing a recursive SQL statement+
            +(a statement applying to internal dictionary tables).+
            *Action:   If the situation described in the next error on the stack+
            can be corrected, do so; otherwise contact Oracle Support.
            • 3. Re: Error while creating tables in schema since trigger already enabled
              stefan nebesnak
              When executing insert statement, a problem was encountered and the general error message ORA-00604 was returned followed by the actual error message ORA-01858 that should be corrected.
              ORA-01858: a non-numeric character was found where a numeric was expected
              insert into access_table values
              (ora_dict_obj_owner, --VARCHAR(30)
              ora_dict_obj_name, --VARCHAR(30)
              ora_dict_obj_type, --VARCHAR(20)
              current_timestamp --"TIMESTAMP WITH TIME ZONE"
              );
              The Oracle CURRENT_TIMESTAMP function will give you the current time with all details. It returns the current timestamp with time zone for the current session's time zone.
              --example
              SQL> select CURRENT_TIMESTAMP from dual;
              CURRENT_TIMESTAMP
              -------------------------------------------------
              09-FEB-13 02.53.33.753000 PM 03:00
              983419 wrote:create table access_table
              (owner_name varchar2(30),
              object_name varchar2(30),
              object_type varchar2(30),
              created_time timestamp default current_timestamp);
              You are inserting 'CURRENT_TIMESTAMP' ( TIMESTAMP WITH TIME ZONE datatype ) into the column with TIMESTAMP datatype.

              Try this:
              create table access_table
              (owner_name varchar2(30),
              object_name varchar2(30),
              object_type varchar2(30),
              created_time TIMESTAMP WITH TIME ZONE default CURRENT_TIMESTAMP);
              Edited by: stefan nebesnak on Feb 9, 2013 1:27 PM
              • 4. Re: Error while creating tables in schema since trigger already enabled
                ranit B
                insert into access_table values
                (ora_dict_obj_owner, --VARCHAR(30)
                ora_dict_obj_name, --VARCHAR(30)
                ora_dict_obj_type, --VARCHAR(20)
                current_timestamp --"TIMESTAMP WITH TIME ZONE"
                );
                The Oracle CURRENT_TIMESTAMP function will give you the current time with all details. It returns the current timestamp with time zone for the current session's time zone.
                --example
                SQL> select CURRENT_TIMESTAMP from dual;
                CURRENT_TIMESTAMP
                -------------------------------------------------
                09-FEB-13 02.53.33.753000 PM 03:00
                You are inserting 'CURRENT_TIMESTAMP' ( TIMESTAMP WITH TIME ZONE datatype ) into the column with TIMESTAMP datatype.
                So what?? Can you please do some workout on what you said and post it here?
                Try this:
                create table access_table
                (owner_name varchar2(30),
                object_name varchar2(30),
                object_type varchar2(30),
                created_time TIMESTAMP WITH TIME ZONE default CURRENT_TIMESTAMP);
                NO NEED.

                Check this -
                ranit@XE11GR2>> create table access_table
                  2  (owner_name varchar2(30),
                  3  object_name varchar2(30),
                  4  object_type varchar2(30),
                  5  created_time timestamp default current_timestamp);
                
                Table created.
                
                Elapsed: 00:00:00.07
                ranit@XE11GR2>> insert into access_table(owner_name,object_name,object_type,created_time)
                  2  values('rr','bb','xx',current_timestamp); -- "CURRENT_TIMESTAMP with TIME ZONE inserted"
                
                1 row created.
                
                Elapsed: 00:00:00.00
                ranit@XE11GR2>> insert into access_table(owner_name,object_name,object_type)
                  2  values('rr2','bb2','xx2');
                
                1 row created.
                
                Elapsed: 00:00:00.01
                
                ranit@XE11GR2>> select *
                  2  from access_table;
                
                OWNER_NAME                     OBJECT_NAME                    OBJECT_TYPE                    CREATED_TIME
                ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
                rr                             bb                             xx                             09-FEB-13 01.47.56.490000 PM
                rr2                            bb2                            xx2                            09-FEB-13 01.48.20.187000 PM
                
                Elapsed: 00:00:00.01
                Edited by: ranit B on Feb 10, 2013 3:20 AM
                -- code added
                • 5. Re: Error while creating tables in schema since trigger already enabled
                  ranit B
                  >
                  create or replace trigger access_trigger
                  after create
                  on schema
                  begin
                  insert into access_table values(
                  ora_dict_obj_owner,
                  ora_dict_obj_name,
                  ora_dict_obj_type,
                  current_timestamp
                  );
                  end access_trigger;
                  >
                  From where are these values- ora_dict_obj_owner , ora_dict_obj_name , ora_dict_obj_type coming from??
                  Just a guess ,please check if there's any data type mis-match between these values and the column types.

                  Also, please add an exception block just to track the exact line no-
                  EXCEPTION
                  When Others then
                   dbms_output.put_line('Error at line- '||DBMS_UTILITY.FORMAT_ERROR_EXCEPTION||' - '||SQLERRM);
                  • 6. Re: Error while creating tables in schema since trigger already enabled
                    stefan nebesnak
                    ranit B wrote:
                    >
                    insert into access_table values(
                    ora_dict_obj_owner,
                    ora_dict_obj_name,
                    ora_dict_obj_type,
                    current_timestamp
                    );
                    end access_trigger;
                    >
                    From where are these values- ora_dict_obj_owner , ora_dict_obj_name , ora_dict_obj_type coming from??
                    They are System Defined Event Attributes.

                    http://docs.oracle.com/cd/A97630_01/appdev.920/a96590/adg14evt.htm
                    • 7. Re: Error while creating tables in schema since trigger already enabled
                      stefan nebesnak
                      ranit B wrote:

                      So what?? Can you please do some workout on what you said and post it here?
                      Try this:
                      create table access_table
                      (owner_name varchar2(30),
                      object_name varchar2(30),
                      object_type varchar2(30),
                      created_time TIMESTAMP WITH TIME ZONE default CURRENT_TIMESTAMP);
                      NO NEED.

                      Check this -
                      ranit@XE11GR2>> create table access_table
                      2  (owner_name varchar2(30),
                      3  object_name varchar2(30),
                      4  object_type varchar2(30),
                      5  created_time timestamp default current_timestamp);
                      
                      Table created.
                      
                      Elapsed: 00:00:00.07
                      ranit@XE11GR2>> insert into access_table(owner_name,object_name,object_type,created_time)
                      2  values('rr','bb','xx',current_timestamp); -- "CURRENT_TIMESTAMP with TIME ZONE inserted"
                      
                      1 row created.
                      
                      Elapsed: 00:00:00.00
                      ranit@XE11GR2>> insert into access_table(owner_name,object_name,object_type)
                      2  values('rr2','bb2','xx2');
                      
                      1 row created.
                      
                      Elapsed: 00:00:00.01
                      
                      ranit@XE11GR2>> select *
                      2  from access_table;
                      
                      OWNER_NAME                     OBJECT_NAME                    OBJECT_TYPE                    CREATED_TIME
                      ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
                      rr                             bb                             xx                             09-FEB-13 01.47.56.490000 PM
                      rr2                            bb2                            xx2                            09-FEB-13 01.48.20.187000 PM
                      
                      Elapsed: 00:00:00.01
                      Edited by: ranit B on Feb 10, 2013 3:20 AM
                      -- code added
                      You didn't create the access_trigger.
                      983419 wrote:
                      4.But am unable create ORDERS_AUDIT table in this schema since access_trigger is enabled.
                      Error while creating table:
                      SQL Error: ORA-00604: error occurred at recursive SQL level 1
                      ORA-01858: a non-numeric character was found where a numeric was expected
                      • 8. Re: Error while creating tables in schema since trigger already enabled
                        jeneesh
                        983419 wrote:
                        Hi All,

                        Error:
                        Error report:
                        SQL Error: ORA-00604: error occurred at recursive SQL level 1
                        ORA-01858: a non-numeric character was found where a numeric was expected
                        ORA-06512: at line 2
                        00604. 00000 - "error occurred at recursive SQL level %s"
                        *Cause:    An error occurred while processing a recursive SQL statement
                        (a statement applying to internal dictionary tables).
                        *Action:   If the situation described in the next error on the stack
                        can be corrected, do so; otherwise contact Oracle Support.

                        How did you confirm, the error is because of the ACCESS_TRIGGER?

                        Do you have any other triggers? the script you have given is successfully executing in my 11gR2 DB..
                        create table access_table
                        (
                             owner_name varchar2(30),
                             object_name varchar2(30),
                             object_type varchar2(30),
                             created_time timestamp default current_timestamp
                        );   
                        
                        
                        table ACCESS_TABLE created.
                        
                        create or replace trigger access_trigger
                        after create on schema
                        begin
                             insert into access_table values
                             (
                               ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,
                               current_timestamp
                             );
                        end access_trigger;
                        
                        TRIGGER ACCESS_TRIGGER compiled
                        
                        CREATE TABLE orders_audit
                        ( order_id number(5),
                        quantity number(4)
                        );
                        
                        
                        select *
                        from access_table;
                        
                        WNER_NAME                     OBJECT_NAME                    OBJECT_TYPE                    CREATED_TIME                  
                        ------------------------------ ------------------------------ ------------------------------ -------------------------------
                        HR                             ORDERS_AUDIT                   TABLE                          10-FEB-13 11.25.49.453000000 AM 
                        • 9. Re: Error while creating tables in schema since trigger already enabled
                          986422
                          Thank you very much for all your quick response