The standard way for debugging dynamic SQL is: construct the statement (V_TEM variable in your case) and then print it - you may use e.g. DBMS_OUTPUT. Copy/paste it verbatim and try to run it statically. Examine the error. Examine the statement and fix the failure cause.
Thank you for your kind information regarding the stored procedure for the insert query
I have more queries to clarify hope u will help me
I paste the Query n below
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 ;
This is a stored procedure for searching data from table .Here the procedure is created but it will not work properly.I hope u will consider this also
I doubt it - the count of single quotes around P_SEARCHNAME is wrong. Have you not considered to bind that variable(s) instead? Why? You already use INTO clause in EXECUTE IMMEDIATE.
Originally Posted by kannama
Here the procedure is created but it will not work properly.I hope u will consider this also
Alas! yet another mysterious "not work properly" Oracle behaviour. You are very unlucky to fall into such a dreadful situation; in fact I never faced it, Oracle did not document it anywhere, so I cannot assist more.
Besides, without defining, what "work properly" means, especially when multiple rows satisfy that condition, it is impossible to suggest anything.