Returning Result Sets from Stored Procedures? 
Author Message
 Returning Result Sets from Stored Procedures?

Hi,

I'm trying to find an example of how I can return a result set from a
stored procedure?  All the stored procedure examples that I'm finding in
the Oracle and PL/SQL books are written as "action procedures" which
perform an action and then return.

I'm writing a series of Web-based applications that use Server-Side
JavaScript to query the database, then format the returned query into
HTML for the client.  Rather than hard-coding complex SQL cursor-based
queries into my JavaScript, repeatedly, and sending them over the
network, I'd much rather pass arguments to  stored-procedures, and then
traverse the "result set" or cursor that is returned.

Is this possible?
And if so, does anyone out there have an example the could send me of,
at least, a stored procedure or package spec that returns the results of
a whole SELECT a, b, c FROM table WHERE... query?

Any pointers to good reading materials that illustrate this would also
be appreciated!

Thanks much in advance.
--
James Arvigo

============================================================
* Intranet Developer
* Thrifty Call, Inc. via The Maxim Group
* Austin, Texas
*----------------------------------------------------------
* EMAILS:
* Work: JamesA  -AT- ThriftyCall.com
* Home: JArvigo -AT- Hotmail.com
*
* ( Sorry I had to obfuscate the emails... *sigh* )
============================================================



Fri, 19 Jan 2001 03:00:00 GMT
 Returning Result Sets from Stored Procedures?


(if that email address didn't require changing)

Quote:

>Hi,

>I'm trying to find an example of how I can return a result set from a
>stored procedure?  All the stored procedure examples that I'm finding in
>the Oracle and PL/SQL books are written as "action procedures" which
>perform an action and then return.

>I'm writing a series of Web-based applications that use Server-Side
>JavaScript to query the database, then format the returned query into
>HTML for the client.  Rather than hard-coding complex SQL cursor-based
>queries into my JavaScript, repeatedly, and sending them over the
>network, I'd much rather pass arguments to  stored-procedures, and then
>traverse the "result set" or cursor that is returned.

In short, it'll look like this:

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor;
end;
/

With 7.2 on up of the database you have cursor variables.  Cursor variables
are cursors opened by a pl/sql routine and fetched from by another application
or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as
well as open them). The cursor variables are opened with the privelegs of the
owner of the procedure and behave just like they were completely contained
within the pl/sql routine. It uses the inputs to decide what database it will
run a query on.

Here is an example:

create or replace package types
as
    type cursorType is ref cursor;
end;
/

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;

    return l_cursor;
end;
/

REM SQL*Plus commands to use a cursor variable

variable c refcursor
exec :c := sp_ListEmp
print c

-----------------------------------------------------

and the Pro*c to use this would look like:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CURSOR  my_cursor;
    VARCHAR     ename[40];
    int         empno;
EXEC SQL END DECLARE SECTION;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL ALLOCATE :my_cursor;

    EXEC SQL EXECUTE BEGIN
        :my_cursor := sp_listEmp;
    END; END-EXEC;

    for( ;; )
    {
        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH :my_cursor INTO :ename, empno;

        printf( "'%.*s', %d\n", ename.len, ename.arr, empno );
    }
    EXEC SQL CLOSE :my_cursor;

Quote:
}
>Is this possible?
>And if so, does anyone out there have an example the could send me of,
>at least, a stored procedure or package spec that returns the results of
>a whole SELECT a, b, c FROM table WHERE... query?

>Any pointers to good reading materials that illustrate this would also
>be appreciated!

>Thanks much in advance.

Thomas Kyte

Oracle Government
Herndon VA

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.



Sat, 20 Jan 2001 03:00:00 GMT
 Returning Result Sets from Stored Procedures?
Thomas,

Thanks!

I'll take this info and work on it, and post pack to you an example of this usage
in SSJS for future reference.

Regards...
--
James Arvigo

============================================================
* SQL Server & Oracle DBA
* Software & Intranet Developer
* Thrifty Call, Inc. via The Maxim Group
* Austin, Texas
*----------------------------------------------------------
* EMAILS:
* Work: JamesA  -AT- ThriftyCall.com
* Home: JArvigo -AT- Hotmail.com
*
* ( Sorry I had obfuscate the emails... *sigh* )
============================================================



