Execute Immediate with Dynamic From table [message #660871] |
Tue, 28 February 2017 01:15 |
|
saipradyumn
Messages: 419 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 |
|
Michel Cadot
Messages: 68645 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
|
|
|
|