Home » SQL & PL/SQL » SQL & PL/SQL » How to display rows in stored procedure?
How to display rows in stored procedure? [message #224305] Tue, 13 March 2007 16:53 Go to next message
LAScorpion
Messages: 8
Registered: February 2007
Location: LA
Junior Member

How to display rows in stored procedure?

if i use SELECT * FROM ***
if says:
PLS-00428: an INTO clause is expected in this SELECT statement

Is there any tools to displaying it on the screen?

DMBS_OUPUT seems can only display a value. not the whole row.

Thanks,
Joseph
Re: How to display rows in stored procedure? [message #224307 is a reply to message #224305] Tue, 13 March 2007 17:04 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
You can't do that from PL/SQL.

Well, not easily anyway.

You can implement a procedure that "steps" through the columns of your SELECT statement, and displayed the values.

Check this post for details:

http://www.orafaq.com/forum/m/124129/42986/#msg_124129
Re: How to display rows in stored procedure? [message #224391 is a reply to message #224307] Wed, 14 March 2007 02:42 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would something like this satisfy your needs?
SQL> l
  1  CREATE OR REPLACE PROCEDURE prc_dept (par_out OUT sys_refcursor)
  2  IS
  3  BEGIN
  4    OPEN par_out FOR SELECT * FROM dept;
  5* END;
SQL> /

PROCEDURE created.

SQL> var l_dpt refcursor;
SQL> EXEC prc_dept (:l_dpt);

PL/SQL PROCEDURE successfully completed.

SQL> print l_dpt;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
Re: How to display rows in stored procedure? [message #224834 is a reply to message #224391] Thu, 15 March 2007 11:54 Go to previous messageGo to next message
LAScorpion
Messages: 8
Registered: February 2007
Location: LA
Junior Member

No,
Actually I want to print many sql statement results, not just one, if just one, i'd rather type it on SQL*PLUS.

I want to execute a procedure to display all (for example 10) selection results.

Is there any way?

Thanks,
Joseph
Re: How to display rows in stored procedure? [message #224840 is a reply to message #224834] Thu, 15 March 2007 12:34 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, if there are several SQL statements as the one you showed us in your first post (SELECT <column_list> FROM ...), could you - instead of PL/SQL script - put those selects into a pure SQL script?

However, if there's something else there (for example, cursors, some computing, etc.) which really needs PL/SQL, you might INSERT result of those select statements into a table and, as the PL/SQL script finishes, SELECT * from those tables.
Previous Topic: Query Failing with Invalid Number
Next Topic: Column Headings
Goto Forum:
  


Current Time: Sun Dec 04 18:36:33 CST 2016

Total time taken to generate the page: 0.08111 seconds