Re: Simple SP with Simple SELECT

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 13 Sep 2003 21:46:42 -0700
Message-ID: <1063514784.598451@yasure>

Heather B wrote:

>hi there,
>
>i am new to oracle, but not new at all to sql or to coding stored
>procedures. i am trying to write simple stored procedure in plsql for
>oracle 9, and my sp needs to simply return a full recordset. no parameters,
>no where clause, nothing else. just a simple select. but oracle is
>erroring, reporting that i am missing my into clause in my sp. i have
>searched the web and found only sp examples that have select into and then
>output parameters that somehow contain the fields of the recordset. is this
>the only way? isn't there a simpler way with no parameters, and just a full
>recordset being returned in response to a query?
>
>thanks for any help.
>
>heather
>
>
>

Your previous experience, most likely SQL Server, will require you to unlearn a lot. You can not approach Oracle, as you have, with the attitude that SQL is not new to you and therefore you can just start writing stored procedures ... you can't. And even after I show you the syntax you need ... you still shouldn't write it as you don't understand the huge differences in architecture and concepts.

Here's what you need:

--===================

CREATE OR REPLACE PROCEDURE parent (

   pNumRecs VARCHAR2)
IS

p_retcur SYS_REFCURSOR;
at_rec all_tables%ROWTYPE;

BEGIN
   child(pNumRecs, p_retcur);

   FOR i IN 1 .. pNumRecs
   LOOP

      FETCH p_retcur
      INTO at_rec;

      DBMS_OUTPUT.PUT_LINE(at_rec.table_name ||
      ' - ' || at_rec.tablespace_name ||
      ' - ' || TO_CHAR(at_rec.initial_extent) ||
      ' - ' || TO_CHAR(at_rec.next_extent));

   END LOOP; END parent;
/

--===================

CREATE OR REPLACE PROCEDURE child (
  p_NumRecs IN PLS_INTEGER,
  p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
   OPEN p_return_cur FOR
   'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;

END child;
/

--===================

To test:

SET SERVEROUTPUT ON exec parent(1)
exec parent(17)

--===================

After you have tested this ... don't write another line of code until you can explain how the DBMS_FLASHBACK built-in package works ... and why. I don't say this to be difficult. I say this in the hopes you won't make a huge mess and then blame it on Oracle.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat Sep 13 2003 - 23:46:42 CDT