Re: How to create procedures without inserts (newbie switching from Sybase to Oracle)

pcmodeler wrote:

> OK, I've been using SQL Sybase stored procedures as a datasource for
> Crystal Reports. I'm trying to learn Oracle as we are switching
> environments.
>
> I see in Oracle that when you try to do standard select statements
> within a procedure that they are always combined with insert
> statements into a table.

What you think you see is not correct. There is absolutely no need for an insert statement with a select so I am left wondering what it is you are looking at: Most certainly not the Oracle help info. on Crystal's web site which explicitly states that you MUST use an IN OUT ref cursor to return rows.

The following procedure works quite nicely with Crystal Reports:

CREATE OR REPLACE PROCEDURE child (

   p_NumRecs IN PLS_INTEGER,
   p_return_cur IN OUT SYS_REFCURSOR)
IS

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

END child;
/

I don't see an insert statement anywhere.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)