Here is a sample pulled from metalink:
This Visual Basic (VB) code shows how to insert a nested table
into a stored procedure and have the stored procedure update
the nested table. This was tested out with version 220.127.116.11.10
and will only work with 8.1.6.X version of Oracle Objects for OLE.
There are several steps that need to be taken inorder to
get this code to work.
1. The developer must have Visual Basic 6.0 Sevice Pack 3 (or higher)
installed on their development machine on Windows NT 4.0, Windows
98, or Windows 2000.
2. They must also have 8.1.6 client installed with Oracle Objects for
OLE version 18.104.22.168.10 or higher. This version can be downloaded
from metalink under the patches button.
3. Then the sql under the heading Scripts to be Run, must be run
into to create the object type, nested table, Pl/Sql Stored Procedure,
and insert some data into the nested table.
4. Then the developer can take the code under the heading Visual Basic
Code, and place it under the command1_click method of a commandbutton.
- Don't forget to include the Oracle InProc Server 3.0 Type Library
under the Project -> References in Visual Basic.
- Then you can run the code.
The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
Scripts to be Run:
create type myobject as object ( attr1 number, attr2 varchar2(5) );
create type mytable as table of myobject;
create table testtable
( col1 number,
col2 mytable )
nested table col2 store as col2_tab;
insert into testtable values ( 1, mytable( myobject(1, 'ONE'), myobject(2, 'TWO') ) );
insert into testtable values ( 2, mytable( myobject(3, 'THREE'), myobject(4, 'FOUR') ) );
select * from testtable;
create or replace procedure updatenested ( nestab IN mytable ) is
update testtable set col2 = nestab
where col1 = 1;
- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
Visual Basic Code:
Private Sub Command1_Click()
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraCollection As OraCollection
Dim OraObject As OraObject
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("v816", "scott/tiger", 0&)
Set OraCollection = OraDatabase.CreateOraObject("MYTABLE")
OraDatabase.Parameters.Add "NestedObj", OraCollection, ORAPARM_INPUT, _
Set OraObject = OraDatabase.CreateOraObject("MYOBJECT")
OraObject.attr1 = 12
OraObject.attr2 = "Jill"
OraCollection(1) = OraObject
OraDatabase.Parameters("NestedObj").Value = OraCollection
OraDatabase.ExecuteSQL ("Begin updatenested(:NestedObj); end;")
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -
In sqlplus cut and paste the following command:
select * from testtable;
This is the sample output that you should see after running this code:
SQL> select * from testtable;
MYTABLE(MYOBJECT(3, 'THREE'), MYOBJECT(4, 'FOUR'))