What Changes Are Needed For This Stored Proc to Work in Oracle?

From: dhmac <dhmac_at_JUSTREMOVETHISmy-dejanews.com>
Date: Wed, 28 Apr 1999 22:57:17 -0400
Message-ID: <7g8hp5$ea6$1@nntp4.atl.mindspring.net>

We're converting a project from Sybase to Oracle which, of course, means the frequently mentioned problems with resultsets and using ODBC. Below is an example of the code I am having trouble with:

On the Oracle 8.0.5.1 RDBMS Server side, we have a package defined as this:

---

PACKAGE TYPES
IS

    type cursorType is ref cursor;
END;

---

And we have Stored Procedures (actually Functions) like this one:

---

CREATE OR REPLACE
FUNCTION GET_EMP_DATA (partial_name EMPLOYEE.name%type) RETURN types.cursorType
IS

    l_cursor types.cursorType;

BEGIN     OPEN l_cursor FOR

        SELECT  emp_nbr, name, dept
        FROM    EMPLOYEE
        WHERE   name like partial_name;

    RETURN l_cursor;

END; /*

---

       ORIGINAL SYBASE CODE

---

CREATE PROC GET_EMP_DATA @partial_name char(40) AS

        SELECT  emp_nbr, name, dept
        FROM    EMPLOYEE
        WHERE   name like @partial_name
***********************************************

*/

---

And on the client side, we are calling this function using standard ODBC API calls (below I use pseudo ODBC API calls in the example to make it more readable):

---

     char szSQLStmt[100] = "";
     char szReturnVal[250] = "";

     sprintf( szSQLStmt, "{?=call GET_EMP_DATA ('%s')}", szNameIn );
                                      // Old Sybase Call Syntax: "exec

GET_EMP_DATA '%s'"

     // Bind return variable (the "?" placeholder)
     SQLBindParameter( 1, szReturnVal, sizeof(szReturnVal),
SQL_PARAM_INPUT_OUTPUT );
                                     // Added for Oracle (wasn't needed for

Sybase)

     // Bind output values
     SQLBindCol( 1, szEmpNbrOut  );
     SQLBindCol( 2, szEmpNameOut , sizeof(szEmpNameOut) );
     SQLBindCol( 3, szEmpDeptOut );

     if ( SQLExecDirect( szSQLStmt ) != SQL_SUCCESS )
     {
         OutputErrorMsg();
     }

     while ( SQLFetch() != SQL_NO_DATA_FOUND )
     {
        // Fetch each row and put in display grid
        Grid.AddRow( szEmpNbrOut, szEmpNameOut, szEmpDeptOut );
     }




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

Running this code returns the following error when the SQLExecDirect call is made:

     SQLState: S1000
Native Error#: 6550

[Oracle][ODBC][Ora]
ORA-06550: line 1, column 14: PLS-00382: expression is of wrong type

ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I know the error is with the SQLBindParameter call, but I don't know what I need to do here. What type of variable should I be binding to my return parameter for the function call? Or should I even be using a different ODBC function here?

Also, I have some other questions:

1. We are using Oracle 7.3 drivers for both SQL*Net (v2.3.4.0.0) and ODBC (Oracle73 Ver 2.5) - can these drivers handle resultsets? If not, what version of the drivers can?
2. Does the converted Sybase Procedure have to be an Oracle Function in order to use a REF cursor or can this be done with an Oracle Procedure instead?

Thanks
in
advance Received on Wed Apr 28 1999 - 21:57:17 CDT