This content has been marked as final. Show 13 replies
Is your package created using quoted identifiers?
So, when using quoted identifiers, the name of your object becomes case-sensitive...you should check that.
SQL> create table "Bla" as select * from dual; Table created. SQL> drop table bla; drop table bla * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table "Bla"; Table dropped.
I run some scripts like. I don't know is the enough to undersatnd the issue.
Inserting Into His_Meldungen for F4524 1 row created. 1 row created. Inserting Into His_Meldungen is complete. Please commit the changes. Renaming duplicate some column values PL/SQL procedure successfully completed. Rename null columns name PL/SQL procedure successfully completed. Add a uinique key constraint to a table Adding uinique key constraint for Name,Cod_id_sg_generation_pav,Cod_id_pav_typ columns in His_Pav_Se Alter trigger TIU_DATI_HPS for O2108b5 Trigger created. adding index to status column in his_pav_sequences table Index created. SP2-0734: unknown command beginning "PROMPTexec..." - rest of line ignored. ---Package failed Warning: Package created with compilation errors. Warning: Package Body created with compilation errors. SQL> show errors; Errors for PACKAGE BODY PK_PAV_REF: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PL/SQL: Compilation unit analysis terminated 1/21 PLS-00304: cannot compile body of 'PK_PAV_REF' without its specification 1/21 PLS-00905: object HISADM.PK_PAV_REF is in
Search for this text ""PROMPTexec" in the file which you are executing and include a space between the prompt and exec. Btw, fixing this line will not solve the actual problem this will sort the sp2 error.
The line after that should be the script which is trying to create a package "PK_PAV_REF" and it fails. Execute that line on its own and then type show errors.
I hope you are aware that you cannot create a package body without the package specification.
Hope it makes sense and hopefully you will be able to solve the issue by yourself.
It doesn't allow to modify and drop.
Again copy & paste what you have tried so far rather than trying to explain your problem in plain english.
What I am asking you to do is
substitute create_package.sql with the relevant file or command you are using to create the package.
@ create_package.sql show errors
The implementation of all pipelined functions is supported by object and collection types. Oracle provides three ways of defining these types, but most developers will either create their own object and collection types explicitly or rely on "versioned types" that Oracle generates from PL/SQL packaged record and collection declarations. There is an interesting bug with versioned types that is worthy of note in this article.
To demonstrate the issue, we will create a pipelined function that relies on versioned types for its implementation. To do this, we simply create a package with a global record type, a collection type based on this record type and the pipelined function itself, as follows.
Remember that pipelined functions require SQL types to be able to pipe collections of data to the consumer. Because we have only declared PL/SQL types to support our pipelined function, Oracle creates the SQL types on our behalf. We can see these in the dictionary using the following type of query.
SQL> CREATE PACKAGE etl_pkg AS 2 3 TYPE plsql_record_type IS RECORD 4 ( a1 VARCHAR2(30) 5 , a2 VARCHAR2(30) 6 , a3 VARCHAR2(30) 7 ); 8 9 TYPE plsql_nested_table_type 10 IS TABLE OF plsql_record_type; 11 12 FUNCTION parallel_fx 13 RETURN plsql_nested_table_type 14 PIPELINED; 15 16 END etl_pkg; 17 / Package created.
The type names are system-generated but contain the object ID of the package that the types were created for (as an aside, if we base our PL/SQL types on a table%ROWTYPE, the corresponding object ID will be that of the table).
SQL> WITH t AS ( 2 SELECT object_id AS o 3 FROM user_objects 4 WHERE object_name = 'ETL_PKG' 5 AND object_type = 'PACKAGE' 6 ) 7 SELECT type_name, typecode 8 FROM user_types 9 WHERE type_name LIKE 'SYS%' 10 AND type_name LIKE '%' || (SELECT o FROM t) || '%'; TYPE_NAME TYPECODE ------------------------------ ------------------------------ SYS_PLSQL_53640_33_1 COLLECTION SYS_PLSQL_53640_9_1 OBJECT SYS_PLSQL_53640_DUMMY_1 COLLECTION 3 rows selected.
The issue we will see arises when we create synonyms for the versioned types. We are unlikely to do this knowingly (we have no need to access these types; access to the package is all that is required to execute the pipelined function). Nevertheless, some environments automatically generate synonyms for all objects created for their applications, so we will replicate something similar. In the following example, we will imagine that the supplied SH schema is our application user, with SCOTT being the application owner. SCOTT creates synonyms in the SH schema for all of its objects, as follows.
Versioned types are so-named for a reason. If we recompile the package specification and repeat our query over USER_TYPES, we see that the trailing integer in the type name increases, as follows.
SQL> DECLARE 2 v_ddl VARCHAR2(1024) := 'CREATE SYNONYM sh."%s" FOR "%s"'; 3 BEGIN 4 FOR r IN (SELECT object_name FROM user_objects) LOOP 5 EXECUTE IMMEDIATE 6 REPLACE(v_ddl, '%s', r.object_name); 7 END LOOP; 8 END; 9 / PL/SQL procedure successfully completed.
Our supporting types have been re-generated by Oracle and a new "version" has been created. If we use CREATE OR REPLACE, the "version numbers" are reset to 1. However, because we have already recompiled this package, it appears as though we have no more chances to change its state, as follows.
SQL> ALTER PACKAGE etl_pkg COMPILE; Package altered. SQL> WITH t AS ( 2 SELECT object_id AS o 3 FROM user_objects 4 WHERE object_name = 'ETL_PKG' 5 AND object_type = 'PACKAGE' 6 ) 7 SELECT type_name, typecode 8 FROM user_types 9 WHERE type_name LIKE 'SYS%' 10 AND type_name LIKE '%' || (SELECT o FROM t) || '%'; TYPE_NAME ------------------------------ SYS_PLSQL_53640_33_2 SYS_PLSQL_53640_9_2 SYS_PLSQL_53640_DUMMY_2 3 rows selected.
We have hit bug number 3744836. We receive the same message if we try to drop the package, as follows.
SQL> ALTER PACKAGE etl_pkg COMPILE; ALTER PACKAGE etl_pkg COMPILE * ERROR at line 1: ORA-04043: object SYS_PLSQL_53640_33_1 does not exist
Note the version number of the type that we are being told does not exist. We know from above that our system-generated types have been incremented to version 2. We can also verify that we have no version 1 types as follows.
SQL> DROP PACKAGE etl_pkg; DROP PACKAGE etl_pkg * ERROR at line 1: ORA-04043: object SYS_PLSQL_53640_33_1 does not exist Finally, if we try to replace the package specification, we receive the following compilation errors. SQL> CREATE OR REPLACE PACKAGE etl_pkg AS 2 c INTEGER; 3 END etl_pkg; 4 / Warning: Package created with compilation errors. SQL> sho err Errors for PACKAGE ETL_PKG: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 ORA-04043: object SYS_PLSQL_53640_33_1 does not exist
no rows selected
SQL> WITH t AS ( 2 SELECT object_id AS o 3 FROM user_objects 4 WHERE object_name = 'ETL_PKG' 5 AND object_type = 'PACKAGE' 6 ) 7 SELECT type_name 8 FROM user_types 9 WHERE type_name LIKE 'SYS%' 10 AND type_name LIKE 'SYS_PLSQL_' || (SELECT o FROM t) || '%_1';
Of course, we created some synonyms earlier in the application user's schema (SH) and these are at version 1. But these are in another schema and we are trying to drop our own objects. Fortunately, we are able to see the root of this problem quite easily in a SQL trace file, so we will enable SQL trace and attempt to drop the ETL_PKG package, as follows.
SQL> ALTER SESSION SET SQL_TRACE = TRUE; Session altered. SQL> DROP PACKAGE etl_pkg; DROP PACKAGE etl_pkg * ERROR at line 1: ORA-04043: object SYS_PLSQL_53640_33_1 does not exist SQL> ALTER SESSION SET SQL_TRACE = FALSE;
The trace file leads us to the cause of this problem. Before Oracle can drop, recompile or replace a package with versioned types, it needs to identify the associated types and drop these first. In 10.2, Oracle uses the following SQL statement to identify the types. Each type is then dropped in turn.
PARSING IN CURSOR #25 len=109 dep=1 uid=0 oct=3 lid=0 tim=78367384109 hv=2962406971 ad='1d176f3c'
select UNIQUE name from obj$ where name like 'SYS_PLSQL@_53640@_%' escape '@' and type# != 10 order by name
END OF STMT
Note that there is no schema reference in this query. We can run this query directly, as follows.
The query returns the synonyms before our own schema's types! This means that Oracle will try to drop the version 1 synonym first, which of course doesn't exist as a type in our schema. To resolve this issue on 10.2, we can simply drop the redundant synonyms, as follows.
SQL> SELECT UNIQUE name 2 FROM obj$ 3 WHERE name LIKE 'SYS_PLSQL@_53640@_%' ESCAPE '@' 4 AND type# != 10 5 ORDER BY 6 name; NAME ------------------------------ SYS_PLSQL_53640_33_1 SYS_PLSQL_53640_33_2 SYS_PLSQL_53640_9_1 SYS_PLSQL_53640_9_2 SYS_PLSQL_53640_DUMMY_1 SYS_PLSQL_53640_DUMMY_2 6 rows selected.
We can now test that Oracle's recursive SQL statement returns the correct versioned type details, as follows.
SQL> BEGIN 2 FOR r IN (SELECT synonym_name 3 FROM dba_synonyms 4 WHERE owner = 'SH' 5 AND synonym_name LIKE 'SYS_PLSQL%') 6 LOOP 7 EXECUTE IMMEDIATE 8 'DROP SYNONYM sh.' || r.synonym_name; 9 END LOOP; 10 END; 11 / PL/SQL procedure successfully completed.
We should now be able to drop the package.
SQL> SELECT UNIQUE name 2 FROM obj$ 3 WHERE name LIKE 'SYS_PLSQL@_53640@_%' ESCAPE '@' 4 AND type# != 10 5 ORDER BY 6 name; NAME ------------------------------ SYS_PLSQL_53640_33_2 SYS_PLSQL_53640_9_2 SYS_PLSQL_53640_DUMMY_2 3 rows selected.
SQL> DROP PACKAGE etl_pkg;