FW: ODTUG-DEV2K-L Digest -- Volume 2001, Number 301

From: Raymond Lee Meng Hong <RAYMOND_at_infopro.com.my>
Date: Sun, 28 Oct 2001 17:31:37 -0800
Message-ID: <F001.003B6953.20011028174017@fatcity.com>

If you want to try dynamic sql..

here is some of my advice...you form
 ASSIGN THIS STATEMENT A VARCHAR2 STRING VARIABLE 'SELECT EMPNO FROM EMP WHERE TO_CHAR(DEPTNO) IN '||'('||STRCODE||')'; Then it will work..as a concatenation method instead of bind method.

try and see.

 From: Abdul Karim Khan <abdul.karim_at_almarai.com>  Date: Sat, 27 Oct 2001 16:21:36 +0300
 Subject: how to set cursor parameter

Hi
Guru I am passing value dynamically to cursor , but it gives error . Example:

DECLARE
  STRCODE VARCHAR2(1000) :='10,20'; -- this value assign dynamically by function which returns in string.
  A1 NUMBER;
 Cursor c1 is select empno from emp
 Where to_char(deptno) in (strcode);
  BEGIN
  Open c1;
 Loop
 Fetch c1 into a1 ;
 Insert into a (no) values(a1);
 If c1%notfound then
 exit;
 End if;
 End loop;
 Close c1;
 END; But it is working well when apply only one value Example STRCODE VARCHAR2(1000) :='10'; -- this value assign dynamic A1 NUMBER;
 Cursor c1 is select empno from emp
 Where to_char(deptno) in (strcode);
  BEGIN
  Open c1;
 Loop
 Fetch c1 into a1 ;
 Insert into a (no) values(a1);
 If c1%notfound then
 exit;
 End if;
 End loop;
 Close c1;
 END;
Any help is highly appreciated.

Thanks in advanced

karim
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Raymond Lee Meng Hong
  INET: RAYMOND_at_infopro.com.my

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sun Oct 28 2001 - 19:31:37 CST