| precedure problem [message #36448] |
Wed, 28 November 2001 05:12  |
S.Thiyagarajan
Messages: 4
Registered: November 2001
|
Junior Member |
|
|
hi all, i got a problem in stored procedures.i should pass the table name as a IN parameter to the procedure.how cna that be done.as as example
create precedure samp(a IN varchar2) as
begin
select * from passed argument(table name) where condition;
end;
how can i substitute the parameter for the table name.any help would be of great help.bye thiyagu.
----------------------------------------------------------------------
|
|
|
|
| Re: precedure problem [message #36449 is a reply to message #36448] |
Wed, 28 November 2001 05:32  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
use dynamic sql
create precedure samp(a IN varchar2) as
l_var varchar2(10);
begin
execute immediate 'select colname from '||a||' where cond' into l_var;
end;
----------------------------------------------------------------------
|
|
|
|
| Re: precedure problem [message #36451 is a reply to message #36449] |
Wed, 28 November 2001 19:51 |
S.Thiyagarajan
Messages: 4
Registered: November 2001
|
Junior Member |
|
|
hi,that was useful but for my stored procedure problem the case is as follows.i am giving you my procedure below
create or replace procedure mysearch(a in number,b in varchar2) is
i number;
cursor curmark is 'select wrel from '||b||' where wno=a';
begin
for i in curmark loop
insert into hold values(i.wrel);
mysearch(i.wrel,should have table name here i.e b);
end loop;
commit;
end;
here hold is a table.i will pass the table name as b and the the ID number in my table as a.how can i make this to execute in the way you have given.expecting your early reply.and do slightly explain your reply too.bye thiyagu.
----------------------------------------------------------------------
|
|
|
|
| Re: precedure problem [message #36453 is a reply to message #36451] |
Wed, 28 November 2001 21:10 |
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.
----------------------------------------------------------------------
|
|
|
|
| Re: procedure problem [message #36459 is a reply to message #36451] |
Thu, 29 November 2001 00:30 |
Thiyagarajan
Messages: 9
Registered: October 2000
|
Junior Member |
|
|
hi ,when i executed the procedure given by tinel it went to an infinite loop and said maximum number of open cursors exceeded.that may be because if do a recursive call on my procedure.but i need to do that.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 original proc i am passing only a number.now i also need to pass a varchar2.help me regarding this.any help is very welcome.bye thiyagu.
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
