Re: Getting result sets from stored procedures

From: Hugh Pendry <hpendry_at_trinitech.co.uk>
Date: Mon, 16 Aug 1999 11:12:12 +0100
Message-ID: <7p8o5s$j87$1@trinitech.demon.co.uk>

I had a similar query and was posted this:-

Oracle Sample Code Repository Entry# 581

---

---

Disclaimer:
This sample is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The sample has been tested and appears to work as intended. However, you should always test in YOUR environment before relying on it.

---

---

Name: ODBC ResultSets from a stored procedure Category: ODBC
Port: WIN32
Description: Sample (from the Oracle ODBC Driver 8.0.4.0.3 Help File) modified to work better with MSVC 5.0. This returns 2 refcursors from a stored procedure and fetches upon them with the C ODBC API. Requires Oracle ODBC Driver 8.0.4.0.3 or higher, but is not supported until the 8.0.5 release for the ODBC driver and the RDBMS due to underlying OCI and RDBMS issues.

---

---

/*
* Sample Application using Oracle reference cursors via ODBC
*

• Assumptions:
  *
  
• 1) Oracle Sample databases is present with data loaded for the EMP table.
  *
  
• 2) Two fields are referenced from the EMP table ename and mgr.
  *
  
• 3) A data source has been setup to access the sample database.
  *
  *
  
• Program Description:
  *
  
• Abstract:
  *
  
• This program demonstrates how to call result sets using
• Oracle store procedures
  *
  
• Details:
  *
  
• This program Creates and ODBC Connection to the database.
• Creates a Packaged Procedure containing two result sets.
• Executes the procedure it just created, and then retrieves
• the data from both result sets. The data is displayed to
• the user. The package is then delete and the user is
• logged out of the database.
  *
  *
  
• The following is the actual PL/SQL this code generates to
• create the store procedures.
  *
  

DROP PACKAGE ODBCRefCur;
CREATE PACKAGE ODBCRefCur AS
TYPE ename_cur IS REF CURSOR;
TYPE mgr_cur IS REF CURSOR;

PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2);
END;
/

CREATE PACKAGE BODY ODBCRefCur AS
PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2)
    AS

      BEGIN
         IF NOT Ename%ISOPEN
         THEN
             OPEN Ename for SELECT ename from emp;
         END IF;
         IF NOT Mgr%ISOPEN
         THEN
             OPEN Mgr for SELECT mgr from emp where job = pjob;
         END IF;
      END;

END;
/

*

• End PL/SQL for Reference Cursor.
  */
  

/*
* Include Files
*/

#include <windows.h>

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

/*

• Defines

*/

#define JOB_LEN 9

#define DATA_LEN 100

#define SQL_STMT_LEN 500

/*

• Procedures

*/

void DisplayError( SWORD HandleType, SQLHANDLE hHandle, SQLCHAR
*Module );

/*

• Main Program

*/

int main()

