Home » SQL & PL/SQL » SQL & PL/SQL » precedure problem
precedure problem [message #36448] Wed, 28 November 2001 05:12 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 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.

----------------------------------------------------------------------
Re: procedure problem [message #36459 is a reply to message #36451] Thu, 29 November 2001 00:30 Go to previous message
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.

----------------------------------------------------------------------
Previous Topic: About System triggers
Next Topic: how to write a file with PL/SQL
Goto Forum:
  


Current Time: Fri Mar 29 05:52:50 CDT 2024