| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: What Changes Are Needed For This Stored Proc to Work in Oracle?
A copy of this was sent to "dhmac" <dhmac_at_JUSTREMOVETHISmy-dejanews.com>
(if that email address didn't require changing)
On Wed, 28 Apr 1999 22:57:17 -0400, you wrote:
>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
>***********************************************
>*/
>
>
>
>-----------------------------------------------------------
>
>
[code snipped]
>
>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?
>
no, see below.
>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?
>
it can be a procedure, see below
>
>
>Thanks
>in
>advance
>
The following is thanks to marktoml_at_hotmail.com (mark tomlinson)..
If you use ODBC here is a working example, but it requires the use of the 8.0.5.2.0 or later Oracle ODBC driver, and an 8.0.5 server.
'
' 1) Create a form with 1 Text control (Text1) and 1 List Control
(List1) and
' 1 Button (btnExecute).
' 2) The only code that you need is a Click method on your button.
Here is the Code.
'
'
Private Sub btnExecute_Click()
'PL/SQL Code
'===========
'
'CREATE OR REPLACE package reftest as
' cursor c1 is select ename from emp;
' type empCur is ref cursor return c1%ROWTYPE;
' Procedure GetEmpData(en in varchar2,EmpCursor in out empCur);
'END;
'
'
'CREATE OR REPLACE package body reftest as
' Procedure GetEmpData
'(en in varchar2,EmpCursor in out empCur) is
'begin
' open EmpCursor for select ename from emp where ename LIKE en;
'end;
'end;
'
Dim cn As New rdoConnection
Dim qd As rdoQuery
Dim rs As rdoResultset
Dim cl As rdoColumn
Static Number As Integer
List1.Clear
Number = 0
cn.Connect = "uid=scott; pwd=tiger; DSN=MSLANGORL;"
'enable the MS Cursor library
cn.CursorDriver = rdUseOdbc
'Make the connection
cn.EstablishConnection rdNoDriverPrompt
sSQL = "{call RefTest.GetEmpData(?,?)}"
Set qd = cn.CreateQuery("", sSQL)
qd.rdoParameters(0).Type = rdTypeVARCHAR
qd(0).Direction = rdParamInputOutput
qd(0).Value = Text1.Text
qd.rdoParameters(1).Type = rdTypeVARCHAR
'Dynamic or Keyset is meaningless here
Set rs = qd.OpenResultset(rdOpenStatic)
Do
Debug.Print
Debug.Print
Do Until rs.EOF
For Each cl In rs.rdoColumns
If IsNull(cl.Value) Then
List1.AddItem "(null)"
' Debug.Print " "; cl.Name; "NULL"; Error trap for
null fields
Else
List1.AddItem cl.Value
' Debug.Print " "; cl.Name; " "; cl.Value;
End If
Next
Debug.Print
rs.MoveNext
Loop
Loop While rs.MoreResults
cn.Close
End Sub
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |