This discussion is archived
9 Replies Latest reply: Feb 10, 2013 1:59 AM by 986422 RSS

Error while creating tables in schema since trigger already enabled

986422 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    >
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you very much for all your quick response

Legend

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