Hi,
I am trying to craete a procedure where the input to the procedure will be a string like 'a,b,c,d' and output will be the values from a table which match the comma separated value from in IN sting.
For that --
create table test (nm varchar2(10));
insert into test values ('a');
insert into test values ('b');
insert into test values ('c');
insert into test values ('d');
select * from test;
NM
------
a
b
c
d
Now i am trying to make a procedure where the IN parameter will be the values of NM column of TEST table in a comma separated string like 'a,b,c,d,x,w' there could be false values also.
So the procedure will return only the values which are matching with NM column of the TEST table for this i have created This procedure ---
create or replace procedure p_test (p_nm IN varchar2 /*, p_out OUT sys_refcursor*/)
is
l_len number;
l_val varchar2(10);
l_val1 varchar2(10);
begin
l_len := length(p_nm);
-- dbms_output.put_line(l_len);
begin
for i in 1..l_len
loop
select REGEXP_SUBSTR (p_nm, '([^,]*)(,|$)',1, i , NULL, 1) into l_val from dual;
-- dbms_output.put_line(l_val);
-- open p_out for
select * into l_val1 from test where nm = l_val;
dbms_output.put_line(l_val1);
exit when l_len is null;
end loop;
exception
when no_data_found then
null;
end;
exception
when others then
dbms_output.put_line('Error reason :'||sqlerrm||' error code :'||sqlcode);
end;
EXECUTE p_test('a,,b,c,d,q,w');
OUTPUT --
a
b
c
d
This procedure is giving me output as i required but i need to get that result into a variable which should be OUT parameter of this procedure as it will be called by the JAVA of our application.
As i have already tried to use the refcursor (please see the commented part) but it is giving me no output while a call it.
Its a call to this procedure when i use the refcursor (by removing comments ).
declare
l_out sys_refcursor;
l_val varchar2(20);
l_str varchar2(20) := 'a,b,c,d';
begin
p_test (l_str, l_out);
loop
fetch l_out into l_val;
dbms_output.put_line(l_val);
dbms_output.put_line('a');
exit when l_out%notfound;
end loop;
end;
So here i got stuck with that how to get the multiple output or am i missing something here and if there is a better approach to this requirement as i have come with this so i am sharing it here.
OR if some one can tell me what is wrong with this package --
create or replace package p_test_api
is
type t_rec is record(name varchar2(200));
type t_tab is table of t_rec index by pls_integer;
procedure p_pest_proc (p_nm In varchar2, p_out OUT t_tab);
end p_test_api;
/
create or replace package body p_test_api
is
procedure p_pest_proc (p_nm In varchar2, p_out OUT t_tab)
is
l_len number;
l_val varchar2(10);
l_val1 varchar2(10);
begin
l_len := length(p_nm);
begin
for i in 1..l_len
loop
select REGEXP_SUBSTR (p_nm, '([^,]*)(,|$)',1, i , NULL, 1) into l_val from dual;
dbms_output.put_line(l_val);
select * bulk collect into p_out from test where nm = l_val;
exit when l_len is null;
end loop;
exception
when no_data_found then
null;
end;
exception
when others then
dbms_output.put_line('Error reason :'||sqlerrm||' error code :'||sqlcode);
end p_pest_proc;
end p_test_api;
CALL TO THIS PACKAGE --
declare
l_out p_test_api.t_tab;
l_str varchar2(20) := 'a,b,c,d';
begin
P_TEST_API.P_PEST_PROC(l_str, l_out);
dbms_output.put_line('b');
for i in 1..l_out.count
loop
dbms_output.put_line('a');
dbms_output.put_line(l_out(i).name);
dbms_output.put_line('a');
exit when l_out.count = 0 ;
end loop;
dbms_output.put_line('a');
end;
It is not going into loop
I am using ---
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Thanks