Executing Stored Procedure that returns result set (in Kiva) 
Author Message
 Executing Stored Procedure that returns result set (in Kiva)

I've seen this question posed a couple of times, but I can't quite get
the solutions to work.  I want to execute a stored procedure that
returns a result set back to Kiva (aka Netscape Application Server).
I'm fairly new to Oracle, having come from a Sybase shop.  In Sybase,
I was used to having a stored procedure return a result set from a
procedure simply by performing a select in the proc.  However, what
I've been reading so far on this newsgroup is that I have to do some
sort of fancy footwork with declaring a package and a cursor and what
not.  Is there no easier way?  And furthermore, it appears that the
return value for most of these solutions is a cursor, which I am not
so certain will work, but of this I'm uncertain.  Could someone
explain to me like a 5th grader how I should do this?

Thanks,
Ken



Fri, 09 Feb 2001 03:00:00 GMT
 Executing Stored Procedure that returns result set (in Kiva)

Ken,

I agree with you... it's a little annoying to have to do the extra "fancy
footwork" as you descrribed in order to return a result set, however, for
now... suffice it to say... this works.

Secondly... I'm afraid that returning result sets to a Web App server and
then processing the result set in your web app server.... well it's just
not 5th grader type of stuff.  It's pretty deep and it's about as deep as
the relationship between a Web App server and an RDBMS go.  So... here's
some examples that DO use a cursor as you said, but that I have working on
my Netscape Enterprise Server 3.5.1 (a.k.a. LiveWire, a.k.a. "Kiva++").

Good luck!

Here's an example of an Oracle stored procedure I wrote that returns a
result set:

-------------------------
PACKAGE        types as
   type customer_cursor_type is ref cursor return customer%rowtype;
   type dialup_cursor_type is ref cursor return dialup%rowtype;
end types;

CREATE OR REPLACE PROCEDURE
getcustlist(custrows in out types.customer_cursor_type) as
begin
   open custrows for select * from customer order by cust_id;
end getcustlist;
-------------------------

Here's some Server Side JavaScript for Netscape Enterprise Server that
uses this result set:
(View it in a monofont like Courrier 10)

function displayQueryResult()
{
var nRetVal;          // integer return value
var dbProc, dbCurs;   // stored procedure accessor, result set accessor
var dbConn = project.pool.connection();  // temp database connection

    switch (client.qryType)
    {
        case "NewCalls":
            // Call a stored procedure that returns a single value
            dbProc = dbConn.storedProc("NewCallsFunc", client.custID,
                                       client.fromDate, client.toDate);
            nRetVal = dbProc.returnValue();
            write("Return Value = " + val + "<br>\n");
            write("<INPUT TYPE=submit NAME=done VALUE='Done'>\n");
            break;

        case "Minutes":
            // Call a stored procedure that returns a loopable result-set
cursor
            dbProc = dbConn.storedProc("GetCustList");
            // Get the returned result set
            dbCurs = dbProc.resultSet();

            // Generate an HTML display table from a DB cursor
            genResultSetTable(dbCurs);  // See function below
            dbCurs.close();  // close the cursor
            break;

        case "Duration":
            // and so forth...
            break;
    }  // switch

    dbConn.release();  // Release the DB connection

Quote:
}  // END function()

function genCursorTable(dbCurs)
{
var nRow, nCol;
var sHTML;

    // Generate a table header from the cursor column names
    sHTML  = "<TABLE bgcolor=F0F0F0 border=2 cellspacing=1
cellpadding=4>\n";
    sHTML += "<TR bgcolor=669999>\n";
    for (nCol = 0; nCol < dbCurs.columns(); nCol++ ) {
        sHTML += "<TH height=32>" + dbCurs.columnName(nCol) + "</TH>\n"; }

    sHTML += "</TR>\n";
    write(sHTML);

    // Loop through the cursor, draw each row.
    while (dbCurs.next())
    {
        // Display row contents
        for (nCol = 0; nCol < dbCurs.columns(); nCol++ )
        {
            // Display column value, unless == NULL
            if (dbCurs[nCol] != null)
                sHTML += "<TD height=24>" + dbCurs[nCol] + "</TD>\n";
            else
                sHTML += "<TD><br></TD>\n";
        }  // for
        sHTML += "</TR>\n";
        write(sHTML);
    } // while
    sHTML  = "</TABLE></P>\n";
    sHTML += "<INPUT TYPE=submit NAME=done VALUE='Done'>\n";
    write(sHTML);

Quote:
}  // END genCursorTable()

--
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* )
============================================================

Quote:

> I've seen this question posed a couple of times, but I can't quite get
> the solutions to work.  I want to execute a stored procedure that
> returns a result set back to Kiva (aka Netscape Application Server).
> I'm fairly new to Oracle, having come from a Sybase shop.  In Sybase,
> I was used to having a stored procedure return a result set from a
> procedure simply by performing a select in the proc.  However, what
> I've been reading so far on this newsgroup is that I have to do some
> sort of fancy footwork with declaring a package and a cursor and what
> not.  Is there no easier way?  And furthermore, it appears that the
> return value for most of these solutions is a cursor, which I am not
> so certain will work, but of this I'm uncertain.  Could someone
> explain to me like a 5th grader how I should do this?

> Thanks,
> Ken