Sat, 20 Jan 2001 03:00:00 GMT
 Returning Result Sets from Stored Procedures?
I have discovered the answer to this process, and am posting it here in case
any others are interested in this same issue of Netscape LiveWire to Oracle
queries via stored procedures.  Below is example code that I have working,
and here is an old LiveWire document URL to read more about it:

http://developer.netscape.com:80/docs/manuals/communicator/jsref/lwa1...
http://developer.netscape.com:80/docs/manuals/communicator/jsref/lwa2...
http://developer.netscape.com:80/docs/manuals/communicator/jsref/lwa3...

=======================================
Oracle SQL*Plus:
=======================================

SQL> create or replace package types as
  2    type custCursType is ref cursor
  3    return customer%rowtype;
  4  end types;
  5  .
SQL> /

Package created.

SQL> create or replace procedure
  2  CustListProc(custrows in out types.custCursType)
  3  as begin
  4     open custrows for select * from customer;
  5  end CustListProc;
  6  .
SQL> /

Procedure created.

=======================================
Server-Side JavaScript:
=======================================

// Connect to DB, call stored proc, get result set
dbConn = project.pool.connection();
dbProc = dbConn.storedProc("CustListProc");
dbRslt = dbProc.resultSet();

// Begin a table view of the data
write("<table bgcolor=#F0F0F0 border=2>\n");

// Create a column headers row
write("<tr bgcolor=#669999>\n");
for (c = 0; c < dbRslt.columns(); c++ )
{
    write("<th>" + dbRslt.columnName(c) + "</th>\n");

Quote:
}

write("</tr>\n");

// Loop through each result set record
while (dbRslt.next())
{
    // Create a row for each record
    write("<tr>\n");
    for (c = 0; c < dbRslt.columns(); c++ )
    {
        // Display column value, unless == NULL
        if (dbRslt[c] == null) {
            write("<td><br></td>\n"); }
        else {
            write("<td>" + dbRslt[c] + "</td>\n"); }
    }
    write("</tr>\n");

Quote:
}

write("</table>\n");

// Close result set and release DB connection
dbRslt.close();
dbConn.release();

=======================================
END of EXAMPLE
=======================================

Anybody else out there struggling with this is welcome to write me at the
Email address below (you'll have to unobfuscate it).

Quote:

> Hi,

> I'm trying to find an example of how I can return a result set from a
> stored procedure?  All the stored procedure examples that I'm finding in
> the Oracle and PL/SQL books are written as "action procedures" which
> perform an action and then return.

> I'm writing a series of Web-based applications that use Server-Side
> JavaScript to query the database, then format the returned query into
> HTML for the client.  Rather than hard-coding complex SQL cursor-based
> queries into my JavaScript, repeatedly, and sending them over the
> network, I'd much rather pass arguments to  stored-procedures, and then
> traverse the "result set" or cursor that is returned.

> Is this possible?
> And if so, does anyone out there have an example the could send me of,
> at least, a stored procedure or package spec that returns the results of
> a whole SELECT a, b, c FROM table WHERE... query?

> Any pointers to good reading materials that illustrate this would also
> be appreciated!

> Thanks much in advance.
> --
> James Arvigo

--
James Arvigo

============================================================
* SQL Server & Oracle DBA
* Software & Intranet Developer
* Thrifty Call, Inc. via The Maxim Group
* Austin, Texas
*----------------------------------------------------------
* EMAILS:
* Work: JamesA  -AT- ThriftyCall.com
* Home: JArvigo -AT- Hotmail.com
*
* ( Sorry I had obfuscate the emails... *sigh* )
============================================================



Tue, 23 Jan 2001 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. returning result sets from stored procedures

2. returning result sets from stored procedures

3. return result set from stored procedure

4. return result set from stored procedure

5. Return result set from stored procedure

6. Return result set from stored procedure, possible ?

7. Returning result sets from stored procedures...

8. Returning result sets in oracle stored procedure (OLEDB)

9. return multiple result sets from a stored procedure

10. Can't update result set returned from stored procedure using RDO

11. Returning result set from function to stored procedure is very slooooooooow

12. best way to return a paged result set from a stored procedure


 
Powered by phpBB® Forum Software