Re: Howto generate records in stored procedure

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Mon, 16 Sep 2002 15:54:18 GMT
Message-ID: <3D85FE76.640EC172@exesolutions.com>

Jan van Toor wrote:

> Daniel,
>
> I didn't explain the problem correctly. I am not very experienced with
> Oracle. I am trying to convert a solution, I built with Borland's Interbase,
> to Oracle.
>
> In Interbase you can define a kind of output-record for a stored procedure.
> Based upon data of one or more tables, you can fabricate your own records.
> Everytime you execute an SUSPEND-command, a record is added to the
> resultset. In Interbase I would read from a cursor and give three
> SUSPEND-commands for every record read.
>
> A calling program can read this output as if it where a real table.
>
> Is this also possible with Oracle?
>
> Kind regards
> Jan van Toor
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> schreef in bericht
> news:3D820676.A88A7657_at_exesolutions.com...
> > Jan van Toor wrote:
> >
> > > Hi guys,
> > >
> > > I have got to write a stored procedure, which reads the contents of a
> table.
> > > For every record read, three identical records must be generated in a
> > > resultset (or temp table?). The resultset is connected to a REF CURSOR
> in a
> > > package.
> > >
> > > I work with Oracle7, so a lot of goodies like temp tables and
> Object-types
> > > can't be used.
> > > Do you know a solution? A code-snippet would be highly appreciated!
> > >
> > > Thanks in advance,
> > > Jan van Toor
> >
> > Temp tables are irrelevant to what you are trying to do. Just open a
> cursor,
> > fetch the record, and then follow the fetch with your three inserts.
> Easily done
> > in version 7. No need for anything fancy.
> >
> > But you are working with REF CURSORS in Oracle 7? Really?
> >
> > Daniel Morgan
> >

I've no experience with InterBase other than receiving some promotional stuff from Borland ages ago so this is just a guess. But what I think you are looking for is a REF CURSOR. A ref cursor can be declared to hold multiple rows of data with those rows defined by a SELECT statement. So for example from one of my classroom exmaples:

ref cursor definition CREATE OR REPLACE PACKAGE uw_type IS

TYPE t_ref_cursor IS REF CURSOR;

END;
/

called by parent and selects a specified number of rows from all_table CREATE OR REPLACE PROCEDURE child ( p_NumRecs IN PLS_INTEGER, p_return_cur OUT uw_type.t_ref_cursor) IS

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

END child;
/

calls the child procedure and uses DBMS_OUTPUT to display the results
(remember to set serveroutput on) CREATE OR REPLACE PROCEDURE parent ( pNumRecs VARCHAR2) IS

p_retcur uw_type.t_ref_cursor;
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;
/

And as the example demonstrates ... the ref cursor can be passed from procedure to procedure or, similarly to a calling program be it a web interface, VB, Crystal Reports, etc.

Hope this helps. Well that and I hope I understood what you were asking too.

Daniel Morgan Received on Mon Sep 16 2002 - 10:54:18 CDT