SQL & PL/SQL :: How To Execute Ref Cursor Procedure

Sep 22, 2012

I have one package, that included so maany ref. cursor package..Now , i want to execute of one procedure in this package, how can i do it ..

CREATE OR REPLACE package Pkg_HR As
Type Typ_Cur Is Ref cursor;
procedure getHR_initiate(pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR);
procedure getFin_Yr(Cur_Fin_Yr Out TYP_CUR);
procedure getCutOFfStatus(pvAppsee1_Appsr2_Review3 Varchar2, pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR);
procedure SetEmp_For_FinYr(pvFinYr Varchar2, Cur_Emp OUT TYP_CUR);
End Pkg_HR ;

My Package Body is :

CREATE OR REPLACE package body Pkg_HR As
procedure getHR_initiate(pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR)
IS
Begin
Open Cur_HR_Init For
Select HR_FINYR HR_FinYr, To_Char(HR_PERIOD_FROM,'DD/MM/RRRR') HR_PERIOD_FROM

[code].....

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Procedure Cursor For Loop Don't Execute

May 13, 2010

I'm running a PL/SQL with a For Loop cursor, but when trying to execute it doesn't run. It is as if there is no data, but I ran the cursor separately in a SQL Plus session and it runs perfectly. I'm enclosing the file with the procedure.

View 18 Replies View Related

Execute / Display Results Of Procedure With Ref Cursor OUT Parameter

Apr 12, 2012

How do i execute and display results of a procedure declared with ref cursor OUT parameter.

I am using SQL Developer and Oracle 10.2.

View 4 Replies View Related

Client Tools :: How To Execute Stored Procedure With Cursor Within PL/SQL

Apr 5, 2011

To keep things simple: lets say that this is my procedure:

create or replace procedure testSp(x out sys_refcursor )
is
begin
open x for
select 1 from dual;
end;

how would i be able to execute this from with in sqlTools to see the result ??? i have tried everything...

my goal is in the end to execute this from Magic 8 (to those who herd of it). p.s this needs to work in oracle 8

View 4 Replies View Related

Execute Dynamic SQL Using Both Execute Immediate And Ref Cursor

Jan 8, 2009

We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?

View 5 Replies View Related

SQL & PL/SQL :: Create A Procedure Using EXECUTE IMMEDIATE?

Apr 14, 2013

I am trying to create a procedure using the EXECUTE IMMEDIATE. I have been having problems calling it from an anonymous block

My code

CREATE OR REPLACE PROCEDURE homework
(p_table_name VARCHAR2)
IS
v_department_id departments.department_id%TYPE;
BEGIN
EXECUTE IMMEDIATE

[code]....

I called the procedure from an anonymous block

BEGIN
homework('Employees');
END;

It gives me an error

ORA-00905: missing keyword

View 3 Replies View Related

SQL & PL/SQL :: Execute Procedure In Cycle

Oct 27, 2011

My need is to execute procedure in cycle. I can run

EXEC zoo.pkg_z184.rep184_fill( date '2011-09-28' );
EXEC zoo.pkg_z184.rep184_fill( date '2011-09-29' );

But I'd like to do it in cycle when the period is much bigger. Here is my try to do it:

begin

for l_row in (
select date '2011-09-27' as arcdate from dual union all
select date '2011-09-28' as arcdate from dual union all
select date '2011-09-29' as arcdate from dual union all
select date '2011-09-30' as arcdate from dual
)
loop
EXEC zoo.pkg_z184.rep184_fill( l_row.arcdate );
end loop;
end;

It returns error:

