Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    16

    Unanswered: search storedprocedure to return multiple rows

    Please, help us i want to fetch more than one returns of rows while searching a data from table.Here in this code. if only one row matches the condition given then it returns the output,else where it shows an error when multiple rows matches the condition.

    create or replace procedure bank_search_test1
    (
    p_tablename in char,
    p_searchname in char,
    p_bankcode out char,
    p_bankname out char,
    p_distcode out number
    )
    as
    v_tem varchar2(500);
    begin
    v_tem := 'select bankcode,bankname,dist_code from ' || p_tablename || ' where bankcode like ''%' ||
    p_searchname || '%''';
    execute immediate v_tem into p_bankcode, p_bankname,p_distcode;
    end;

    This is the error shown when multiple rows matches

    Running "PENSIONS"."BANK_SEARCH_TEST1" ( P_TABLENAME = bank, P_SEARCHNAME = D ).
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at "PENSIONS.BANK_SEARCH_TEST1", line 13
    ORA-06512: at line 1

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >execute immediate v_tem into p_bankcode, p_bankname,p_distcode;
    P_BANKCODE is a scalar variable which means it can contain only a single value
    It can not hold the result set when more than 1 row is returned.
    The same is true for the other variables in the "INTO" clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Posts
    153
    Try with Pl/Sql table with Object type for the out value/s.

Posting Permissions

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