want to pass multiple values into a procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: want to pass multiple values into a procedure

  1. #1
    Join Date
    Jun 2001
    Posts
    3
    want to pass multiple values into a procedure wherr the select query where condition is as ' select * from table name where param in (parameter)'

    The parameter should be like 12,36,48 and so on.

    I shall appreciate if anyone can help me in this.

    Thank you
    Shravan

  2. #2
    Join Date
    Jan 2001
    Posts
    153
    since the number of parameters is not static u can do this by sending the parameters in to an varchar2 field.
    variablein = '14,48,56,42,31,12'

    replace the where clause with the variable...
    Vijay.s

  3. #3
    Join Date
    Jun 2001
    Posts
    3

    I read your message. here is my stored procedure

    create or replace procedure ProcedureName (Parametername in String) as

    begin

    Open Cursor for

    SELECT * FROM tablename WHERE tablename.column IN
    (parametername);

    end;
    ----------------------------------------------------------------------
    In the above example of stored procedure the paramter value I ma sending is like 24,36,54,68 and the column datatype in database is number.

    So when I make parameter as string it will send it as '24,36,54' with quotes. Am i right? If I hardcode the parametervalue in the stored procedure it works fine but while passing it gives error.

    The otherway round as you told to parameter as Varchar2. I also tried that but doesn't work.

    Please help me.

    Thank you
    Shravan

  4. #4
    Join Date
    Jan 2001
    Posts
    153
    Hi

    I assume u r using oracle8i...use EXECUTE IMMEDIATE..

    Example :

    variable1 := 'SELECT * FROM tablename WHERE tablename.column IN ('||parametername||')';

    open urcursor for variable1;
    if urcorsuror%found then
    --do necessary
    null;
    end if;
    close urcursor;


    Vijay.s

  5. #5
    Join Date
    Jun 2001
    Posts
    3
    Hi,

    Thank you. I worked in the same manner and could solve it. but now I cannot return the values from that stored procedure as I am using cursor variables and opening the cursor as as follows
    --------------------------------------------------------------------
    create or replace procedure name (parameter1 in varchar2,
    test_cur in out packagename.Reference_cursor)

    Type test_cur_type is ref cursor;
    test_cur test_cur_type;

    begin

    variableSQL:='Sql statement';

    Open Test_cur for variableSQL;

    end;
    ----------------------------------------------------------------------
    here in the above example in the parameter list packagename refers toa package in which I defined a record and refernce.cursor means a referenced cursor in the package.

    package is as follows
    ------------------------------------------------------------------
    create or replace package packagename is

    type test_rec is record (

    varcolumn1 varchar2(100),
    varcloumn2 varchar2(10),
    varcolumn3 number(20));

    type reference.cursor is ref cursor return test_rec;
    end;
    --------------------------------------------------------------------
    Actually when I run this I need to get the output of stored procedures SQL statement from the Reference cursor I have used but it gives me error saying that "Duplicate in the Record, Table or the argument list not permitted"
    --------------------------------------------------------------------
    Note: the SQL statement in procedure is a dynamic one.
    ------
    Please help me if any one knows how to solve this.

    Thank you.
    Shravan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width