Home » SQL & PL/SQL » SQL & PL/SQL » how to pass a set of values to a procedure
how to pass a set of values to a procedure [message #297274] |
Wed, 30 January 2008 23:33  |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Hi All,
How to pass a set of values to a procedure as input parameter.
My Scenario:
I have to write a procedure which accepts a set of values as in put and i will use those set of values in my select where clause to filter the data.
Input parametr to the procedure is ((7001,10),(7002,10),(7003,20),(7004,30))
Below id my Pseduo code.
create or replace procedre hldh_search_info(mmy_input_set in varchar2(1000),
info_cur PLCY_INFO_OUT OUT SYS_REFCURSOR) is
begin
OPEN PLCY_INFO_OUT FOR
select * from emp where (empno,deptno)=Mmy_input ;
end;
For the above procedure mmy_input_set parameter values will be in the form of ((7001,10),(7002,10),(7003,20),(7004,30)).
Please help
[Updated on: Thu, 31 January 2008 01:13] by Moderator Report message to a moderator
|
|
|
|
Re: how to pass a set of values to a procedure [message #297294 is a reply to message #297274] |
Thu, 31 January 2008 01:06   |
mshrkshl
Messages: 247 Registered: September 2006 Location: New Delhi
|
Senior Member |
|
|
Quote: | where (empno,deptno)=Mmy_input
|
declare
type Mmy_input is record( first_field field_type,second_field field_type);
add all the values to above record.
now fetch the value from Mmy_input to empno and deptno separately.
|
|
|
|
Re: how to pass a set of values to a procedure [message #297302 is a reply to message #297274] |
Thu, 31 January 2008 01:20   |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Hi ALL ,
Thanks for the quick help.
A small change in my Pseduo code
earlier if you observe
emp where (empno,deptno) = Mmy_input
Actually it should be emp
where (empno,deptno) IN Mmy_input
create or replace procedre hldh_search_info(mmy_input_set in varchar2(1000),
info_cur PLCY_INFO_OUT OUT SYS_REFCURSOR) is
begin
OPEN PLCY_INFO_OUT FOR
select * from emp where (empno,deptno) IN Mmy_input ;
end;
For the above procedure mmy_input_set parameter values will be in the form of ((7001,10),(7002,10),(7003,20),(7004,30)).
Please help
|
|
|
|
|
|
|
|
Re: how to pass a set of values to a procedure [message #297397 is a reply to message #297274] |
Thu, 31 January 2008 06:01   |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Got some thing like this in Ask tom
create or replace type myTableType as table
of varchar2 (255);
Type created.
create or replace
function in_list( p_string in varchar2 ) return myTableType
as
l_string long default p_string || ',';
l_data myTableType := myTableType();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;
return l_data;
end;
But how can i encorporate this in my Procedure still clue less
and approaching the dead line time
[Updated on: Thu, 31 January 2008 06:02] Report message to a moderator
|
|
|
|
|
|
|
Re: how to pass a set of values to a procedure [message #297417 is a reply to message #297274] |
Thu, 31 January 2008 07:03   |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Hi All,
Don't know where I am heading, But,how should i Prent the vallues from the function in_list(from Ask tom )through a cursor
I had remodified that a litlle bit as
create or replace
function in_list( p_string in varchar2 ) return myTableType
as
l_string long default p_string || ',';
l_data myTableType := myTableType();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;
return l_data;
end;
And i want test the above funcction by opening a cursor
declare
cursor c1 is select *
from THE
( select cast( in_list('abc-01, xyz-01, 012-01') as
mytableType ) from dual ) a ;
c2 varchar2(100);
begin
open c1;
loop
fetch c1 into c2 ;
exit when c1%notfound;
dbms_output.put_line(' c2.column_name');
end loop;
close c1;
end;
O/P: c2.column_name
c2.column_name
c2.column_name
PL/SQL procedure successfully completed.
but I want to print the values from the cursor
Please help
|
|
|
|
Re: how to pass a set of values to a procedure [message #297424 is a reply to message #297274] |
Thu, 31 January 2008 07:31   |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
let put the whole scenario in this way
create or replace procedure p1 (v_emp_set in varchar2,
mmy_val_cur out SYS_REFCURSOR )is
cursor c1 is select *
from THE
( select cast( in_list(v_emp_set) as
mytableType ) from dual ) a ;
cursor c2 is select * from emp where empno||'_'||deptno= ?
In ? place i want to assign the value from c1 ;
how can i achiev this?
|
|
|
Re: how to pass a set of values to a procedure [message #297426 is a reply to message #297424] |
Thu, 31 January 2008 08:03   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> var list varchar2(200);
SQL> exec :list := '((7369,20),(7782,10),(7902,20),(7844,30))'
PL/SQL procedure successfully completed.
SQL> with
2 data as (
3 select replace('/'||substr(:list,3,length(:list)-4)||'/','),(','/') list
4 from dual
5 ),
6 list1 as (
7 select substr(list,
8 instr(list, '/', 1, level)+1,
9 instr(list, '/', 1, level+1)
10 - instr(list, '/', 1, level) - 1) value
11 from data
12 connect by level
13 < length(list)-length(replace(list,'/',''))
14 )
15 select to_number(substr(value,1,instr(value,',')-1)) val1,
16 to_number(substr(value,instr(value,',')+1)) val2
17 from list1
18 /
VAL1 VAL2
---------- ----------
7369 20
7782 10
7902 20
7844 30
4 rows selected.
SQL> with
2 data as (
3 select replace('/'||substr(:list,3,length(:list)-4)||'/','),(','/') list
4 from dual
5 ),
6 list1 as (
7 select substr(list,
8 instr(list, '/', 1, level)+1,
9 instr(list, '/', 1, level+1)
10 - instr(list, '/', 1, level) - 1) value
11 from data
12 connect by level
13 < length(list)-length(replace(list,'/',''))
14 ),
15 list2 as (
16 select to_number(substr(value,1,instr(value,',')-1)) val1,
17 to_number(substr(value,instr(value,',')+1)) val2
18 from list1
19 )
20 select *
21 from emp
22 where (empno,deptno) in (select * from list2)
23 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 800 20
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 3000 20
4 rows selected.
Regards
Michel
|
|
|
Re: how to pass a set of values to a procedure [message #297481 is a reply to message #297274] |
Thu, 31 January 2008 23:27   |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Hi All,
Huh, I am able to meet the requirements with way given in the ASk tom site.
In the first step I opend the cursor which calls the said funtion in the ask tom site(accepting a string then convert thatin to the individual values) and i am opening the 2nd curosr for the values c1 and i am repeating this process till all records in the 1st cursor end.
But, this is similar to the calling a procedure every time ?
I mean Even the front end Person Can call My Procedure for each individual values, instead of passing whole as a String of values?
Please suggest ,which is the Better way ?
I mean accepting whole as a string then implement the logic at database level (as i said above)
or call the procedure independently for each values
?
OR else
Is there any other way to handle such scenario ?
Please help
[Updated on: Thu, 31 January 2008 23:39] Report message to a moderator
|
|
|
|
Re: how to pass a set of values to a procedure [message #297636 is a reply to message #297302] |
Fri, 01 February 2008 20:05   |
sukarnar
Messages: 1 Registered: February 2008
|
Junior Member |
|
|
CREATE OR REPLACE procedure hldh_search_info(p_input in varchar2, PLCY_INFO_OUT OUT SYS_REFCURSOR) is
begin
OPEN PLCY_INFO_OUT FOR
'select * from employees where (employee_id,department_id) IN '||p_input ;
end;
/
To Use the above procedure here is the script below
SET SERVEROUT ON;
DECLARE
p_dept_refcur SYS_REFCURSOR;
v_emp employees%ROWTYPE;
BEGIN
hldh_search_info('((111,100),(112,100))',p_dept_refcur);
LOOP
FETCH p_dept_refcur INTO v_emp;
EXIT WHEN p_dept_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.first_name||' '||v_emp.last_name);
END LOOP;
END;
thanks
sukarna
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 18:31:27 CST 2025
|