Sat, 10 Feb 2001 03:00:00 GMT
 Executing Stored Procedure that returns result set (in Kiva)
I am trying to do something similiar from ODBC. A question arises from the
example below:

GetCustList seems to be called without a parameter, yet it returns a
customer_cursor_type variable. Is this right?

Thanks Much,
Randy Brokaw

Quote:

>Ken,

>I agree with you... it's a little annoying to have to do the extra "fancy
>footwork" as you descrribed in order to return a result set, however, for
>now... suffice it to say... this works.

>Secondly... I'm afraid that returning result sets to a Web App server and
>then processing the result set in your web app server.... well it's just
>not 5th grader type of stuff.  It's pretty deep and it's about as deep as
>the relationship between a Web App server and an RDBMS go.  So... here's
>some examples that DO use a cursor as you said, but that I have working on
>my Netscape Enterprise Server 3.5.1 (a.k.a. LiveWire, a.k.a. "Kiva++").

>Good luck!

>Here's an example of an Oracle stored procedure I wrote that returns a
>result set:

>-------------------------
>PACKAGE        types as
>   type customer_cursor_type is ref cursor return customer%rowtype;
>   type dialup_cursor_type is ref cursor return dialup%rowtype;
>end types;

>CREATE OR REPLACE PROCEDURE
>getcustlist(custrows in out types.customer_cursor_type) as
>begin
>   open custrows for select * from customer order by cust_id;
>end getcustlist;
>-------------------------

>Here's some Server Side JavaScript for Netscape Enterprise Server that
>uses this result set:
>(View it in a monofont like Courrier 10)

>function displayQueryResult()
>{
>var nRetVal;          // integer return value
>var dbProc, dbCurs;   // stored procedure accessor, result set accessor
>var dbConn = project.pool.connection();  // temp database connection

>    switch (client.qryType)
>    {
>        case "NewCalls":
>            // Call a stored procedure that returns a single value
>            dbProc = dbConn.storedProc("NewCallsFunc", client.custID,
>                                       client.fromDate, client.toDate);
>            nRetVal = dbProc.returnValue();
>            write("Return Value = " + val + "<br>\n");
>            write("<INPUT TYPE=submit NAME=done VALUE='Done'>\n");
>            break;

>        case "Minutes":
>            // Call a stored procedure that returns a loopable result-set
>cursor
>            dbProc = dbConn.storedProc("GetCustList");
>            // Get the returned result set
>            dbCurs = dbProc.resultSet();

>            // Generate an HTML display table from a DB cursor
>            genResultSetTable(dbCurs);  // See function below
>            dbCurs.close();  // close the cursor
>            break;

>        case "Duration":
>            // and so forth...
>            break;
>    }  // switch

>    dbConn.release();  // Release the DB connection
>}  // END function()

>function genCursorTable(dbCurs)
>{
>var nRow, nCol;
>var sHTML;

>    // Generate a table header from the cursor column names
>    sHTML  = "<TABLE bgcolor=F0F0F0 border=2 cellspacing=1
>cellpadding=4>\n";
>    sHTML += "<TR bgcolor=669999>\n";
>    for (nCol = 0; nCol < dbCurs.columns(); nCol++ ) {
>        sHTML += "<TH height=32>" + dbCurs.columnName(nCol) + "</TH>\n"; }

>    sHTML += "</TR>\n";
>    write(sHTML);

>    // Loop through the cursor, draw each row.
>    while (dbCurs.next())
>    {
>        // Display row contents
>        for (nCol = 0; nCol < dbCurs.columns(); nCol++ )
>        {
>            // Display column value, unless == NULL
>            if (dbCurs[nCol] != null)
>                sHTML += "<TD height=24>" + dbCurs[nCol] + "</TD>\n";
>            else
>                sHTML += "<TD><br></TD>\n";
>        }  // for
>        sHTML += "</TR>\n";
>        write(sHTML);
>    } // while
>    sHTML  = "</TABLE></P>\n";
>    sHTML += "<INPUT TYPE=submit NAME=done VALUE='Done'>\n";
>    write(sHTML);
>}  // END genCursorTable()

>--
>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* )
>============================================================


>> I've seen this question posed a couple of times, but I can't quite get
>> the solutions to work.  I want to execute a stored procedure that
>> returns a result set back to Kiva (aka Netscape Application Server).
>> I'm fairly new to Oracle, having come from a Sybase shop.  In Sybase,
>> I was used to having a stored procedure return a result set from a
>> procedure simply by performing a select in the proc.  However, what
>> I've been reading so far on this newsgroup is that I have to do some
>> sort of fancy footwork with declaring a package and a cursor and what
>> not.  Is there no easier way?  And furthermore, it appears that the
>> return value for most of these solutions is a cursor, which I am not
>> so certain will work, but of this I'm uncertain.  Could someone
>> explain to me like a 5th grader how I should do this?

>> Thanks,
>> Ken



Fri, 16 Feb 2001 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. execute stored procedure for each record in a result set without cursor

2. Executing Stored procedures with multiple Result sets

3. Executing Stored procedures with multiple Result sets

4. Executing a Stored Procedure in a Stored Procedure and selecting on the result

5. returning result sets from stored procedures

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

7. return multiple result sets from a stored procedure

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

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

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

11. Returning a result set from a Stored Procedure

12. returning result sets from stored procedures


 
Powered by phpBB® Forum Software