Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate with Dynamic From table (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Execute Immediate with Dynamic From table [message #660871] Tue, 28 February 2017 01:15 Go to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

I want to pass the table name as dynamic value from the parameter to the query. Its accepting if I am concatenating the dynamic value to the query .

But its not working with the help of USING clause while its is working for other where condition columns.

Ex:


set serveroutput on  
declare
 max_row_id  varchar2(200);
 p_table_name  varchar2(200)  :=  'EMP';
begin
--execute  immediate 'select max(rowid) from  :from_table'  into  max_row_id  using p_table_name  ;
execute  immediate 'select max(rowid) from '|| p_table_name into  max_row_id    ;
DBMS_OUTPUT.put_line(' Rowid :' ||max_row_id );
end;


But the commented part is not working fine.

Please help me

Thanks
SaiPradyumn
Re: Execute Immediate with Dynamic From table [message #660873 is a reply to message #660871] Tue, 28 February 2017 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bind variable can bind values not object component (table name, column_name, procedure name...).

Maybe in your case, you can use SQL*Plus substitution variable:
set serveroutput on  
declare
 max_row_id  varchar2(200);
begin
  select max(rowid) from &table_name into  max_row_id;
  DBMS_OUTPUT.put_line(' Rowid :' ||max_row_id );
end;
If this is the case then you can just use the following for the same:
select ' Rowid :'||max(rowid) max_row_id from &table_name;

[Updated on: Tue, 28 February 2017 02:02]

Report message to a moderator

Re: Execute Immediate with Dynamic From table [message #660875 is a reply to message #660873] Tue, 28 February 2017 01:51 Go to previous message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Ok Got It .

Thank You very much Michel .

[Updated on: Tue, 28 February 2017 01:52]

Report message to a moderator

Previous Topic: 2 column values going inverse and how to display corresponding keys
Next Topic: online table structure changes without affecting the user
Goto Forum:
  


Current Time: Thu Feb 22 19:48:44 CST 2018

Total time taken to generate the page: 0.02907 seconds