{

    SQLHENV hEnv;

    SQLHDBC hDbc;

    SQLHSTMT hStmt;

    SQLRETURN rc;

    SQLCHAR *DefUserName = (SQLCHAR*) "scott";

    SQLCHAR *DefPassWord = (SQLCHAR*) "tiger";

    SQLCHAR ServerName[DATA_LEN];

    SQLCHAR *pServerName=ServerName;

    SQLCHAR UserName[DATA_LEN];

    SQLCHAR *pUserName=UserName;

    SQLCHAR PassWord[DATA_LEN];

    SQLCHAR *pPassWord=PassWord;

    SQLCHAR Data[DATA_LEN];

    SQLINTEGER DataLen;

    SQLCHAR error[DATA_LEN];

    SQLCHAR *charptr;

    SQLCHAR SqlStmt[SQL_STMT_LEN];

    SQLCHAR *pSqlStmt=SqlStmt;

    SQLCHAR *pSalesMan = (SQLCHAR*) "SALESMAN";

    SQLINTEGER sqlnts=SQL_NTS;

    /*

• Allocate the Environment Handle

     */

    rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );

    if (rc != SQL_SUCCESS)

    {

      printf( "Cannot Allocate Environment  Handle\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ( (char*) &error);

      exit(1);

    }

    /*

• Set the ODBC Version

     */

    rc = SQLSetEnvAttr( hEnv,

                        SQL_ATTR_ODBC_VERSION,

                        (void *)SQL_OV_ODBC3,

                        0);

    if (rc != SQL_SUCCESS)

      printf( "Cannot Set ODBC Version\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

      exit(1);

    }

    /*

• Allocate the Connection handle

     */

    rc = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );

    if (rc != SQL_SUCCESS)

      printf( "Cannot Allocate Connection Handle\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

      exit(1);

    }

    /*

• Get User Information

     */

    lstrcpy( (char *) pUserName, (char * )DefUserName );

    lstrcpy( (char *) pPassWord, (char * )DefPassWord );

    /*

• Data Source name

     */

    printf( "\nEnter the ODBC Data Source Name\n" );

      charptr = (SQLCHAR*) gets ((char*) ServerName);

    /*

• User Name

     */

    printf ( "\nEnter User Name Default [%s]\n", pUserName);

      charptr = (SQLCHAR*) gets ((char*) error);

    if (*charptr == '\0')

      lstrcpy( (char*) pUserName, (char *) DefUserName );

    }

    /*

• Password

     */

    printf ( "\nEnter Password Default [%s]\n", pPassWord);

      charptr = (SQLCHAR*) gets ((char*) error);

    if (*charptr == '\0')

      lstrcpy( (char *) pPassWord, (char *) DefPassWord );

    }

    /*

• Connection to the database

     */

    rc = SQLConnect( hDbc,

                     pServerName,

                     (SQLSMALLINT) lstrlen((char *) pServerName),

                     pUserName,

                     (SQLSMALLINT) lstrlen((char *) pUserName),

                     pPassWord,

                     (SQLSMALLINT) lstrlen((char * )pPassWord));

    if (rc != SQL_SUCCESS)

      DisplayError(SQL_HANDLE_DBC, hDbc, (SQLCHAR *) "SQLConnect");

    }

    /*

• Allocate a Statement

     */

    rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );

    if (rc != SQL_SUCCESS)

      printf( "Cannot Allocate Statement Handle\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

      exit(1);

    }

    /*

• Drop the Package

     */

    lstrcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    /*

• Create the Package Header

     */

    lstrcpy( (char * )pSqlStmt, "CREATE PACKAGE ODBCRefCur AS\n");
    lstrcat( (char *) pSqlStmt, "  TYPE ename_cur IS REF CURSOR;\n");
    lstrcat( (char *) pSqlStmt, "  TYPE  mgr_cur  IS REF

CURSOR;\n\n");

    lstrcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");

    lstrcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur, pjob IN VARCHAR2);\n\n");

    lstrcat( (char *) pSqlStmt, "END;\n");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    if (rc != SQL_SUCCESS)

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLExecDirect");

    }

    /*

• Create the Package Body

     */

    lstrcpy( (char *) pSqlStmt, "CREATE PACKAGE BODY ODBCRefCur AS\n");

    lstrcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");

    lstrcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur, pjob IN VARCHAR2)\n AS\n BEGIN\n");

    lstrcat( (char *) pSqlStmt, " IF NOT Ename%ISOPEN\n THEN\n");

    lstrcat( (char *) pSqlStmt, " OPEN Ename for SELECT ename from emp;\n");

    lstrcat( (char *) pSqlStmt, "      END IF;\n\n");
    lstrcat( (char *) pSqlStmt, "      IF NOT Mgr%ISOPEN\n
THEN\n");
    lstrcat( (char *) pSqlStmt, "          OPEN Mgr for SELECT mgr
from emp where job = pjob;\n");
    lstrcat( (char *) pSqlStmt, "      END IF;\n");

    lstrcat( (char *) pSqlStmt, " END;\n");     lstrcat( (char *) pSqlStmt, "END;\n");

printf((char *) pSqlStmt);

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    if (rc != SQL_SUCCESS)

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLExecDirect");

    }

    /*

• Bind the Non-Result Set Parameter

     */

    rc = SQLBindParameter(hStmt,

                          3,

                          SQL_PARAM_INPUT,

                          SQL_C_CHAR,

                          SQL_CHAR,

                          JOB_LEN,

                          0,

                          pSalesMan,

                          0,

                          &sqlnts);

    /*

• Call the Store Procedure which executes the Result Sets

     */

    lstrcpy( (char *) pSqlStmt, "{CALL ODBCRefCur.EmpCurs(?,?,?)}");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    if (rc != SQL_SUCCESS)

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLExecDirect");

    }

    /*

• Bind the Data

     */

    rc = SQLBindCol( hStmt,

                     1,

                     SQL_C_CHAR,

                     Data,

                     sizeof(Data),

                     &DataLen);

    if (rc != SQL_SUCCESS)

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLBindCol");

    }

    /*

• Get the data for Result Set 1

     */

    printf( "\nEmployee Names\n\n");

    while ( rc == SQL_SUCCESS )

      rc = SQLFetch( hStmt );

      if ( rc == SQL_SUCCESS )

        printf("%s\n", Data);

      }

      else

        if (rc != SQL_NO_DATA)

          DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLFetch");

        }

      }

    }

    printf( "\nFirst Result Set - Hit Return to Continue\n");

      charptr = (SQLCHAR*) gets ((char*) error);

    /*

• Get the Next Result Set

     */

    rc = SQLMoreResults( hStmt );

    if (rc != SQL_SUCCESS)

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLMoreResults");

    }

    /*

• Get the data for Result Set 2

     */

    printf( "\nManagers\n\n");

    while ( rc == SQL_SUCCESS )

      rc = SQLFetch( hStmt );

      if ( rc == SQL_SUCCESS )

        printf("%s\n", Data);

      }

      else

        if (rc != SQL_NO_DATA)

          DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLFetch");

        }

      }

    }

    printf( "\nSecond Result Set - Hit Return to Continue\n");

      charptr = (SQLCHAR*) gets ((char*) error);

    /*

• Should Be No More Results Sets

     */

    rc = SQLMoreResults( hStmt );

    if (rc != SQL_NO_DATA)

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLMoreResults");

    }

    /*

• Drop the Package

     */

    lstrcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    /*

• Free handles close connections to the database

     */

    SQLFreeHandle( SQL_HANDLE_STMT, hStmt );

    SQLDisconnect( hDbc );

    SQLFreeHandle( SQL_HANDLE_DBC, hDbc );

    SQLFreeHandle( SQL_HANDLE_ENV, hEnv );

    printf( "\nAll Done - Hit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

    return(0);

}

