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 Go to next message
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 #297285 is a reply to message #297274] Thu, 31 January 2008 00:53 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Any help ?
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 Go to previous messageGo to next message
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 #297297 is a reply to message #297274] Thu, 31 January 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Generally speaking, the syntax is (c1,c2) IN ((v1,v2),(v3,v4)...)

2/ There is very few differences with a classical "varying in-list" that you can find here or on AskTom.

Please search for this and try to apply it.

Regards
Michel
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 Go to previous messageGo to next message
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 #297311 is a reply to message #297302] Thu, 31 January 2008 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Ok now try step 2.

Regards
Michel
Re: how to pass a set of values to a procedure [message #297373 is a reply to message #297274] Thu, 31 January 2008 05:00 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

Unable to find any useful information.can any one please provide some Help as I am running out of time.

my procedure is ready except with the said functionality,
so please show me right direction.


Thanks in advance
Re: how to pass a set of values to a procedure [message #297380 is a reply to message #297373] Thu, 31 January 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Where did you search?

In addition why don't you directly pass a table of records instead of a string that you have built?

Regards
Michel

[Updated on: Thu, 31 January 2008 05:19]

Report message to a moderator

Re: how to pass a set of values to a procedure [message #297384 is a reply to message #297274] Thu, 31 January 2008 05:28 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
From the front end I get a big string which is like empno,deptno

((70001,10),(70002,10),(70003,20))

Can I directly use them in my where Cluase ?
Or else what to do ?



Re: how to pass a set of values to a procedure [message #297385 is a reply to message #297384] Thu, 31 January 2008 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The question is why your front end gives you a big string that it has to build and you to "unbuild" instead of table of records that you could directly use?

Quote:
Can I directly use them in my where Cluase ?

No.

Quote:
Or else what to do ?

What is in the links you'll easily find if you put the keywords I gave in the search field of the sites I mentionned.

Regards
Michel
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 Go to previous messageGo to next message
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 #297399 is a reply to message #297397] Thu, 31 January 2008 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Which dead line time?

By the way, T. Kyte says how to use it.

Regards
Michel

[Updated on: Thu, 31 January 2008 06:04]

Report message to a moderator

Re: how to pass a set of values to a procedure [message #297402 is a reply to message #297397] Thu, 31 January 2008 06:12 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:41324290340632. Try to incorporate the existing logic based on this link.

Regards

Raj
Re: how to pass a set of values to a procedure [message #297411 is a reply to message #297274] Thu, 31 January 2008 06:39 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Michel,

can you please provide me that link .
Re: how to pass a set of values to a procedure [message #297414 is a reply to message #297411] Thu, 31 January 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The one you probably found: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Regards
Michel
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 Go to previous messageGo to next message
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 #297419 is a reply to message #297417] Thu, 31 January 2008 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
but I want to print the values from the cursor

So don't put the variable name in a string, just give the variable itself.

By the way, the syntax "THE select cast..." is 8.0, use "TABLE(in_list...)" now.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #297483 is a reply to message #297481] Thu, 31 January 2008 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is there any other way to handle such scenario ?

I already answered this.

Regards
Michel
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 Go to previous messageGo to next message
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
Re: how to pass a set of values to a procedure [message #297654 is a reply to message #297636] Sat, 02 February 2008 01:09 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
sukarnar,

I think you should Google for "sql injection".

Regards
Michel
Previous Topic: Time taken for INSERT into table
Next Topic: inserting
Goto Forum:
  


Current Time: Tue Feb 11 18:31:27 CST 2025