Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier (3 threads merged by bb)
PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier (3 threads merged by bb) [message #379282] Mon, 05 January 2009 22:59 Go to next message
cherry
Messages: 56
Registered: December 2007
Member
I got the error msg when I ran this piece of code from
http://www.orafaq.com/wiki/PL/SQL_FAQ#How_can_one_keep_a_history_of_PL.2FSQL_code_changes.3F
Im not able to figure out what the problem is!
I know the description of ORA-00904, but dont know how to fix.
Any help?
Thanks

SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production

SQL> CREATE OR REPLACE TRIGGER change_hist
  2    AFTER CREATE ON APPS.SCHEMA
  3  DECLARE
  4  BEGIN
  5    if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
  6                               'PACKAGE', 'PACKAGE BODY', 'TYPE') then
  7  
  8       INSERT INTO SOURCE_HIST
  9              SELECT sysdate, user_source.* FROM USER_SOURCE
 10               WHERE  TYPE = DICTIONARY_OBJ_TYPE
 11                 AND  NAME = DICTIONARY_OBJ_NAME;
 12    end if;
 13  EXCEPTION
 14    WHEN OTHERS THEN
 15         raise_application_error(-20000, SQLERRM);
 16  END;
 17  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER CHANGE_HIST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/6      PL/SQL: SQL Statement ignored
9/28     PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier


[Updated on: Mon, 05 January 2009 23:07]

Report message to a moderator

Re: PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier (3 threads merged by bb) [message #379289 is a reply to message #379282] Mon, 05 January 2009 23:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Oracle changed the names from one version to another. It should be ora_dict instead of dictionary:

SCOTT@orcl_11g> CREATE TABLE SOURCE_HIST		    -- Create history table
  2    AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
  3    FROM   USER_SOURCE WHERE 1=2;

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER change_hist	     -- Store code in hist table
  2    AFTER CREATE ON SCOTT.SCHEMA	     -- Change SCOTT to your schema name
  3  DECLARE
  4  BEGIN
  5    if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
  6  				  'PACKAGE', 'PACKAGE BODY', 'TYPE') then
  7  	  -- Store old code in SOURCE_HIST table
  8  	  INSERT INTO SOURCE_HIST
  9  		 SELECT sysdate, user_source.* FROM USER_SOURCE
 10  		  WHERE  TYPE = ORA_DICT_OBJ_TYPE
 11  		    AND  NAME = ORA_DICT_OBJ_NAME;
 12    end if;
 13  EXCEPTION
 14    WHEN OTHERS THEN
 15  	    raise_application_error(-20000, SQLERRM);
 16  END;
 17  /

Trigger created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g>


I will update the FAQ.
Re: PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier (3 threads merged by bb) [message #379290 is a reply to message #379282] Mon, 05 January 2009 23:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
use <package_name>.<function_name>

i.e. DBMS_STANDARD.DICTIONARY_OBJ_TYPE instead of only DICTIONARY_OBJ_TYPE.

regards,
Delna
Re: PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier (3 threads merged by bb) [message #379291 is a reply to message #379290] Mon, 05 January 2009 23:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Dictionary_obj_type and dictionary_obj_name are from Oracle 8.1.5. They were changed to ora_dict_obj_type and ora_dict_obj_name in 8.1.6, but the old dictionary_obj_type and dictionary_obj_name are still in the dbms_standard package for backwards compatibility, so you could use that, but it is better to use the newer version as the older one may eventually not be available.

[Updated on: Mon, 05 January 2009 23:27]

Report message to a moderator

Re: PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier (3 threads merged by bb) [message #379294 is a reply to message #379282] Mon, 05 January 2009 23:47 Go to previous message
cherry
Messages: 56
Registered: December 2007
Member
Thanks much Barbara.
Thanks to delna for your insight.

Previous Topic: This 9i code is throwing error "ORA-06503: Function returned without value" when executed
Next Topic: Need Query??
Goto Forum:
  


Current Time: Wed Dec 07 14:56:14 CST 2016

Total time taken to generate the page: 0.06504 seconds