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
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 ;
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
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.