I am using Oracle's OLEDB Provider to connect to Oracle 8i database
from VB6(ADO - MDAC 2.6,SP1). Can I recieve Table type arrays from
Oracle Stored procedures to VB back. If yes, how?

Note: Table types can be received back to VB from Oracle when using
Microsoft's OLE DB Provider - MSDASQL and Microsoft's ODBC Driver.

Regards,
Ashok

--
hth
Hirantha
Please reply only to the newsgroups.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

hi Hirantha,

Thanks a lot for the prompt reply.
The links you have mentioned do not describe the provider I am using --
Oracle OLE DB Provider(OraOLEDB.Oracle).
These links instead refer to Micrsoft's OLE DB Provider and Microsoft'
ODBC Driver.

I specifically require the solution for the above specified provider.!

Regards,
Ashok

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Oracle can return a cursor via a stored procedure, and that cursor is
presented as a recordset.  The first step is to create a package - because
you will need to decalre a user type as ref cursor, then declare the
procedure as returning an argument of the user type (i.e. as ref cursor).
the next step is to create the procedure - and in that procedure, you will
open a cursor using the ref cursor parameter.  Consider the following:

-- ------------------------
Create Package Package MyPackage as
Type MyCursor as Ref Cursor;
Procedure GetData(p_cursor Out MyCursor, id In Number);
End MyPackage;
-- ------------------------
Create Package Package Body MyPackage as
Procedure GetData(p_cursor Out MyCursor, id In Number) is
  Begin
  Open p_cursor For Select * from MyTable where cid=id;
  End GetData;
End MyPackage;
-- ------------------------

Then the corresponding ADO code would look something like

Dim cnxion as ADODB.Connection
Dim cmd as ADODB.Command
Dim rset as ADODB.Recordset
...
cnxion.Open etc, etc, etc
...
cmd.ActiveConnection = cnxion
cmd.Properties("PLSQLRSet") = True
cmd.CommandText = "{Call MyPackage.GetData(2)}"
rset = cmd.Execute
cmd.Properties("PLSQLRSet") = False

If you have Oracle 8i or Oracle 9i client installed, have a look at the
OraOleDB Programmers guide that is installed on the client machine.  There
are several pretty good examples included there.

HOWEVER--
In your post, you are asking if Table type arrays can be returned -- and the
answer is NO.  The reason is that the returned data must be of some
recognized type, and PL/SQL does not understand traditional arrays, and
VB/OLEDB/ADO do not understand Table arrays.  Consider the following:
-- ------------------------
Procedure test is
type t_mytable is Table of Varchar2(30);
type myCursor is Ref Cursor;
v_myTable t_mytable;

begin
open myCursor for select * from v_myTable;
end;
-- ------------------------
The above will not compile, because the cursor open statement MUST have a
valid SQL statement - specifically a select statement, and the From clause
must reference a real table or view - and the Table array is not recognized
as such.

best regards,
Roy Fine

In our project, we have my code where Stored Procedures return Table
Types. And I am using Microsoft's Ole DB Provider (MSDASQL) and
Microsoft's ODBC Driver to connect to Oracle from VB(ADO). and this
works fine.

I am trying out the OraOleDb.Oracle Provider as this is the fastest way
to connect to Oracle from VB(ADO). But we are failing to send back the
Table Types!!

Any pointers towards this will be of great help.!!

Regards,
Ashok

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The short answer is No, you can't return the  Table types. Recall that the
table type is an array of some type, and that it is index via a binary key.
It is not a classical table type that a cursor can operate on, hence
exposing it as a Rowset (or an ADO Recordset) is not defined.

I think you do have a couple of options -- either

1) write your own OLEDB Provider that implements this special functionality
(or purchase a 3rd party solution), or

2) you can insert the data into an application specific table with an access
key that is unique in time and space, and return that key to the
application -- this is a messy implementation, but works.

regards
Roy Fine