Q

Help with ORA-06550 error

I need to translate my store from SQL Server to Oracle. I wrote the following function:

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?
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

Dig deeper on Oracle error messages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close