/*

• Display Error Messages

*/

void DisplayError( SWORD HandleType, SQLHANDLE hHandle, SQLCHAR
*Module )

{

    SQLCHAR MessageText[255];

    SQLCHAR SQLState[80];

    SQLRETURN rc=SQL_SUCCESS;

    LONG NativeError;

    SWORD RetLen;

    SQLCHAR error[25];

    SQLCHAR *charptr;

    rc = SQLGetDiagRec(HandleType,

                       hHandle,  

                       1, 

                       SQLState, 

                       &NativeError, 

                       MessageText, 

                       255, 

                       &RetLen); 

    printf( "Failure Calling %s\n", Module );     if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)     {

      printf( "\t\t\t         State: %s\n", SQLState); 
      printf( "\t\t\t  Native Error: %d\n", NativeError ); 
      printf( "\t\t\t Error Message: %s\n", MessageText ); 

    }

    printf( "\nHit Return to Exit\n");     charptr = (SQLCHAR*) gets ((char*) error);     exit(1);

}

Bob Kline wrote in message <7p4ijb$lat_at_nnrp1.farm.idt.net>...

>In the Sybase world there is very elegant support for returning a result set
>from a stored procedure.  Any select statement in the procedure is available
>to the client as a result set, which is very easy to grab from ODBC or
>Perl's DBI.  The closest I've been able to find in the Oracle world is the
>REF CURSOR, but it appears that the REF CURSOR needs to be set up as one of
>the parameters for the stored procedure.  I haven't been able to find any
>way to call such a stored procedure from ODBC or DBI and get to the result
>set.  Anyone have any tips on where to look?
>
>Thanks,
>Bob Kline
>bkline_at_rksystems.com
>
>
>

Received on Mon Aug 16 1999 - 05:12:12 CDT