How to pass variables in cursor

 function    X  is
   tempvar Integer (8);
   tempvar1 Integer (8);
   tempvar2 Integer (8);


  begin
 
   SELECT id into tempvar FROM DEPT DEP WHERE DEP.STATUS='OPEN'
   and DEP.DEP_NO='DEPT-1126';
   
   dbms_output.put_line('tempvar' || tempvar);
   
 //from this cursor one record will be fetched.do i need use cursor here?
 CURSOR C1 IS
       SELECT EMP.REF_ID_1,EMP.REF_ID_3 FROM EMPlOYEE EMP WHERE
            EMP.REF_ID_4=tempvar;

//How to pass REF_ID_1 and REF_ID_3 variables to the below two tables.Is it right way of passing variables to the below tables;should i use cursor here?
where should i define cursor.should i define cursor before begin statement?

 
 FOR R2 IN C1
            LOOP

SELECT ID INTO tempvar1 FROM DOCUMENT X WHERE X.ID=R2.REF_ID_1;  
SELECT ID INTO tempvar2 FROM TEMP_ORDER JO WHERE JO.ID=R2.REF_ID_3;  
         
end loop;
  end;


 
end    X;
LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
>> from this cursor one record will be fetched.do i need use cursor here?
No, u need not use a CURSOR for a SELECT returning just 1 record and hence, even the LOOP is not reqd.
FUNCTION x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
SELECT  id
  INTO  tempvar1
  FROM  document
  WHERE id = v_ref_id_1;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0
Jinesh KamdarCommented:
Noe that ur FUNCTION must return some value. If u don't wanna return anything, use a procedure instead.
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
SELECT  id
  INTO  tempvar1
  FROM  document
  WHERE id = v_ref_id_1;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaitu chaituAuthor Commented:
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;

 
CURSOR C1 IS
SELECT  id,temp_id
  FROM  document
  WHERE id = v_ref_id_1;


 FOR R1 IN c1
            LOOP
  DELETE
  FROM  temp_order
  WHERE id = R1.temp_id;
  END LOOP;
 

 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;


thanks for the explanation;i have small doubt here;i have changed little bit what you have written.

lets say  i got multiple items from the document table then i think we have to convert into cursor.then i want to loop each id and delete temp_order table;when i write like this i am getting below exception;can i initialize cusor inside begin statemnt?

Compilation errors for PACKAGE BODY    .x

Error: PLS-00103: Encountered the symbol "C1" when expecting one of the following:
       
          := . ( @ % ;


0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jinesh KamdarCommented:
>> can i initialize cusor inside begin statemnt?

No, you can't, CURSOR declarations always go in the DECLARE section. You're right that while dealing with SELECT statements that return multiple rows in PL/SQL, we have to use CURSOR.
However, for ur case, since the operations are pretty straight-forward (since ur SELECTing from the document table just to be able to DELETE the reqd. records from the temp_order table, correct me if u think thats not the case), u can do away with them. Try below.
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
SELECT  ref_id_1  , ref_id_3
  INTO  v_ref_id_1, v_ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
DELETE FROM temp_order
  WHERE id IN
    (SELECT  temp_id
       FROM  document
       WHERE id = v_ref_id_1);
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0
chaitu chaituAuthor Commented:
//in other scenario i must use cursor only ;for tht purpose
 I am passing variable to the cursor and cursor is returning multiple values but its not going into the loop;

PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
cursor c1 is(tempvar integer)
SELECT  ref_id_1  , ref_id_3
  FROM  employee
  WHERE ref_id_4 = tempvar;
 
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
   
 
 //in other scenario i must use cursor only ;for tht purpose
 I am passing variable to the cursor and cursor is returning multiple values but its not going into the loop;
 its not printing '..in the loop  ...........'
 
for r1 in c1(tempvar)
 loop
 
 dbms_output.put_line('..in the loop  ...........');
DELETE FROM temp_order
  WHERE id = R1.ref_id_1;
 
 end loop;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;
 
Open in New Window 

Open in new window

0
Jinesh KamdarCommented:
Have you SET SERVEROUTPUT ON ? I have also added a COMMIT at the end of the BEGIN block.
PROCEDURE x IS
 
tempvar    dept.id%TYPE;
tempvar1   document.id%TYPE;
tempvar2   temp_order.id%TYPE;
v_ref_id_1 emp.ref_id_1%TYPE;
v_ref_id_3 emp.ref_id_3%TYPE;
 
CURSOR c1 (tempvar INTEGER) IS
  SELECT  ref_id_1, ref_id_3
    FROM  employee
    WHERE ref_id_4 = tempvar;
 
BEGIN  
 
SELECT  id
  INTO  tempvar
  FROM  dept
  WHERE status = 'OPEN'
    AND dep_no = 'DEPT-1126';
 
DBMS_OUTPUT.PUT_LINE('tempvar : ' || tempvar);
 
FOR r1 IN c1(tempvar) LOOP
 
DBMS_OUTPUT.PUT_LINE('..in the loop  ...........');
 
DELETE FROM temp_order
  WHERE id = r1.ref_id_1;
 
END LOOP;
 
SELECT  id
  INTO  tempvar2
  FROM  temp_order
  WHERE id = v_ref_id_3;
 
-- Remaining code to process the SELECTed data.
 
COMMIT;
 
EXCEPTION
 
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 
END x;

Open in new window

0
chaitu chaituAuthor Commented:
i made a simple mistake;its working fine now.
0
Jinesh KamdarCommented:
Glad to be of help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.