SQL & PL/SQL :: Compiling Package BODY Causes Invalidation?
Aug 25, 2010
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'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.
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?
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"
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?
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>
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
In a document from the oracle website. "If a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency".
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 am new to oracle,when i am compiling below procedure,
create or replace procedure GetTempPolicyDetails2(i_officeid in number,pr_cursor out SYS_REFCURSOR) as
L_QUEY VARCHAR2(4000):= 'select POLTMP.POLICY_ID,POLTMP.CORPORATE_NAME,POLTMP.POLICY_FROM_DATE,POLTMP.POLICY_TO_DATE, POLTMP.NETPREMIUM_WITH_SERVICE_TAX, IOF.OFFICENAME,UW.NAME,REG.REGNAME,brmstr.brokername,created_on from policies_temp POLTMP INNER JOIN ISSUINGOFFICE IOF ON IOF.ID=POLTMP.Divisionid INNER JOIN REGION REG ON REG.ID=IOF.RID INNER JOIN underwriters UW ON UW.ID=REG.UWID LEFT OUTER JOIN broker_master BRMSTR ON BRMSTR.ID=POLTMP.Broker_Name where POLTMP.Policy_Status=1 where 1=1' L_WHERE VARCHAR2(4000):='WHERE 1=1'; [code]....
I have a requirement to reformat some data elements that consist of two numbers separated by a hyphen e.g. 123-456. I have written a function to get the first number but it compiles with a warning.
CREATE OR REPLACE FUNCTION get_lowernumber (housenumber varchar2) RETURN varchar2 IS v_hyphen number; [code].......
I'm getting the following error on a procedure that I was able to compile until today after some changes.
ORA-06550: line 354, column 20: PLS-00103: Encountered the symbol "AND" when expecting one of the following: . then or The symbol ". was inserted before "AND" to continue.
I am trying to resolve by using a cursor C2 inside the already existing cursor C1, i will use a inner loop to get the select statement value to cursor c2 then i will assign the vlaues the variables, then once i read all the values of cursor c2 i will exit inner loop and go to outer loop to read the next value of c1 and then again go to cursor c2 and inner loop.
I have pasted the code below.
Declare temp1 source.source%type; temp2 QUOTE_LETTERS_MASTER%rowtype; Cursor c1 is Select * from Source;
I am getting this err ora-31640 while compling a menu in forms6i and when i have searched in oracle it is giving in forms 6i shared submenu's are not supported.
I have a form, using views which has tables from other databases.
e.g. :my database name is G1 here i have a view
MR_HEAD
CREATE OR REPLACE VIEW MR_HEAD AS SELECT "MR_HEAD_KEY","MR_TRAN_TYPE","MR_NO","MR_DATE","PO_TYPE","PO_NO","VENDOR_CODE", "MODIFY_FLAG","SUPERSEDE_FLAG","REF_MR_HEAD_KEY","PO_KEY","PO_DATE","REJECTION_NO", "USER_ID","LAST_UPD_DATE" FROM INMATMAS.MR_HEAD@SPICFIN
i am linking my database G1 with Above Database. so after using this in my form as
'INMATMAS.mr_head@DB_OPTIMAT'[/email]
for insert/select. i cant able to compile it. form closes immediately when i compile this.
We are porting our application from HP-UX to Sun Solaris and as part of that I am trying the compile a Pro*C program in Sun Solaris using SUNWspro C++ compiler. Precompiling is failing with following error.
System default option values taken from: /u01/app/oracle/product/10.2.0/precomp/admin/pcscfg.cfg
Syntax error at line 44, column 30, file /u01/app/SUNWspro/prod/include/CC/stlport4/algorithm: Error at line 44, column 30 in file /u01/app/SUNWspro/prod/include/CC/stlport4/a lgorithm # include _STLP_NATIVE_HEADER(algorithm) .............................1 PCC-S-02014, Encountered the symbol "(" when expecting one of the following:
[code]...
Normal C++ files are getting compiled with out eny issues. This particular file is having functions written in C fashion.
