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

    Unanswered: Searching data from a table using stored procedure in oracle by passing tablename

    Pls help me by giving the solution .
    This procedure is not working properly.



    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 ;
    commit;
    end bank_search_sp;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Not working properly" means what exactly?

  3. #3
    Join Date
    Feb 2012
    Posts
    16

    error in the code

    the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
    ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
    ORA-06512: at line 1
    Last edited by kannama; 02-03-12 at 07:25.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; quotes problem again ...

    Rule of thumb: when dealing with dynamic SQL, put a statement into a VARCHAR2 variable ("v_tem" in your case) and display it on the screen by DBMS_OUTPUT.PUT_LINE. Review the result. Copy/paste it into the same SQL*Plus session. If it gets executed in SQL*Plus, it will be executed from your PL/SQL procedure as well.

Posting Permissions

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