Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Stored Procedure for searching data from a table by passing tablename as a parameter
Linju
post Feb 6 2012, 05:15 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 3-February 12
Member No.: 46,707



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;


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
Go to the top of the page
 
+Quote Post
burleson
post Feb 6 2012, 07:16 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,435
Joined: 26-January 04
Member No.: 13



Hi Linju,

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

No.

Sorry, this is an Oracle DBA forum, we don't have the resources to tutor people!

You want to open a service request with Oracle technical support:

http://support.oracle.com

Having said that, see below, you forgot a quote mark and the firsr quote mark uis a "leaning" quote":

CODE
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || ';'


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 2nd September 2014 - 09:04 PM