5 Replies Latest reply: Jun 4, 2003 9:12 AM by 395991 RSS

    table object as IN parameter for a stored procedure

    395991
      Can somebody, please, tell me how to pass an oracle table object as IN parameter for a stored procedure in Oracle 9i from VB using OO4O...thanks, in advance.
        • 1. Re: table object as IN parameter for a stored procedure
          82532
          Do you mean a nested table type?
          • 2. Re: table object as IN parameter for a stored procedure
            395991
            Dear Mark..you are right...can you please tell me how to do it..thanks..prem.
            • 3. Re: table object as IN parameter for a stored procedure
              82532
              Here is a sample pulled from metalink:

              Overview
              --------
              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 8.1.6.3.10
              and will only work with 8.1.6.X version of Oracle Objects for OLE.

              Program Notes
              -------------
              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 8.1.6.3.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.

              Caution
              -------
              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
              on it.


              Program
              -------

              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') ) );

              commit;

              select * from testtable;

              create or replace procedure updatenested ( nestab IN mytable ) is
              begin
              update testtable set col2 = nestab
              where col1 = 1;
              end;
              /
              Commit;
              /
              - - - - - - - - - - - - - - - - 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, _
              ORATYPE_TABLE, "MYTABLE"
              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;")
              End Sub

              - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -


              Sample Output
              -------------
              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;

              COL1
              ----------
              COL2(ATTR1, ATTR2)
              -------------------------------------------------------
              1
              MYTABLE(MYOBJECT(12, 'Jill'))

              2
              MYTABLE(MYOBJECT(3, 'THREE'), MYOBJECT(4, 'FOUR'))


              • 4. Re: table object as IN parameter for a stored procedure
                395991
                thanks a lot, Mark...i shall try this out and get back to you with the outcome...regards..prem
                • 5. Re: table object as IN parameter for a stored procedure
                  395991
                  dear mark..we tried and it worked..thanks a lot for the timely help..thanks for all the replies..prem.