Toad World® Forums

SQL question


#1

SQL question


#2

Thomas,

1 option: Before you call procedure, populate your input_variable by looping through parameters. You variable will look like ‘AAA’,BBB’,‘CCC’;
There’s PL/SQL block for example
DECLARE
llist VARCHAR2 (1000);
CURSOR c_tr IS
SELECT org1_code
FROM MARS_ORG1
WHERE org1_code BETWEEN 10 and 50
ORDER BY 1;
c_tr_rec c_tr%ROWTYPE;
BEGIN
llist := ‘,’; – DBMS_OUTPUT.put_line(llist);

OPEN c_tr;
LOOP
FETCH c_tr
INTO c_tr_rec;

EXIT WHEN c_tr%NOTFOUND;

IF llist = ‘,’
THEN
llist := llist || c_tr_rec.org1_code;
ELSE
llist := llist || ‘,’ || c_tr_rec.org1_code;
END IF;
END LOOP;

CLOSE c_tr;
llist := substr(llist, 2);


#3

Tom Kyte has an elegant solution for this:

http://tkyte.blogspot.com/2006/06/varying-in-lists.html