Home » SQL & PL/SQL » SQL & PL/SQL » cursors and procedure problem,urgent!!
cursors and procedure problem,urgent!! [message #36460] Thu, 29 November 2001 00:38 Go to next message
S.Thiyagarajan
Messages: 4
Registered: November 2001
Junior Member
hi all,i got a problem with my procedure.i have given below my original procedure which works perfect.i need to make the table name in that procedure as a input parameter.thats what is needed.what should i do for that.

create or replace procedure mysearch(a in number) is
i number;
cursor curmark is select wrel from hyper where wno=a;
begin
for i in curmark loop
insert into hold values(i.wrel);
mysearch(i.wrel);
end loop;
commit;
end;

hold is a table and in my procedure i am passing only a number.now i also need to pass a varchar2 which is the table name i will pass it recursively and the table name will be the same in the recursive calls too.how should i modify my procedure.help me regarding this.any help is very welcome.bye thiyagu.

----------------------------------------------------------------------
Re: cursors and procedure problem,urgent!! [message #36463 is a reply to message #36460] Thu, 29 November 2001 02:28 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
this should work:
CREATE OR REPLACE PROCEDURE mysearch
(A IN NUMBER,b IN VARCHAR2) IS
TYPE cursor_type IS REF CURSOR;
c cursor_type;
c_details c%ROWTYPE;
s_query VARCHAR2(1000);
BEGIN
s_query := 'SELECT wrel FROM ' || b || ' WHERE wno = a';
OPEN c FOR s_query;
LOOP
FETCH c INTO c_details;
EXIT WHEN c%NOTFOUND;
INSERT INTO hold VALUES(i.wrel);
END LOOP;
CLOSE c;
END;

you use a references to a cursor and then open cursor using a dinamic sql statement.

and you should read your older messages on the forum as well, because i allerady send this procedure to you!

----------------------------------------------------------------------
Re: cursors and procedure problem,urgent!! [message #36466 is a reply to message #36460] Thu, 29 November 2001 03:00 Go to previous messageGo to next message
Thiyagarajan
Messages: 9
Registered: October 2000
Junior Member
hi tinel,i executed your procedure.i made a single change and it gives an error as
"invalid column name".it is not taking the condition part correctly.and also i need a recursive call.how can that be done.if i give recursion in your given program.ita says
"maximum open cursors exceeded".
what to do.i have given my procedure below.thankz.and i did read that procedure too,but i gave a seperate message because i felt i needed to give a more detiled explanation.thankz for that too.expecting your help.

CREATE OR REPLACE PROCEDURE mysearch
(a IN NUMBER,b IN VARCHAR2) IS
TYPE cursor_type IS REF CURSOR;
c cursor_type;
c_details number;
s_query VARCHAR2(1000);
BEGIN
s_query := 'SELECT wrel FROM ' || b || ' WHERE wno = a';
OPEN c FOR s_query;
LOOP
FETCH c INTO c_details;
EXIT WHEN c%NOTFOUND;
INSERT INTO hold VALUES(c_details);
mysearch(c_details,b);
END LOOP;
CLOSE c;
end;

its not taking the value of variable 'a' and shows max open cursors.what to do!!!

----------------------------------------------------------------------
Re: cursors and procedure problem,urgent!! [message #36468 is a reply to message #36460] Thu, 29 November 2001 03:49 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
Hi thiyagarajan! why do you need a recursive call?
and the part with invalid column name i really don't know why. if it's more convenable for you send me an email at shaokan@k.ro

----------------------------------------------------------------------
Maximum number of cursors [message #37434 is a reply to message #36463] Thu, 07 February 2002 07:19 Go to previous message
Sunil
Messages: 132
Registered: September 1999
Senior Member
If more number of cursors are used around 6000,
will there be a situation of database shutdown.please let me know.

thanks
sunil
Previous Topic: I want to load data from 3 tables in schema1 into a table in schema2 using a CURSOR..
Next Topic: expected symbol name is missing
Goto Forum:
  


Current Time: Fri Sep 24 14:13:28 CDT 2021