Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql problem
dynamic sql problem [message #37137] Sun, 20 January 2002 21:55 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.....
Previous Topic: Deferred trigger?
Next Topic: Pl/SQL Store procedure size too large -PLS-00123
Goto Forum:
  


Current Time: Fri Mar 29 05:24:42 CDT 2024