| Pass parameter list to IN clause of cursor [message #37605] |
Fri, 15 February 2002 06:50  |
Avery
Messages: 4
Registered: February 2001
|
Junior Member |
|
|
I'll try to explain this as best I can. I basically have a procedure that I would like to pass a data list to, I want the data list to be used in the IN clause of a cursor.
EX.
CREATE OR REPLACE PROCEDURE forum_example(param_list IN VARCHAR2)
CURSOR get_list is
SELECT *
FROM the_table
WHERE the_field IN (param_list);
BEGIN
OPEN get_list
**** LOGIC ****
CLOSE get_list;
END
It seems to me that when I pass a comma delimited list oracle sees that as a single value thus no results. Is this even possible to do? If not how would you suggest I accomplish something like this. Thank you very much for your help.
-Avery
|
|
|
|
| Re: Pass parameter list to IN clause of cursor [message #37611 is a reply to message #37605] |
Fri, 15 February 2002 10:19  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
1) CREATE FUNCTION WITH THIS CODE
create or replace function split_it(pstrval varchar2) return varchar2 IS
totstr VARCHAR2(100) := '';
mainstr VARCHAR2(40) := pstrval;
splitstr VARCHAR2(30) := '';
l_count NUMBER(20) := 1;
itr_count NUMBER(20) := 0;
processed BOOLEAN := FALSE;
ret_reg VARCHAR2(60):='';
begin
itr_count := 0;
processed := FALSE;
LOOP
itr_count := itr_count+1;
IF instr(mainstr,',',1,itr_count)>0 THEN
splitstr := SUBSTR(mainstr,l_count,(INSTR(mainstr,',',1,itr_count)-l_count));
l_count := INSTR(mainstr,',',1,itr_count)+1;
ELSE
splitstr := SUBSTR(mainstr,l_count,LENGTH(mainstr)+1-l_count);
processed := TRUE;
END IF;
splitstr := ''''||splitstr||'''';
IF itr_count>1 THEN
totstr:= totstr||','||upper(splitstr);
ELSE
totstr:= totstr||upper(splitstr);
END IF;
IF processed THEN
EXIT ;
END IF;
END LOOP;
return totstr;
end;
2) write stored procedure as shown below
CREATE or replace PROCEDURE PROC5(V varchar2) is
type r is ref cursor;
r1 r;
crec emp%rowtype;
begin
open r1 for 'select * from emp where ename in ('|| split_it(v) ||')';
loop
fetch r1 into crec;
if r1%notfound then
exit;
end if;
dbms_output.put_line(crec.ename);
end loop;
end;
3) execute procedure
SQL> exec proc5('SMITH,ROB');
SMITH
ROB
|
|
|
|
| Re: Pass parameter list to IN clause of cursor [message #37613 is a reply to message #37611] |
Fri, 15 February 2002 15:08 |
Avery
Messages: 4
Registered: February 2001
|
Junior Member |
|
|
Hi Suresh,
I modified my procedure to do what you said but when I get to the point where i open the cursor and define my select at the ' before select it gets hung up. If i take the single quotes out and compile it compiles fine. THe problem is that I run into the same problem i had before cause my query is not being create for me on the fly. Thank you for your help, any suggestions to remedy the situation?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
