dynamic sql problem [message #37137] |
Sun, 20 January 2002 21:55 |
S.Thiyagarajan
Messages: 4 Registered: November 2001
|
Junior Member |
|
|
hi all,
i got his problem in dynamic sql and it will be helpful if u can help me solve this problem.this is VERY URGENT.
heres the pl/sql block that i have written
create or replace procedure mysearch(a in number,b in varchar2) is
type refcur is ref cursor;
rc refcur;
i number;
wd number;
dno number;
begin
open rc for 'select relsenseno from ' || b || 'where senseno = :dno' using a; -- line number 8
loop
fetch rc into wd;
exit when rc % notfound;
insert into hold values(a,wd);
mysearch(wd,b);
end loop;
close rc;
commit;
end;
when this is executed it saya a error that
the sql command is not properly ended at line number 8.
what could be the problem in this line.
i am getting the table name and the number that is usedd for searching as dynamic variables.
this is very urgent and so expecting replies very soon please.
bye thiyagu.
|
|
|
Re: dynamic sql problem [message #37138 is a reply to message #37137] |
Sun, 20 January 2002 23:58 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
Hi,
please give one or more space before 'where' in line number 8.
open rc for 'select relsenseno from '
|| b || ' where senseno = :dno' using a; -- line number 8
and try again. if u have still problem then let me know.
cheers
pratap
|
|
|
Re: dynamic sql problem [message #37187 is a reply to message #37137] |
Wed, 23 January 2002 04:44 |
manoj v nair
Messages: 1 Registered: January 2002
|
Junior Member |
|
|
Try This
Why don't u use immediate execute statement while using dynamic tables.
Usage-
immediate execute 'create table ' || b || '(code varchar2(10)......';
if this is not a solution for ur problem then i think my understanding of the problem is wrong.
Please respond asap.....
|
|
|