Re: Howto generate records in stored procedure

Daniel Morgan (dmorgan_at_exesolutions.com) wrote:
: 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

I think he means he wants to write SQL statements like the following

        SELECT * FROM THIS_IS_A_PROCEDURE_NOT_A_TABLE ; I don't think Oracle can do this.

The code for the procedure looks sort of like this

        PROCEDURE THIS_IS_A_PROCEDURE_NOT_A_TABLE                 while I-havent-finished                 

			build-one-row
			pass-it-back-to-the-caller
			yield

		end loop

	END PROCEDURE

You can do this in various other databases, but I have not seen a technique to allow this in oracle. Note that contrary to what some oracle programmers seem to thing when they see this, this is nothing like `select my-function() from what-ever' because is this latter case you have no way to make the select loop over a number of rows controlled by the function. Received on Tue Sep 24 2002 - 15:13:11 CDT