ORA-06550: line 9, column 6:
PLS-00103: Encountered the symbol "ZOO" when expecting one of the following:
:= . ( @ % ;

The symbol ":=" was substituted for "ZOO" to continue.

View 4 Replies View Related

How To Execute A Stored Procedure In Oracle

May 22, 2012

how to you execute a stored procedure in ORACLE..For example in SQL SERVER its just

EXEC Proc_Name ParameterValues

How the hell do you do this in oracle i just want to test if my stored procedure works.

View 1 Replies View Related

Can Execute Procedure On Some Other Day Without Changing Actual

Aug 21, 2012

I have a procedure which will execute on every Monday. Same is not executed last Monday. Can I execute the Procedure on some other day with out changing the actual procedure?

View 1 Replies View Related

Forms :: Execute Procedure Using Personalization

Aug 24, 2010

I have written a package in that package i have written a procedure. That procedure has two i/p and two o/p variables. I am calling the procedure using the below syntax.

in built in type : Execute a Procedure

='Declare
v_customer_name varchar2(100);
v_id number;
v_out1 number;
v_out2 NUMBER;
begin
v_id := 1041;
XXFBI_OM_UTILITIES.XXFBI_CUSTOMER_CREDIT_DETAILS1(''' || ${item.order.SOLD_TO.value} || ''', v_id, v_out1, v_out2);
end'

Its working fine.

My question is how can i capture the value of out put variables v_out1 and v_out2 and assign to a DFF.

View 2 Replies View Related

SQL & PL/SQL :: How To Execute Type Variable Procedure

Apr 26, 2013

I have created the following procedure. Since I am using this first time I don't know how to execute this.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
IS
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
END;
/

[Code]...

View 2 Replies View Related

SQL & PL/SQL :: Grant Execute On More Than One Procedure To A Role

Aug 9, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Is there a way to grant EXECUTE on a group of procedures/functions/packages to a particular role , in a single statement ? Or we have to do it one by one... like
GRANT EXECUTE on event_main to role1,
GRANT EXECUTE on event_main2 to role1,
GRANT EXECUTE on event_main3 to role1,

View 2 Replies View Related

SQL & PL/SQL :: Execute A Procedure Present In Different Schema?

Apr 27, 2010

I have two schema SCHEMA_A and SCHEMA_B in the same Database.

I want to execute a procedure UPDATE_CONTACT() which is present in SCHEMA_A from SCHEMA_B.

let me know whether the following SQL is correct:

BEGIN
SCHEM_A.UPDATE_CONTACT();
END;

View 4 Replies View Related

SQL & PL/SQL :: How To Call Procedure Dynamically Using Execute Immediate

Jan 2, 2011

I have a procedure p1 in schema s1.i want to call this procedure in schema s2 dynamically.how can i do?

View 4 Replies View Related

SQL & PL/SQL :: DML In Execute Immediate Inside A Stored Procedure

Apr 28, 2011

Requirement is to build procedure where it has 10-12 input variables but some of them (input variables) may at times be NULL.Based on this, i thought of getting into EXECUTE IMMEDIATE but this would just return rows i mean DML stmt for EXECUTE IMMEDIATE.Also, on the requirment is all parameters are present then result set be based on range on start and end date.

View 3 Replies View Related

Block Of Procedure Taking Time To Execute

Feb 9, 2011

I have connected to the database from two sessions

Box 1 : i have executed a procedure.it took few seconds to get executed

Box 2 : I have executed a block of procedure.It is taking 1 min 40 secs to get executed.

why it is taking much time from Box2(for executing a block from procedure) , as the same code executed successfully in a procedure from Box 1.

View 2 Replies View Related

ORA-00904 / Invalid Identifier - Not Able To Run EXECUTE IMMEDIATE In PL/SQL Procedure

Feb 4, 2012

Here is the code I am using:

CREATE OR REPLACE
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN

[code]....

The code complies successfully but when I am executing the code, here is the error I get. The code works successfully when I manually insert the values (as done in the commented out statement) but when use it from variable, it gives me error.

Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 18
ORA-06512: at line 6
START DATE IS 90301
END DATE IS 111201
Process exited.
Disconnecting from the database LocalEnvironment.

View 3 Replies View Related

Forms :: How To Execute A Stored Procedure Without Parameter

Jun 17, 2010

I have created a procedure like this

CREATE OR REPLACE PROCEDURE SCOTT.GetEmpList(p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT * FROM scott.emp
WHERE deptno=10;
END GetEmpList;

Now i would like to execute this procedure in sqlplus which will display the following:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 CLARK MANAGER 7839 6/9/1981 2450 10
7839 KING PRESIDENT 11/17/1981 5000 10
7934 MILLER CLERK 7782 1/23/1982 1300 10

View 5 Replies View Related

Client Tools :: How To Execute A Procedure Within Editor

Jan 15, 2010

something like

exec package name.procedure ?

View 3 Replies View Related

SQL & PL/SQL :: Execute File In Oracle Stored Procedure

May 16, 2012

I need to execute a .sql file in an oracle stored procedure.

View 9 Replies View Related

Execute AUDIT SELECT ON EMPLOYES From Procedure?

Dec 16, 2010

how can i execute "AUDIT SELECT ON EMPLOYES" from a procedure?

Ex:

create or replace procedure myfunc()
begin
AUDIT SELECT ON EMPLOYES;
end;

The above procedure will cause an error because AUDIT is no a DML statement.But... I still want to do that bebause i wanna call that function from PHP Application.With PHP, i can't execute AUDIT SELECT ON EMPLOYES.

View 3 Replies View Related

SQL & PL/SQL :: Using EXECUTE IMMEDIATE For DDL Command In Procedure For Password Change?

Nov 6, 2011

I have a procedure in this procedure i use.

EXECUTE IMMEDIATE 'alter user '||use||' identified by '||modp ||' replace '||oldp;

but when i execute it show insufficient privilages but i create for this procedure as public.and grant execute facility to the user.

View 6 Replies View Related

How To Audit Execute On Specified Procedure Or Function In Package

Oct 4, 2012

My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:

SQL Error: ORA-00942: table or view does not exist 00942. 00000 -  "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.

why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???

View 1 Replies View Related

PL/SQL :: Execute Procedure By Passing Number Array

Oct 26, 2012

Oracle 11.2.0.1
Windows

create type np_type is varray(3) of number
/

create type cn_type is varray(3) of number;
/

create type cxn_type is varray(3) of varchar2(2000)
/

I created TEST table by below pl/sql block :

declare
execstr varchar2(2000) :='create table test(';
begin
for i in 1..80 loop

[Code]....

*ERROR at line 5: ORA-06550: line 5, column 8: PLS-00103: Encountered the symbol "MYPROC1" when expecting one of the following: := . ( @ % ;

The symbol ":=" was substituted for "MYPROC1" to continue.

SQL>The above procedure will insert the supplied numbered string into the test table which split the numbered string based upon supplied splitids of sp_table.

View 7 Replies View Related

Execute Create Procedure / Function From SQL Script In SQLPlus

Feb 17, 2010

I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.

Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...

I am using the following command:

sqlplus u/p@<someserver> @<path_to_sqlfile>.sql

this sql file contains create procedure pl/sql code

Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?

View 8 Replies View Related

Execute Java Program From Oracle Procedure Which Is On Different Machines

Oct 10, 2012

Machine 1: Oracle server installed and database hosted on this Machine.
Machine 2: Oracle client is installed and connect through sqlplus to database in machine 1.

The requirement is: I need to have a Java/C program in machine 2 (where client available) which needs to be called from the stored procedure in server on machine 1. The objective is calling a java/C program from Oracle stored procedure but available on 2 different machines. Is it possible,

Environment: Oracle 11.2.0.1
UNIX AIX 6.1

View 3 Replies View Related

View A Stored Procedure Without Granting Execute Permissions?

Jul 13, 2012

Is there a way to grant rights to view a stored procedure without granting execute permissions?

View 2 Replies View Related

SQL & PL/SQL :: How To Execute Dynamic Query In Procedure With Special Characters

Dec 17, 2012

I have the following procedure body in a package.

PROCEDURE getrecordsForinspection(i_table_name in varchar2, i_thread_id in varchar2, i_max_count in number default null, o_results out sys_refcursor)
AS
v_sql varchar2(1000):= null;

begin
v_sql := 'update '||'i_table_name||' set status = '||'''IN_PROCESS-'||i_thread_id||''''||' Where final_status = '||''''STATUS_ACCEPTED'''||' and ('||i_max_count||' is null or rownum <= '||i_max_count||');';

EXECUTE IMMEDIATE(v_sql);
commit;
end;

when I execute the above procedure it gives the following error.

ORA-00911: invalid character
cause: Identifiers may not start with any ASCII characters other than letters and numbers.$#_ are also allowed after the first character. Identifiers enclosed by double quotes may contain any character other than a double quote. Alternative quotes(q'#....#') can not use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL language reference Manual.

I think dynamic sql is not executed because of the pipe character in the sql statement.

View 2 Replies View Related

SQL & PL/SQL :: Error With Execute Immediate To Pass Db_link To Stored Procedure?

Oct 19, 2011

I have the following error when I try to pass a Procedure parameter carrying db_link name to a stored procedure:

SQL> CREATE OR REPLACE PROCEDURE AFESD.P_DM_VCONTRACT_ITEM (CON_CONNECTION VARCHAR2)
2 IS
3 T_CONNECTION VARCHAR2(50);
4 T_SQL VARCHAR2(500);
5 BEGIN
6 T_CONNECTION := 'X_DM_TEST@' || CON_CONNECTION;

[code]....

The insert statement succeeds when I try to run DBMS_OUTPUT.put_line output.

View 4 Replies View Related

SQL & PL/SQL :: Execute Stored Procedure With Table Of Number As In Parameter?

May 31, 2011

I have one stored proc with three in parameters as

number, varchar and table of number

what statement I need to write in pl/sql to execute it ...

execute getdetails(1,'xyz', ????????????)

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved