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

    Unanswered: stored procedure for search

    Is this stored procedure in oracle is correct for searching data from table by passing table name as a parameter

    CREATE OR REPLACE PROCEDURE bank_search_sp
    (
    p_tablename IN VARCHAR2,
    p_searchname IN VARCHAR2,
    p_bankcode OUT VARCHAR2,
    p_bankname OUT VARCHAR2,
    p_dist_code OUT NUMBER
    )
    AS
    v_tem VARCHAR2(5000);
    BEGIN
    v_tem := 'SELECT bankcode,bankname,dist_code FROM ' || UPPER (p_tablename) || '
    WHERE bankname LIKE '''|| p_searchname||'''';
    EXECUTE IMMEDIATE v_tem
    INTO p_bankcode,p_bankname,p_dist_code
    USING p_searchname ;
    END bank_search_sp;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Is it correct? You wrote it, you tested it. What do you say?

  3. #3
    Join Date
    Feb 2012
    Posts
    16

    Reply

    When i execute this the procedure is getting created but it is not working properly it shows errors like this

    Running "PENSIONS"."BANK_SEARCH_SP" ( P_TABLENAME = bank, P_SEARCHNAME = S ).

    ORA-01006: bind variable does not exist
    ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 15
    ORA-06512: at line 1

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't have your tables, so here's an example based on Scott's schema. See how I did it, and then adjust your code.
    Code:
    SQL> create or replace procedure p_search
      2    (p_tablename in char,
      3     p_job in char,
      4     p_ename out char
      5    )
      6  is
      7    v_tem varchar2(500);
      8  begin
      9    v_tem := 'select max(ename) from ' || p_tablename || ' where job like ''%' ||
     10             p_job || '%''';
     11    execute immediate v_tem into p_ename;
     12  end;
     13  /
    
    Procedure created.
    
    SQL> declare
      2    l_out varchar2(30);
      3  begin
      4    p_search('emp', 'CLERK', l_out);
      5    dbms_output.put_line(l_out);
      6  end;
      7  /
    SMITH
    
    PL/SQL procedure successfully completed.
    
    SQL>

  5. #5
    Join Date
    Feb 2012
    Posts
    16

    Thank u

    Its working

Posting Permissions

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