Re: Howto generate records in stored procedure

Jan,

I have been involved in porting databases/applications from Interbase to Oracle [n.b. Delphi/Midas applications mainly]. I haven't used REF CURSORs with Delphi, and have managed to get by with views, as Oracle has extensive capabilities when it comes to views compared with Interbase. You can have unions, have lots of functions at your disposal etc.
In your particular case, if you need to return 3 records for each original one, you could try to use some cartesian product of that table with another one with just 3 records in it - easy if Oracle 7 supports inline views (don't know that, sorry).
e.g.
select col1, coln
  from t, (select 1 from all_tables where rownum <= 3) Hope this helps.

Raphael

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D85FE76.640EC172_at_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 Wed Sep 18 2002 - 10:22:59 CDT