Home » SQL & PL/SQL » SQL & PL/SQL » Cursor For Loop (Oracle 10g, Win XP)
Cursor For Loop [message #389605] Mon, 02 March 2009 22:41 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi All,

I was just working on one example on cursor for loop, I have one SQL Statment which will be generated dynamically, I want to use that sql statement in Cursor For Loop, but i have declared that SQL Statement as Varchar2,How do i use it ?

Please have a look at the following code,

CREATE OR REPLACE PROCEDURE testtest (i_depts IN VARCHAR2)
IS
   i       NUMBER;
   l_sql   VARCHAR2 (2000);
BEGIN
   l_sql := 'Select empno from emp where deptno in  (' || i_depts || ')';
   DBMS_OUTPUT.put_line (l_sql);

   FOR i IN l_sql
   LOOP
      EXIT WHEN SQL%NOTFOUND;
      DBMS_OUTPUT.put_line ('EmpNo' || i.empno);
   END LOOP;
END;


It will obviously through error because 'l_sql' is varchar2 and not cursor.

This is just an example to check how to use a Varchar2 string as the cursor.

Regards,
Ashoka BL
Bengaluru
Re: Cursor For Loop [message #389616 is a reply to message #389605] Mon, 02 March 2009 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Search for VARYING INLIST
Search for EXECUTE IMMEDIATE

Doing "dynamic SELECT" is ill advised & is non-scalable code.
Re: Cursor For Loop [message #389623 is a reply to message #389605] Mon, 02 March 2009 23:09 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Adding to my requirement, i dont want it to be implemented with the RefCursor

Please find the DDL And DML below,

[Edit MC: Oracle code removed, don't post copyrighted material. (Standard EMP table is used in this topic.)]

The input Parameter will be passed to the Procedure as following,
Quote:

SQL> execute testtest('20,30,40');



Regards,
Ashoka BL

[Updated on: Mon, 02 March 2009 23:14] by Moderator

Report message to a moderator

Re: Cursor For Loop [message #389624 is a reply to message #389605] Mon, 02 March 2009 23:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A direct answer to your question is use "open mycursor for l_sql" (after declaring mycursor, of course).
But this is not the correct (most efficient) way to achieve your goal. Search as BlackSwan said.

Regards
Michel

[Updated on: Mon, 02 March 2009 23:15]

Report message to a moderator

Re: Cursor For Loop [message #389625 is a reply to message #389605] Mon, 02 March 2009 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL is "compiled" as a static statement; when procedure is compiled.
SQL can not vary at run time.
Re: Cursor For Loop [message #389627 is a reply to message #389605] Mon, 02 March 2009 23:27 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Thanks for the replies,

Quote:

SQL is "compiled" as a static statement; when procedure is compiled.
SQL can not vary at run time



How will it be compiled or effected if i use REF CURSOR ?

But i believe we can achieve this by using REF CURSOR as,

Open RefCursor for l_sql;


Regards,
Ashoka BL
Bengaluru
Re: Cursor For Loop [message #389628 is a reply to message #389605] Mon, 02 March 2009 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>But i believe we can achieve this by using REF CURSOR as,
Then why are you here?
Either you can or you can not.

Nothing is impossible for the person who does not have to actually make it reality!
Re: Cursor For Loop [message #389635 is a reply to message #389605] Tue, 03 March 2009 00:24 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I always wanted to be polite and respect the people out here, but i dont know why does the other person be like that !!

I asked for the solution or wanted to share whatever i have,

Quote:

Then why are you here?
Either you can or you can not.



This cannot be an answer, i think its not really required here to talk about anything too much.

If you want to share things or motivate people in doing that it is good.

I want to close this topic here.

This may be useful for everybody when they post here !!
./fa/5840/0/

Regards,
Ashoka BL
Bengaluru


  • Attachment: Respect.PNG
    (Size: 8.27KB, Downloaded 264 times)
Re: Cursor For Loop [message #389878 is a reply to message #389635] Tue, 03 March 2009 21:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE TestTest
  2    (i_depts IN VARCHAR2)
  3  AS
  4  BEGIN
  5  	FOR i IN
  6  	  (SELECT empno
  7  	   FROM   emp
  8  	   WHERE  deptno IN
  9  		  (SELECT deptno
 10  		   FROM   TABLE (CAST (str2tbl (i_depts) AS MyTableType)))
 11  	   ORDER  BY empno)
 12  	LOOP
 13  	  DBMS_OUTPUT.put_line ('EmpNo ' || i.empno);
 14  	END LOOP;
 15  END TestTest;
 16  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> execute testtest ('20,30,40')
EmpNo 7369
EmpNo 7499
EmpNo 7521
EmpNo 7566
EmpNo 7654
EmpNo 7698
EmpNo 7782
EmpNo 7788
EmpNo 7839
EmpNo 7844
EmpNo 7876
EmpNo 7900
EmpNo 7902
EmpNo 7934

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 




You can find the str2tbl function used above and corresponding mytabletype in the following thread on "varying elements in in list" on Tom Kyte's site:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Re: Cursor For Loop [message #389881 is a reply to message #389605] Tue, 03 March 2009 22:22 Go to previous message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Barbara,

Thank you very much Smile

Regards,
Ashoka BL
Bengaluru
Previous Topic: SQL to sort a column containing numeric/characters/slphanumeric characters
Next Topic: desc table
Goto Forum:
  


Current Time: Fri Dec 02 14:18:33 CST 2016

Total time taken to generate the page: 0.12483 seconds