| cursors and procedure problem,urgent!! [message #36460] |
Thu, 29 November 2001 00:38  |
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  |
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 |
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!!!
----------------------------------------------------------------------
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
