Using PL/SQL REF Cursors in VB.

[sujark]

Hello all,

I am trying to execute an Oracle Stored Procedure from VB 6.0.

My Oracle Stored Procedure looks like follows:

Procedure TEST_SQL
(
RETCursor IN OUT VES_PROCS.PKG_DYNAMIC.cv_type,
Cmpny_id varchar2,
Vndr_no varchar2)
IS
BEGIN

Open retcursor for select * from VES.Vendor_Attributes
where cmpny_id = 'S'
order by vndr_no;

END;

Please note that VES_PROCS is the schema name, PKG_DYNAMIC is the Package name. 'RETcursor' is a ref cursor created in this Package as follows:

Create Package PKG_DYNAMIC
type cv_type is ref cursor
end PKG_DYNAMIC;

I call the above stored Procedure from VB as follows:

Private Sub Command1_click()

Dim p_strConnString As String
Dim p_rs As ADODB.Recordset
Dim p_conConn As ADODB.Connection
Dim p_cmdCmd As ADODB.Command
Dim p_prmPrmtr As ADODB.Parameter
Dim strsql As String

p_strConnString="Provider=MSDAORA.1;Password='VES_PROCS';USER ID='VES_PROCS';Data Source='MISTDEV';Persist Security Info=True"

Set p_conConn = New ADODB.Connection
Set p_cmdCmd = New ADODB.Command
Set p_prmPrmtr = New ADODB.Parameter
Set p_rs = New ADODB.Recordset

p_conConn.ConnectionString = p_strConnString
p_conConn.CursorLocation = adUseClient
p_conConn.Open

strsql = "DYNAMICSQL('S' , 'Y') "

With p_rs
.ActiveConnection = p_conConn
.CursorLocation = adOpenStatic
.CursorType = adOpenStatic
.Open strsql
.ActiveConnection = Nothing
End With

End Sub

For some reason the above Code doesn't work. While executing the Stored Procedure, VB returns an error code:

ORA-00900: Invalid SQL Statement.

Can I use a PL/SQL ref cursor like I have used above? Though my Stored Procedure has the ref cursor set up as an IN OUT parameter, I don't pass that from VB when calling the stored procedure? Is that my problem? If I need to pass that too, how do I do that?

Please help.

Thanks in advance.