PL/SQL :: Creating A Procedure Using Explicit Cursor?
Nov 19, 2013
I am writing this procedure with a explicit cursors defined in it. However when i compile the procedure i get this error: Error(39,1): PL/SQL: SQL Statement ignored Error(39,1):PLS-00394: wrong number of values in the INTO list of a FETCH statement .
I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.
I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.
FOR i IN 300..300 --18000 LOOP y:=ymax-ysize*(i+0.5);
difference between between these two constructs. Finally when i read the asktom.oracle.com , I was totally confused. The reason is thatTom says...we can retrieve more than one row in implicit cursor. If that would be case, what's the difference between these two cursors?? when to use?? My understanding was implicit cursors" ---> single-row queryExplicit cursors ---> multi-row query Experts
Oracle Version: 11.2.0.2.0. I have two explicit cursors and I would like to choose at run time which one to run. Here is a simplified code snippet of what I am doing today:
DECLARE CURSOR Cursor_A IS SELECT * FROM EMP_A; CURSOR Cursor_B IS SELECT * FROM EMP_B; RUNA CHAR(1) := 'Y';
[code]....
I want to avoid maintaining the same long list of transformations. I also want to avoid, if possible, an explicit FETCH INTO, because there are hundreds of fields in both tables. I'm looking for something like this (and I know this doesnt work):
DECLARE CURSOR Cursor_A IS SELECT * FROM EMP_A; CURSOR Cursor_B IS SELECT * FROM EMP_B; RUNA CHAR(1) := 'Y'; CursorToRun IS REF CURSOR;
I'm modifying a package which contains a function with different parameters (say cno, gno, pname etc) need to be passed though it and that function is called by the cursor later in the program. I need to create a cursor (select statement) for every class (a program set)...i.e. multiple select statements for a program set with different values. The parameters needed to be passed correspond to same columns in different tables...so do i have to refer those tables everytime i'm passing the parameters using a select statement? The Program accepts the parameters as an array (list of parameters) and returns a cursor and the program handles one program set (class) at a time. That parameter list will be in a loop..i.e. it will be repeated for every certain combination (say cno - pname combination). So ... How should i proceed and create a select statement with different parameters declared in the function of type string making it dynamic and returning them using ref cursor?
I am trying to create trigger through Procedure due to following reasons-
1. The name of the column on which trigger will execute, is to be fetched dynamically. 2. The name of the column to be updated, is to be fetched dynamically.
Here is the sample code-
CREATE OR REPLACE PROCEDURE test2 IS var VARCHAR2 (4000); uname VARCHAR2 (30); attribtask VARCHAR2 (100); mapcol VARCHAR2 (100); BEGIN [code].........
On execution, the procedure throws the error of 'Insufficient privileges'. The 'var' seems to be the main culprit because the issue disappears if var is set to 'select * from dual'. Also, if i take the output (value of var) given by DBMS_output.put_line function and execute it explicitly, trigger gets created.
In addition- The procedure is (and being executed) within the same user/schema under which trigger is going to be created.
declare v1str varchar2(100):='select empno,ename from emp'; v2str varchar2(100):='select empno,ename,sal from emp'; type t_array is varray(2) of varchar2(100);
[Code]....
So my problem is while executing the different sql statements by passing it to the procedure,how can the procedure would behave dynamically.It must be able to process all the sql statements.
I have the procedure with out parameter is ref cursor.
l_sql VARCHAR2(32767); BEGIN l_sql := 'select query with appending procedure IN aparameters'; OPEN rc_rpt FOR l_sql;
Here procedure IN parameter is a string with comma separated value which is appended in the dynamic query IN clause.So some time the size exceeded more then 32767 and getting error.If i am using normal parametrized cursor this issue is not there,but i want to return only ref cursor for some java purpose.My oracle version is 10g.
I have a procedure(used to delete the data from all tables) which taking two parameters as input. like EXCS_kiwldate(1,'keepitems')[/color][/size] but it taking only one parameter at a time if i want to delete all data using that procedure by calling it in a cursor
how to write a cursor by giving loop function to delete the data once.
Begin open p_result for select distinct d.dname, d.loc, e.empno, e.job from dept d, emp e where d.deptno=e.deptno; fetch p_result into v_dname,v_loc,v_empno,v_job; dbms_output.put_line(v_dname||' '||v_loc||' '||v_empno||' '||v_job); end;
This is throwing me error saying " ORA-06550: line 7, column 15: PLS-00103: Encountered the symbol "REFCURSOR" when expecting one of the following:"
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
I have to create a stored procedure having some 10 cursors and i have to display the data's fetched by select statement in cursors using dbms_output.put_line().
for ex: ------- CREATE OR REPLACE PROCEDURE PROC AS CURSOR C1 AS SELECT * FROM EMP; BEGIN FOR R IN C1 LOOP
[code].....
My question is while creating procedure i want to check if the object exist in database or not.
If EXIST NO PROBLEM MY CODE CREATE THE PROC AND EXECUTED FINE IF NOT EXIST
I don't want an exception to be thrown stating "OBJECT DOES NOT EXIST" I wanted PL/SQL engine not to execute the particular select statement itself and continue executing other select statements.
The reason why have such kind of wierd requirement is my program displays all the CEMLI objects in any 11.5.10 instance. I don't know whether they have installed discoverer or not. if they installed it no problem else my program have to eliminate.EXECUTE IMMEDIATE, REF CURSOR becoz i tried it and works fine.but not able to wrap in WRAPPER UTILITY 8.0 versions.
I am trying to get my stored procedures together again after many years of working on MS SQL server. I'm alternating between using SQL-Plus and Oracle SQL Developer for Mac. In SQL Developer - which I like to use - I keep getting errors at the point where the END command for the package header occurs and the create command for the package body starts.
I've found that if I run the following in SQL-Plus I'm OK but if I try it from SQL Developer I get a compile error such as following or else an error telling me that it expects function, or pragma or something to that effect:
Error(8,1):PLS-00103:Encountered the symbol "/"
The code is as follows: -------------------------------------- CREATE OR REPLACE PACKAGE MACR_SAMPLE_PROC_4 AS PROCEDURE sampleproc_4( pParam1 in integer, pParam2 out varchar2); END MACR_SAMPLE_PROC_4;
PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER, in_SPLY_REORD_NO IN CHAR, in_OHB_QTY_CARTONS IN NUMBER, in_OHB_QTY_UNITS IN NUMBER, in_SPLY_TOT_OHB_QTY IN NUMBER, in_OHB_INPUT_CTNS_MIN IN NUMBER, in_OHB_INPUT_CTNS_MAX IN NUMBER, in_UNITS_PER_CARTON IN NUMBER, in_OHB_INPUT_UNIT_CONSTANT IN NUMBER, in_TOTAL_CARTONS IN NUMBER, out_ALLOW_OHB_INPUT_FLAG OUT CHAR, out_ERR_CODE OUT NUMBER, out_ERR_MESSAGE OUT VARCHAR2)
When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor
Here is the execution script DECLARE IN_CALL_ID NUMBER; IN_SPLY_REORD_NO VARCHAR2(32767); IN_OHB_QTY_CARTONS NUMBER; IN_OHB_QTY_UNITS NUMBER; IN_SPLY_TOT_OHB_QTY NUMBER; IN_OHB_INPUT_CTNS_MIN NUMBER; [code]...
There is no cursor used in the procedure. It just inserts records in the table for given input values.
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.
I have a DML Statement inside a procedure and i use a cursor variable to get the values checked as below . I have attached my procedure not completely but the declaration part and the DML statement part.
The issue is my procedure is not inserting the records at all. It selects the values and then inserts accoringly but its not selecting because of the cursor reference R_LOC.LOCATION_GID.
when i hard code the value in the DML statemnt for the R_LOC.LOCATION_GID, the rows are inserted as expected. So i guess the way the procedure executes the value is not correct.
Modifying my select part which uses cursor variable R_LOC.LOCATION_GID under Insert statement.
select d.servprov_gid, d.depot_gid, replace(d.appointment_time,'':'') appmt_time, d.'||v_day_to_use||' DayUsed from tesco_fresh_templates t, tesco_fresh_templates_d d, location_refnum r where t.set_id= d.set_id and d.depot_gid=r.location_gid AND D.SERVPROV_GID=''R_LOC.LOCATION_GID'' and r.location_refnum_qual_gid=''TESCO.IVS SCHEDULING'' and (r.location_refnum_value=''YES'' or r.location_refnum_value=''Y'') and t.default_set=''Y''
I'm trying to fetch data from Ref Cursor OUT parameter filling by stored procedure. Using latest version of ODP.NET provider (11.2.0.3.0).
My stored procedure:
TYPE cursor_type IS REF CURSOR;
PROCEDURE test_proc (p_recordset out cursor_type) AS BEGIN OPEN p_recordset FOR SELECT 1,2, CURSOR (SELECT 3,4 FROM dual) FROM dual END; END test_proc;
If i'm removing "*CURSOR (SELECT 3,4 FROM dual)*" from the procedure, all works just fine. But when the nested cursor exists in procedure, i'm getting the following exception:
System.NullReferenceException occurred Message=Object reference not set to an instance of an object. Source=Oracle.DataAccess StackTrace: at Oracle.DataAccess.Client.OracleDataReader.GetOraDbTypeEx(Int32 i) [code]........
My c# code:
using (OracleConnection conn = new OracleConnection(connString)) *{* OracleCommand command = new OracleCommand(); command.Connection = conn; command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = "test_proc"; [code].......
If i'm changing procedure signature from out parameter, to return value, code throws the same exception.
I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.
create or replace procedure PRC_add_data( P_Emp_No varchar2, P_Member_Name varchar2, P_IDvarchar2, P_UHID varchar2 ) is BEGIN INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID) END; /
I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.
I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.
Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.
create or replace procedure addUsers(userNam in varchar2) is begin EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'|| 'PASSWORD EXPIRE'; end addUsers; /
