SQL & PL/SQL :: Hide Declaration Or Declare It Inside The Body Of Package
Mar 11, 2010
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?
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).
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 can't understand the following cursor declaration (inside the DECLARE of a PL/SQL block)
CURSOR c_emps IS SELECT emp_large_ot(empno, ename, job, mgr,hiredate, sal, comm, deptno) FROM emp_large; emp_large_ot is an object type created as CREATE TYPE emp_large_ot AS OBJECT ( empno NUMBER , ename VARCHAR2(10) , job VARCHAR2(9)
[code]...
and emp_large is similar to the standard emp table
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.
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 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
I'm making a function A that does many calls to procedures in an other package B. To make this function more readable, I'd like to specify synonyms for the procedures in B. I only need the synonyms inside this function, I don't want to make database synonyms.
For example:
Function get_all_employees return clob is v_emp clob; begin
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace 2 PACKAGE new_pack 3 AS 4 g_id employee_details1.employee_id%type; 5 PROCEDURE emp_project( 6 st_date IN DATE, 7 Prj_id out VARCHAR2, 8 prj_name out VARCHAR2, 9 Prj_location out VARCHAR2); 10 11 END new_pack; 12 /
Package created.
SQL> CREATE OR REPLACE 2 PACKAGE body new_pack 3 AS 4 PROCEDURE emp_project( 5 st_date IN DATE, 6 Prj_id OUT VARCHAR2, 7 prj_name OUT VARCHAR2, 8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR -------- ----------------------------------------------------------------- 12/12 PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table LONG_ double ref char time timestamp interval date binary national character nchar
We are facing a new type of issue , the issue is the variable declaration and assigning a value for it is not properly used in the procedure of a package.
for ex:
create or replace procedure test is v_type VARCAHR2(5) := 'XX'; v_awb varchar2(5) := 'AL'; BEGIN Insert into tt(col1,col2,col3,col4) select v_type,v_awb,col3,col4 from xxx; commit; END;
Above is the sample procedure used ,here i am declaring a variable v_type and v_awb and assigning a value for it and inserting them into a table,but the concern is the values declared is always inserting a NULL value instead of the assigned value ,but in our local we could not reproduce it. The issue is occurring in RAC oracle machine,and the version is below
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
I have this Error in PL /SQL procedure ORA-06550: line 6, column 12:PLS-00320: the declaration of the type of this expression is incomplete or malformedORA-06550: line 6, column 12:PL/SQL: Item ignoredwhere this is my procedure
CREATE OR REPLACE PROCEDURE MOAMALAT."IO_EMP_REP" (P_FROMDATE IN NUMBER,P_TODATE IN NUMBER,P_EMPID IN NUMBER,RCT1 OUT GLOBALPKG.RCT1)ASBEGINOPEN RCT1 FOR SELECT COUNT (I.CORRESPONDENCENUMBER) cont,EMP.FULLNAME empname,D.DEPARTMENTNAME deptnameFROM MOAMALAT.IO_INCOMING i,MOAMALAT.IO_EMPLOYEES emp,MOAMALAT.IO_DEPARTMENTS dWHERE I.RECEIVEDBY = (SELECT EM.USERIDFROM MOAMALAT.IO_EMPLOYEES emWHERE EM.EMPLOYEEID = P_EMPID)AND I.RECEIVEDBY LIKE EMP.USERID--and EMP.DEPARTMENTID=1900AND I.RECEIVEDBYDEPARTMENTID = D.DEPARTMENTIDAND I.CORRESPONDENCEDATE BETWEEN
i 'm using APEX 4.2.1.00.08 and i 'm wondering if there is a way to translate "Function and Global Variable Declaration" textarea. Every other script textarea is available for translation, but not this.
I know that this is code is loaded on header but may contains important alert messages of global functions.
declare ret_val number; begin exec p_buildinfo('252657020001', to_date('20120820','YYYYMMDD'),to_date('20120928','YYYYMMDD'),ret_val, 0); DBMS_OUTPUT.PUT_LINE('Value Returned Is : '||ret_val) ; end;
I getting the below error
ORA-06550: line 3, column 10: PLS-00103: Encountered the symbol "P_BUILDINFO" when expecting one of the following:
:= . ( @ % ; The symbol ":=" was substituted for "P_BUILDINFO" to continue.
the procedure structure is
CREATE OR REPLACE PROCEDURE p_buildsinfo ( var_p_cod CHAR := NULL, var_p_dat_from DATE := NULL, var_p_dat_to DATE := NULL, po_var_l_nxt_seq IN OUT NUMBER, var_p_consol_flg NUMBER default 0 )
