Oracle and ADO Query from Excel returns no results

Question

I am having trouble running a query in Excel 2010 VBA code using Oracle OraOLEDB.Oracle Provider.

Certain queries work fine and return results, while others return no results...

I connect as such:

 Set DBConnection = New ADODB.Connection
    DBConnection.Provider = "OraOLEDB.Oracle"
    DBConnection.CursorLocation = adUseClient
    DBConnection.ConnectionString = "Data Source=" & TNSName & ";User Id=" & OraUserName & ";Password=" & OraPassWord & ";"
    DBConnection.Open

I then try to query:

command2.ActiveConnection = DBConnection
command2.CommandText = "SELECT COL1,COL2,COL3 FROM table(MySchema.MyPackage.MyFunction('Param1'))"
command2.CommandType = adCmdText

Set QueryRecordSet = New ADODB.Recordset
QueryRecordSet.LockType = adLockReadOnly
QueryRecordSet.CursorType = adOpenDynamic
QueryRecordSet.Open command2


command2.Execute

and I get nothing...any ideas?

If I run a simple query like

select * From my_table

it works fine...it seems joins or other more complex queries don't compile??

Additionally, selecting from views does not work.

select * from my_view

Returns nothing

Answer 1

I'm putting this as an answer only because comment formatting doesn't allow me to add code.

Does the stored procedure work if you run it separately via the command object?

command2.CommandText = "MySchema.MyPackage.MyFunction"
command2.CommandType = adCmdStoredProc
command2.Parameters.Refresh
command2.Parameters.Item(1).Value = "Param1"
command2.Execute
Debug.Print command2.Parameters.Item(0).Value