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 .
create or replace PROCEDURE PRO_ICMISd_customer_no BB_PM.customer_no%type;d_pr_code_bbl BB_PM.pr_code_bbl%type;d_pr_code_pmm BB_PM.pr_code_pmm%type;d_subdept_desc PM.subdept_desc%type;d_class_desc PM.class_desc%type;d_cat_desc PM.cat_desc%type;d_subcat_desc PM.subcat_desc%type;d_brand_name PM.brand_name%type;d_product_desc PM.product_desc%type;d_unit_price_bbl PM.unit_price%type; e_customer_no BB_PM.customer_no%type;e_pr_code_bbl BB_PM.pr_code_bbl%type;e_pr_code_pmm BB_PM.pr_code_pmm%type;e_subdept_desc PM.subdept_desc%type;e_class_desc PM.class_desc%type;e_cat_desc PM.cat_desc%type;e_subcat_desc PM.subcat_desc%type;e_brand_name PM.brand_name%type;e_product_desc
[code]....
View 17 Replies
ADVERTISEMENT
Nov 23, 2011
I want to return ref cursor based on explicit cursors
create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;
[Code].....
The above procedure has compilation errors when I am trying to open ref cursor
LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>
View 5 Replies
View Related
Nov 16, 2011
what are the main points that these examples are considered cursors? and why are they called explicit and implicit cursor.
explicit
for x in (select * from emp) loop
dbms_output.put_line('emp no: '||x.empno);
end loop;
implicit
select empno
into vEmpno
from empno
where empname = 'SCOTT';
for all we know that these are not clearly defined on the declaration area as cursor.
View 16 Replies
View Related
Nov 25, 2012
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);
[Code]......
View 6 Replies
View Related
Feb 21, 2013
What is the difference between implicit cursor and explicit cursor in PL/SqL?
And what is ref cursor ?
View 2 Replies
View Related
Nov 5, 2013
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
View 10 Replies
View Related
Nov 1, 2011
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;
[code]....
View 4 Replies
View Related
Aug 24, 2008
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?
View 1 Replies
View Related
Jul 24, 2009
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.
View 2 Replies
View Related
Mar 12, 2011
Recently decided to try using Oracle. Previously when creating a procedure in SQL server I would have done the following.
Create PROCEDURE GetInfo AS
SELECT InfoID, Name, Description
FROM GetInfo;
How can I write this same procedure in Oracle?
View 10 Replies
View Related
Mar 4, 2011
I have create a procedure as
create or replace procedure aa is
abc varchar2(1000);
begin
abc:='create or replace view abc_view as select * from abc';
execute immediate abc;
end;
When i try to execute the same as
BEGIN
aa;
END;
i got error as
ORA-01031: insufficient privileges
ORA-06512: at "EARS.AA", line 5
ORA-06512: at line 3
why i get this error. there is no problem in grants. the same create or replace view query works if i directly executes in sql.
View 8 Replies
View Related
Jun 28, 2012
I am getting many errors when creating the below procedure.
alter procedure sp_compileinvalid as
Begin
Set lines 999;
Spool run_invalid.sql
select
[code].......
View 14 Replies
View Related
Apr 19, 2012
My requirement is like as follows,
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.
View 21 Replies
View Related
Jan 12, 2012
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.
View 4 Replies
View Related
Sep 11, 2012
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.
View 3 Replies
View Related
Aug 9, 2010
I have created a Pl/SQl block as the following
Declare
v_dname dept.dname%type;
v_loc dept.loc%type;
v_empno emp.empno%type;
v_job emp.job%type;
p_result SYS_ refcursor;
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:"
View 11 Replies
View Related
Jan 7, 2013
I have a procedure where my end result will give like
INSERT INTO ABC(A,B,C,D,ID) SELECT 1,2,3,4,P_ID FROM BBC WHERE P_ID=300;
this is a bulk insert where having 30 records.if one record fail then nothing will be commited.error willbe moved into my error log table.
I want the insert statement to be record by record and commit the successful one and move the error into error log table.
View 2 Replies
View Related
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
View Related
Mar 5, 2012
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.
View 14 Replies
View Related
Jan 23, 2012
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;
[code].....
View 9 Replies
View Related
Feb 2, 2011
I have this stored procedure as part of package.
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.
View 10 Replies
View Related
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
May 20, 2013
I am running this procedure but it will not compile. I get the error "PLS-00356: 'REC.XX' must name a table to which the user has access"
All of the query results from the cursor are correct.
create or replace procedure SWDCADMIN.Hard_Delete_Client( cltId IN number)
IS
cursor c1 IS
select
t1.table_name xx,
t1.owner || '.' || t1.TABLE_NAME uu,
[code]...
View 15 Replies
View Related
Sep 12, 2011
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''
View 21 Replies
View Related
Jul 31, 2012
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.
View 4 Replies
View Related
Jun 6, 2012
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;
/
View 6 Replies
View Related
Oct 23, 2012
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.
View 4 Replies
View Related
Feb 18, 2011
Need some Clarification on the below query:
Can we specify explicit name for the index of the IOT.
View 2 Replies
View Related
Apr 8, 2012
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;
/
[code].....
View 21 Replies
View Related
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
