Ask the Expert

Help with ORA-06550 error

I need to translate my store from SQL Server to Oracle. I wrote the following function:
   l_cursor SYS_REFCURSOR;
   open l_cursor for select IDSTREET, STREETNAME, IDDISTRICT from DBO.STREETS;
   return l_cursor;

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"
    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); 
'CREATE OR REPLACE package body reftest as 
'   Procedure GetEmpData 
'(en in varchar2,EmpCursor in out empCur) is 
' open EmpCursor for select ename from emp where ename LIKE en; 
     Dim cn As New rdoConnection 
     Dim qd As rdoQuery 
     Dim rs As rdoResultset 
     Dim cl As rdoColumn 
     Static Number As Integer 

     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 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 
                    List1.AddItem cl.Value 
                    ' Debug.Print " "; cl.Name; " "; cl.Value; 
                End If 
     Loop While rs.MoreResults 

End Sub

This was first published in November 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: