Re: Howto generate records in stored procedure

Malcolm Dew-Jones wrote:

> 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.

I suspect soemthing like it can be done as what you describe appears identical to what Oracle Forms does when you use a procedure as a data-source rather than a table or view. But I must confess that I have never tried it, never seen anyone do it, and just failed trying to fool Oracle into letting me do it.

Might make an interesting research project unless someone knows how Oracle connects Form blocks to an out paramater in a procedure and is willing to share their knowledge.

Daniel Morgan Received on Wed Sep 25 2002 - 14:49:08 CDT