| Dynamic SQL [message #20191] |
Mon, 06 May 2002 03:18  |
Amitabha
Messages: 14
Registered: May 2002
|
Junior Member |
|
|
I am to construct and execute sql statements dynamically I.e Cursors , the specific help is saught for dynamic changes in select statements from < table > name and < columns > part.
Native Dynamic Sql is working for where condition like :
declare
sql_stmt VARCHAR2(1000);
fil varchar2(100):='emp';
begin
sql_stmt:='declare cursor c1 is select ename from emp where job = :2;'
||' begin for i in c1 loop dbms_output.put_line( ''Name : '' || i.ename ); end loop; end; ';
execute immediate sql_stmt using IN 'CLERK';
end;
====================================
but i need the table name part to be parameterized. can any body help ? Please ?
|
|
|
|
| Re: Dynamic SQL [message #20192 is a reply to message #20191] |
Mon, 06 May 2002 05:21  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
declare
sql_stmt VARCHAR2(1000);
fil varchar2(100):='emp';
type r is ref cursor;
r1 r;
l_ename varchar2(30);
begin
sql_stmt:='select ename from '||fil ||' where job = '||''''||'CLERK'||'''';
open r1 for sql_stmt;
loop
fetch r1 into l_ename;
if r1%notfound then
exit;
end if;
end loop;
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
