FUNCTION SPCONSTREETS return SYS_REFCURSOR is l_cursor SYS_REFCURSOR; begin open l_cursor for select IDSTREET, STREETNAME, IDDISTRICT from DBO.STREETS; return l_cursor; end;
This function runs fine in Oracle. I wrote the following code in Visual Basic 6:
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cn.ConnectionString = "Provider=MSDAORA.1;Password=datos2004;User ID=dbo;Data Source=servidor;Persist Security Info=True"
cn.Open
cmd.CommandText = "SPCONSTREETS"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn
Set rs = cmd.Execute
When I run this, the following message appears at cmd.execute momment:
ORA-06550: Line 1 Column 7 PLS-00306 wrong number or types of arguments in call SPCONSTREETS
Any idea?
Requires Free Membership to View
You're not asking for the resultset correctly.
Here's an example to follow (Click here for this and more examples):
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 This was first published in November 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation