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?
View 9 Replies
ADVERTISEMENT
Feb 26, 2013
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).
View 11 Replies
View Related
Jul 26, 2011
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?
View 10 Replies
View Related
Jun 30, 2011
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
View 3 Replies
View Related
Jan 11, 2012
I need to find out the exact DML statement which invoked a particular trigger inside a trigger body.
View 7 Replies
View Related
Jan 6, 2013
I'm trying to create a package body but i keep getting an error message saying package created with complication.
CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
PROCEDURE MY_PROC(A NUMBER, B NUMBER)
IS
C NUMBER(4);
BEGIN
C:=A+B;
[code].......
View 2 Replies
View Related
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
View 6 Replies
View Related
Mar 31, 2011
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.
View 1 Replies
View Related
Jun 28, 2011
When i compile the package body i get errors.
CREATE OR REPLACE PACKAGE BODY CEE_OSPCM_PKG AS
PROCEDURE CEEOT_WORK_TYPE_PRC IS
CURSOR CUR_WORK_TYPE IS
SELECT *
[code]...
View 7 Replies
View Related
Mar 5, 2012
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.
View 4 Replies
View Related
Jan 8, 2013
I am trying to Spool a Package Body into a file using sqlplus. I tried 2 days:
WAY1
SET LINESIZE 32000;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET ECHO OFF;
SET TERM OFF;
SET FEEDBACK OFF;
[code]....
WAY2
.....
SELECT dbms_metadata.get_ddl( 'PACKAGE', 'SPI_xxxx_PCK', 'owner') FROM dual;
......
In Way1, I got different DDL or partial DDL. In Way2, I got only first 2 lines of Package body. I am running this command from Schema user only.
View 7 Replies
View Related
Sep 3, 2010
while compiling this package body, i get error
error 102 at line 21 column 11
encounterd symbol "Group" when expecting one of following
begin function package pragma procedure subtype type use form cursor
View 5 Replies
View Related
Aug 3, 2012
I sent a request for the DBA's to grant me rights to the package body but I only see the spec with the grant execute command.
Here is the command I just sent:
grant create any procedure to DEVELOPER1;
But the DBA changed my grant command to:
grant create procedure to DEVELOPER1;
But I still cannot see the package body. This is on a test environment.
Would the "create any procedure" command see the package body of different schema owners package bodies?
Or is there another grant command to see different schema package bodies?
View 4 Replies
View Related
Jan 27, 2011
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
View 39 Replies
View Related
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.
View 8 Replies
View Related
Jan 30, 2013
I am getting the below error when i am trying to import the dmp file into my oracle 11g.
impdp system/password@orcl dumpfile=aaa.dmp directory=datapump remap_schema=dev: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"
View 7 Replies
View Related
Mar 8, 2012
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>
View 11 Replies
View Related
Sep 13, 2012
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
ORA-00933: SQL command not properly ended
View 3 Replies
View Related
Apr 25, 2012
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
[Code]....
View 1 Replies
View Related
Nov 23, 2012
I was trying to create types inside package like:
-create or replace package pack1 is
type udtable is table of integer index by binary_integer;
end;
-create or replace package BODY pack1 is
type udtable is table of integer index by binary_integer;
end;
Although this is not exact query what i have doubt in my mind. is it possible to create object types/collections inside/with packages?
View 6 Replies
View Related
Sep 11, 2012
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
View 10 Replies
View Related
Apr 24, 2013
it is possible to undeclared a variable if so how dont worry am with you to solve any problem lets we can do it
View 1 Replies
View Related
Sep 29, 2010
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
View 1 Replies
View Related
Apr 20, 2012
I have two different tables with the same structure.
Depending on the parameter value to the procedure I need to process any one of the table .
declare
Query_string varchar2(500);
input_string varchar2(10) := 'E';
begin
Query_string := 'select * from ';
[Code]...
My intensition is like above snippet .But it's not working fine . If the input string 'E' then we need to process emp table , other wise archieve
View 3 Replies
View Related
Jul 16, 2013
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
[code]....
View 4 Replies
View Related
May 1, 2013
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.
View 1 Replies
View Related
Dec 22, 2010
the same query i put below but in oracle.
I'm trying to declare and set parameters in oracle
I'm trying to do something similar as the follow SQL Server Sentence:
------------------------------------------------------
DECLARE
@Year SMALLINT,
@City varchar(255)
SET @Year = 2010
SET @City = ''NY','NJ''
[Code]....
View 3 Replies
View Related
Feb 20, 2012
Can we have any exception in DECLARE block (shown are just for example)?
Declare
exception occured
begin
code
end;
declare
variables;
begin
declare
variables;
begin
code;
end;
end;
View 6 Replies
View Related
May 22, 2013
When I am running script in oracle 10g ,getting error message as "PLS-00201: identifier 'UTL_SMTP' must be declared".
how to declare identifier 'UTL_SMTP' in oracle 10g or how to give execute access to current user
View 6 Replies
View Related
Jan 28, 2013
while trying to execute this
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
)
View 14 Replies
View Related
