Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple SP with Simple SELECT
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:
--===================
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;
--===================
BEGIN
OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
END child;
/
--===================
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