Oracle improbable errors, or should I say bugs?

SQL> create trigger DUSER.H_SASTAB_BI BEFORE INSERT ON DUSER.H_SASTAB FOR EACH ROW BEGIN   SELECT DUSER.H_SEQ.nextval INTO :new.h_node_id FROM dual; END; /   Warning: Trigger created with compilation errors.   SQL> show err   LINE/COL ERROR -------- ------------------------------------------------------ 3/15     PLS-00302: component 'H_SEQ' must be declared

 

PLS-00302: component "string" must be declared

Cause: In a reference to a component (for example, in the name "A.B", "B" is a component of "A"), the component has not been declared. The component might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.

Action: Check the spelling and declaration of the component. Also confirm that the declaration is placed correctly in the block structure.

 

Straightforward: H_SEQ does not exist, right?

 

SQL> select owner, object_name, object_type        from dba_objects       where object_name = 'H_SEQ';   OWNER   OBJECT_NAME      OBJECT_TYPE ------- ---------------- -------------------------------------DUSER   H_SEQ            SEQUENCE

 

As DUSER owns the sequence let us try without qualifying the sequence:

 

SQL> create trigger DUSER.H_SASTAB_BI BEFORE INSERT ON DUSER.H_SASTAB FOR EACH ROW BEGIN   SELECT H_SEQ.nextval INTO :new.h_node_id FROM dual; END; /   Trigger created.

 

Wow! My best guess? There is a table with the same name as the owner DUSER. Therefore

when the sequence was prefixed by the schema name, actually Oracle was checking for

a column named H_SEQ in the DUSER table. As such column does not exist we received the

error PLS-00302: component 'H_SEQ' must be declared.

 

SQL> select owner, object_name, object_type        from dba_objects       where object_name = 'DUSER';   OWNER   OBJECT_NAME      OBJECT_TYPE ------- ---------------- -------------------------------------DUSER   DUSER            TABLE

 

Why should the application have a table with the same name as the application owner?

Well, to tell you the truth I really don't want to know :o)

 

Anyway, it does not make sense, Oracle should be smarter than that – it should realize this is

a sequence. So, a new guess: there is a bug. Indeed, BUG: 1218383.

Checking it, it seems something that was fixed in the past, but is back. BTW, the database with

this issue was migrated from 10.2.0.4 to 11.2.0.3.

 

A Sequence Prefixed by Schema Name Fails with PLS-00302: component 'SEQUENCE_NAME'

must be declared [ID 1371201.1].

Standards... keep them updated!

I like this definition from Business Dictionary:

A couple of weeks ago I was working on a database refresh. As the database is not so big (800 GB) and it is a test environment I decided to recreate the database from the scratch and import the data instead of using the restore/recover approach (Data Pump does a great job recreating everything :o)  - both databases use the same version and platform (looking back I should use the ordinary restore/recover process).

Four or five years ago I created a set of scripts to create databases. It is pretty nice: there is just one script that needs customization (the parameters script), and after defining all parameters it is just a matter of starting the main script, and we can sit and relax watching the database being created - as if that were possible in our multitask work environments :o( Anyway, I used these scripts and the database was created successfully, so I started the import and less than 2 minutes later it just failed:

Processing object type SCHEMA_EXPORT/USER ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [USER:"ABC"] ORA-01403: no data found ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 8164 ----- PL/SQL Call Stack -----   object      line  object   handle    number  name 0x78a3b9a8   19028  package body SYS.KUPW$WORKER 0x78a3b9a8    8191  package body SYS.KUPW$WORKER 0x78a3b9a8   15631  package body SYS.KUPW$WORKER 0x78a3b9a8    3944  package body SYS.KUPW$WORKER 0x78a3b9a8    8874  package body SYS.KUPW$WORKER 0x78a3b9a8    1651  package body SYS.KUPW$WORKER 0x78a12340       2  anonymous block

To help to identify the issue I ran Data Pump again, this time using the parameter TRACE hoping to get more information about the error – see note Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1].

Analyzing the trace generated by the failing work process it was possible to identify the error occurred just after calling the function DBMS_METADATA.CONVERT:

KUPW:12:37:14.549: 1: DBMS_METADATA.CONVERT returned.              

KUPW:12:37:14.560: 1: In procedure DETERMINE_FATAL_ERROR

Considering Data Pump stores the data to recreate the DDL statements in XML format and DBMS_METADATA.CONVERT is used to transform XML documents, I suspected something was wrong with the XSLT style sheet used to get the DDL. Looking at table METAXSL$ under SYS schema it is possible to identify the style sheet used to transform the XML coming from Data Pump dump file. Once you get the style sheet name (column SCRIPT), look at table METASTYLESHEET to get the complete style sheet. Well, the style sheet looked ok; I compared it against the source database, and both records were the same (for reference the style sheet name is kuuser).

Back to zero ground, I decided to run Data Pump again this time tracing the Data Pump worker sessions using event 10046. Analyzing the trace files I got one more table: SYS.METAXSLPARAM$ - checking ORACLE_HOME/rdbms/admin/catmeta.sql, I see it is used to define all the valid transformation parameters for each dictionary object type and transform the user can set through the DBMS_METADATA.SET_TRANSFORM_PARAM method. Comparing it against the source database I found 1 mismatch: a missing back slash in the reord with the column PARAM equal to PRS_DELIM - it should be "\{]`" instead of "{]`".

Hmm, the back slash is not a strange character; it is the default SQL*Plus escape character! BTW, one of the scripts has the command SET ESCAPE ON, as the convention used to create the data files is DB_NAME.filename.dbf, so the scripts use something like this when specifying file names: &&DATABASE_NAME\.filename.dbf

Database recreated and lesson learned: standards are good, we just need to keep them up to date, reviewing and evaluating them on a regular basis.

Later I found on Oracle Support site this note: DataPump Import (IMPDP) Fails with Errors ORA-39126 ORA-1403 No Data Found [ID 729346.1]. This time if I had checked Oracle Support at first I would have saved some time.