SQL & PL/SQL :: Global Exception In Package Available Outside Package?
Jan 15, 2012
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?
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
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 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 trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR -------- ---------------------------------------------------------------- . ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.
Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.
I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?
We are working on a POC to build Web services from Oracle PL/SQL packages. For this we need to create a PL-SQL package having a stored procedure which will accept one input parameter and sends out data sets with multiple rows.
The type of the output parameter should be compatible with JDBC so as to publish the package as a web service. We tried using an Array and Ref cursor in PL/SQL but it didn't work with JDBC.
what type of PL/SQL output we should use here?
Our Environment : Oracle 10.2.0.3.0, Jdeveloper (to convert PL/SQL package into to Web Service) and Web Logic 11G server to deploy the services.
I ran the dbms_rectifier_diff.differences package successfully just to know the differences after it i droppped the all tables from the schema but now I have some funny table names exist in my schema like BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0. I want to remove it but dont know how, when I run the following command on sql prompt i get error message says 'sql command not properly ended'.
I have written a package including 13 procedures to insert data into a temp table but i am getting duplicate rows in dat table how to track from which procedure i am getting duplicate rows.
step 1 : procedures are inserting data using some joins into a temp table. step 2: from temp table data will get stored into a target table where if the data already exist it will update the target table else will insert the data.(its a procedure where i used merge).
as i have duplicate data on temp table i am not able to update data on target table.
I have created a PL/SQL package and complied it and I got a meaasge with no errors.But I dont know how do I run the package to check whether the data has been populated in tables or not.I want to know do I need to write any code for execution to see the output.If I need to write how can I do it in TOAD.
How to use DBMS_SQL package. I tried with the following procedure.
CREATE OR replace PROCEDURE Crt_tab_inst(tab_name VARCHAR2, col1_name VARCHAR2, col1_value VARCHAR2) IS cur BINARY_INTEGER := dbms_sql.open_cursor; fdbk BINARY_INTEGER; [code]........
But when Iam executing the procedure, it is throwing the below error. This is the error Iam getting:
SQL> EXEC crt_tab_inst('MYTAB','MYCOL','NAME1'); BEGIN crt_tab_inst('MYTAB','MYCOL','NAME1'); END; * ERROR at line 1: ORA-00984: column not allowed here ORA-06512: at "SYS.DBMS_SYS_SQL", line 909 ORA-06512: at "SYS.DBMS_SQL", line 39 ORA-06512: at "SCOTT.CRT_TAB_INST", line 21 ORA-06512: at line 1
I am unable to compile my package because of a lock being held on it . Let me provide the details for better clarity .
select * from dba_ddl_locks where session_id=111 and owner='RAHUL';
session_id owner name type mode_held mode_requested 111 RAHUL RAHUL_PKG BODY NULL None 111 RAHUL RAHUL_PKG Table/Procedure/Type NULL NoneThe session details of the session id 111 is as below
select sid,serial#,user#,username,command,status,process,sql_exec_start from v$session where sid=122
sid serial# user# username command status process SQL_EXEC_START 111 3558 222 RAHUL 47 KILLED 4420 13-DEC-12 02.00.00 AM
The strange this is that we have tried killing this session and the status above even shows ' 'KILLED', stilll the dba_ddl_locks is showing the lock details on the package RAHUL_PKG and we are unable to compile the package (the locking session is running since 13-Dec-2012 ) and we are unable to kill that . Also , is it like if i have executed a package that is referencing some other package then we wont be able to compile the referenced package until and unless the dependent package has completed its execution ??
I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure
in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:
Hops_Haku.veto(opnum);
But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?
Until now I've only gotten error, even after declaring the variable in the declaration part.
I have an Oracle Package with a procedure in which
package with a procedure in which there is a truncate partition, ALTER TABLE table_name TRUNCATE partition_name DROP STORAGE and the all is run with an EXECUTE IMMEDIATE 'alter table ...' .
The point is that the procedure in the package is started from another DB via DB Link (schema USER1) and doesn't work because of lack of privileges.
Instead, if the same procedure is started as a procedure, standalone, not in the package but from the same user (USER1) it works perfectly.
Don't understand why and which privileges must give to the user to run the procedure from inside the package.
All this has within is an SQL statement which is built up (using the string, 'strSql') How can I view the output of a refCur to check what the final strSql is?
I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.
To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...
SELECT 'PROCEDURE test_' || LOWER(procedure_name) || ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END ' || LOWER(procedure_name) || ';' FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;
However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).
So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?
Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?
I have cretaed an external table to read data to a table from a csv file placed in "c:oracleoradataspmap1utl". The csv file name is say "pildata.csv"
I have cretaed a package to read data from the exteranl table and to insert it into a table.
INSERT INTO M_PILEINT SELECT A.AREA AS "AREA", A.SUB_FAC_DESC AS "SUB_FAC_DESC", A.SCOPE_DETAIL AS "SCOPE_DETAIL", A.MTO_ISSUE_DATE AS "MTO_ISSUE_DATE", A.MTO_TAKE_BY AS "MTO_TAKE_BY", A.COMMODITY_CODE AS "SECTION" A.PILE_NAME AS "PILE_NAME" FROM M_EXE_PILE A
(where M_EXE_PILE A is the external table which is reading from pildata.csv)
The package runs good and data is populated to M_PILEINT .Is there a way , I can rename the csv file (say to pildata_logxxxx.csv.. something like that) from within the package.Whenever the package is run , it will copy the data from exteranl table and renames the csv file to something else..?
I´m having a trouble creating a db package. I´ve changed my job and now i´m working full on DB instead of Forms Developer.
So my trouble is that i want to create some like a global cursor on DB package, i know you can create global variables and use it on all the procedures/function inside the package. But how about cursors?
My problem: I dont´know how to declare into the spec. I don´t know how to use it on body.
My actual
I have the structure below:
PackageSpec: CREATE OR REPLACE PACKAGE [i]MyPackageName[/i] IS CURSOR [i]myGlobalCursor[/i](par1 IN VARCHAR2, par2 IN DATE)( ); PROCEDURE [i]MyProcedure1[/i](par1 IN VARCHAR2, par2 VARCHAR2);
