Hello
I have this query
but it is not work properly
pls check this and find a solution to this

create or replace procedure bank_insert_sp
(
p_tablename in varchar2,
p_bankcode in varchar2,
p_bankname in varchar2,
p_dist_code in number
)
as
v_tem varchar2(5000);
begin
v_tem:='insert into ' || UPPER(p_tablename) || '
(
bankcode,
bankname,
dist_code
)
values
(
' ||p_bankcode || ',
' ||p_bankname || ',
' ||p_dist_code || '
)';
execute immediate v_tem ;
end bank_insert_sp;

what is the error it is generating? how do you know it's not working? did you commit after the insert? You need to use quotes around strings in the insert. ore information

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.

After you will be able to spot the error, just stop hardcoding variable values into V_TEM, but bind them instead:
http://www.akadia.com/services/ora_bind_variables.html

try

What about being kind to your shared pool and CPU and not to propose non-scalable solution? There is more effective way described (with examples) in the link I posted...

Also, be prepared for another tweaking of your code, just after the bank name will contain single quote (').

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



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