SQL & PL/SQL :: Package Body Created With Compilation Errors (Warnings On)
Aug 6, 2013
I've compiled a package with warnings ON. Getting message SP2-0809: Package Body Created with Compilation Errors.When I checked the errors by issuing "Show Errors", it shows "No error".
SQL> ALTER PACKAGE PKG_ABC COMPILE PLSQL_WARNINGS = 'ENABLE:ALL';
SP2-0809: Package Body Created with Compilation Errors
SQL> SHOW ERRORS
No Errors
how to check errors for that, or oracle simply pointing that package might have performance issue.
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection AS PROCEDURE sp_final_load_mkt_hub; END pkg_mkt_hub_load_collection; / CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection AS c_default_limit CONSTANT PLS_INTEGER:=5000;
[code]....
show error
error code
SQL> @pkg_mkt_hub_load_collection.sql Package created. Warning: Package Body created with compilation errors. Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION: LINE/COL ERROR -------- ----------------------------------------------------------------- 57/4 PL/SQL: Statement ignored PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL' SQL>
Every quarterly, we export the data from DEEXTRA and import into INEXTRA user.This is not working after i made the changes 2 tables and views in DEEXTRA .
I added new columns to 2 tables and associated 2 views. After this change, my import process got failed, with message especially for those views. "IMP-00041: Warning: object created with compilation warnings"
before i change the tables, import process was working fine. My doubt is, views were created in INEXTRA before the tables in import functionality.
I had given the grants similary the other objects. I belive no problems with privilages.
Because of table changes the order of the objects in exporting got disturbed? like in the exporting functionality first views created then the table?or the order of the objects in importing got disturbed? like first views created and then tables?
I could see in teh log lot of IMP-00041: Warning: object created with compilation warnings
example: IMP-00041: Warning: object created with compilation warnings "CREATE FORCE VIEW "ARADMIN"."BMC_CORE_BMC_IMPACT" (" ""REQUESTID","SUBMITTER","CREATEDATE","ASSIGNEDTO","LASTMODIFIEDBY","MODIFIE" "DDATE","STATUS","SHORTDESCRIPTION","CMDBROWLEVELSECURITY","INSTANCEID","CMD"
CREATE or REPLACE TRIGGER my_trigger BEFORE UPDATE OF PASSWORD ON mytable FOR EACH ROW DECLARE BEGIN :NEW.PASSORD := 'xyzt'; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Error '|| sqlerrm); END trigger_create_user;
But, when i execute it, i get the following message:Warning: Trigger created with compilation errors.Even if i do nothing in trigger body:
CREATE or REPLACE TRIGGER my_trigger BEFORE UPDATE OF PASSWORD ON mytable FOR EACH ROW DECLARE BEGIN END trigger_create_user;
ORA-31626: job does not exist ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_ LOGSTDBY" ORA-06512: at "SYS.KUPV$FT", line 949 ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"
I have compiled some procedures, at the time of compilation i saw some warnings & errors but i did not notice, Now can we see that errors or warnings in db console or any other way.
When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?
I know that this my question is a little to much for some DB tool like TOAD, but maybe this tip is exist and I can't find it!
Does TOAD when I write something wrong in PL/SQL (trigger for example) and after compilation (run as script) when show message:
"Warning: compiled but with compilation errors" to show what is wrong in sql code? I found this option is some db tools, but I can't find this in TOAD, of course if it can do this!
Up until today we assumed that compiling a package BODY-only would not cause invalidation of other code. However when we compiled the body of the HTP package, the OWA package became invalid and required re-compilation.
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production PL/SQL Release 10.2.0.4.0 - Production CORE10.2.0.4.0Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.
I tried to create a UTL_SMTP package using '@?/rdbms/admin/utlmtp.sql' script but there are no package body for UTL_SMTP is created. There are only UTL_SMTP package is create.
1- I do not have access to TOAD for Oracle yet 2- I can connect to SQLPLUS: Release 9.2.0.1.0 3- We have many stored procedures in packages that are available from the TOAD for Oracle interface 4- I need to be able to see the specs/body of some packages containing some procedures. 5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?
For example: SchemaName1.PackageName1.ProcedureName1
I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS type table_name_i is varray(100) of VARCHAR2(30); c_add constant number := 1; c_del constant number := 2; c_select_frm_tmp constant number := 1; [code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT ( TEST_ID NUMBER(9), TEST_DESC VARCHAR(30) ) / CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE / [code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).
I have tried both parallel and non-parallel.how to improve speed of the package body import.
Details about the Schema & Import No. of objects in Schema
SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);
OBJECT_TYPE COUNT(1) ------------------- ---------- FUNCTION 248 INDEX 5161 JAVA CLASS 471 JAVA RESOURCE 1 JAVA SOURCE 16 LIBRARY 1
I applied this patch in our development environment. After validation, I need to apply the same in production, training and publication environments. The detailed information follows :
PowerPC_POWER5 / aix 5300-12 OPatch utility used : 11.2.0.3.0 OUI version : 11.2.0.2.0 Prereq "checkConflictAgainstOHWithDetail" passed. Composite patch 13923804 successfully applied. OPatch Session completed with warnings.
Export: Release 10.2.0.3.0 - Production on Thu Dec 6 15:06:21 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
EXP-00010: DISTESTA is not a valid username Export terminated successfully with warnings.
C:Documents and SettingsAdministrator>
When i connect from sqlplus, it doesn't give any erro. What could be the reason?
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
