Home » SQL & PL/SQL » SQL & PL/SQL » Need procedure to display all the rows
icon9.gif  Need procedure to display all the rows [message #360914] Mon, 24 November 2008 06:09 Go to next message
jagadish.varma
Messages: 2
Registered: November 2008
Location: hyderabad
Junior Member
Hi all,

I need a procedure to display all the rows in the table when you pass a table name in the procedure...

Thanks&regards,
jagadish varma
Re: Need procedure to display all the rows [message #360915 is a reply to message #360914] Mon, 24 November 2008 06:13 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Use Dynamic Query,

[Updated on: Mon, 24 November 2008 06:13]

Report message to a moderator

Re: Need procedure to display all the rows [message #360918 is a reply to message #360914] Mon, 24 November 2008 06:17 Go to previous messageGo to next message
jagadish.varma
Messages: 2
Registered: November 2008
Location: hyderabad
Junior Member
Hi i am new to this Pl/sql so...

This is what i am writing
CREATE OR REPLACE Procedure Display_table
( Table_name out varchar2 )
IS
cursor c1 is
select * from Table_name;

BEGIN
open c1;
fetch c1 into Table_name;
if c1%notfound then
exit ;
end if;
select * from Table_name;
commit;
close c1;
end;

But it shows so many errors...
please some provide me a solution to move forward...
Re: Need procedure to display all the rows [message #360922 is a reply to message #360918] Mon, 24 November 2008 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As already said: use dynamic SQL.
Search for this and dbms_sql, in the documentation, here...

Regards
Michel
Re: Need procedure to display all the rows [message #360956 is a reply to message #360918] Mon, 24 November 2008 08:03 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
jagadish.varma wrote on Mon, 24 November 2008 07:17

This is what i am writing
CREATE OR REPLACE Procedure Display_table
( Table_name out varchar2 )
IS
cursor c1 is
select * from Table_name;

BEGIN
open c1;
fetch c1 into Table_name;
if c1%notfound then
exit ;
end if;
select * from Table_name;
commit;
close c1;
end;



You do not commit when you are only selecting.

And why use a procedure when all you need is
select * from &table_name;


QED
Previous Topic: Oracle Job which calls a stored procedure taking too much time
Next Topic: Pagesize and performance
Goto Forum:
  


Current Time: Wed Dec 07 14:18:28 CST 2016

Total time taken to generate the page: 0.12604 seconds