1 2 Previous Next 16 Replies Latest reply: Mar 15, 2007 10:10 PM by nurhidayat RSS

    ODP.net 10.2.0.2

    nvtincher
      i must say that i am extremely pleased that ODP.net finally allows for custom types to be passed in through stored procedures. i've been able to successfully pass in parameters to stored procedures after converting them from .NET data types to custom Oracle types.

      i will admit that the documentation is a bit difficult to follow and there are really no "how-to" articles or very much sample code at this point but considering that it is only a beta release of the product, it's excusable.

      thank you!
        • 1. Re: ODP.net 10.2.0.2
          kakiyama
          Hi,

          For production, we are planning on having samples in the doc to demonstrate certain features. The beta does ship with some UDT samples that may be helpful to you.

          If there are specific topic/information which you feel are missing or confusing, please let us know.

          Thanks,

          Kiminari Akiyama
          Oracle
          • 2. Re: ODP.net 10.2.0.2
            502891
            When using a stored procedure to insert a row which contains a UDT column, the default constructor method does not appear to be initiated. For example, the following TYPE correctly converts the "TEXT" parameter to uppercase when called directly through PL/SQL, but leaves the parameter value lower case when called using ODP.NET 2. Any thoughts?

            CREATE OR REPLACE TYPE BODY TEST_TY AS

            -- --------------------------------------------------------------------------
            CONSTRUCTOR FUNCTION TEST_TY (
            -- --------------------------------------------------------------------------
            TEST_ID INTEGER
            , TEXT VARCHAR2
            ) RETURN SELF AS RESULT AS

            BEGIN

            SELF.TEST_ID := TEST_ID;
            SELF.TEXT := UPPER(TEXT);
            SELF.TEXT2 := 'CONSTRUCTOR_USED';

            RETURN;

            END;
            • 3. Re: ODP.net 10.2.0.2
              kakiyama
              Hi,

              I'm assuming that the stored procedure used remains the same whether it's executed by ODP.NET or by PL/SQL. If so, the only difference between the two would be the parameters that you are passing to it. When using the PL/SQL approach, do you expliclity call the TEST_TY constructor while it's not called with the ODP.NET approach? Perhaps more information on how the TEST_TY is constructed in the two apporaches may help (unless the instantiation of TEST_TY is done within the stored procedure itself, making the process the same for both PL/SQL & ODP.NET approaches).

              Kiminari Akiyama
              Oracle
              • 4. Re: ODP.net 10.2.0.2
                502891
                Below is a complete set of sample scripts, including the .NET class source code (excluding connection specifics). The end of this message shows the results of both the native PL/SQL insertion (SAMPLE_ID=1) as well as the ODP.NET results (SAMPLE_ID=2). I suspect that since ODP.NET calls the ToOracleObject method during execution to create the Oracle type, this is why the default CONSTRUCTOR method within that type is not called. I appreciate any feedback on this issue. :)


                -- --------------------------------------------------------------------------
                -- TYPE SPECIFICATION
                -- --------------------------------------------------------------------------
                CREATE TYPE SAMPLE_TY AS OBJECT (
                SAMPLE_ID INTEGER
                , SAMPLE_TEXT VARCHAR2(20)
                , SAMPLE_TEXT2 VARCHAR2(20)
                , PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS, RNDS, WNPS, RNPS)
                , CONSTRUCTOR FUNCTION SAMPLE_TY (
                SAMPLE_ID INTEGER
                , SAMPLE_TEXT VARCHAR2
                ) RETURN SELF AS RESULT
                )
                /

                -- --------------------------------------------------------------------------
                -- TYPE BODY
                -- --------------------------------------------------------------------------
                CREATE TYPE BODY SAMPLE_TY AS

                CONSTRUCTOR FUNCTION SAMPLE_TY (
                SAMPLE_ID INTEGER
                , SAMPLE_TEXT VARCHAR2
                ) RETURN SELF AS RESULT AS

                BEGIN
                SELF.SAMPLE_ID      := SAMPLE_ID;
                SELF.SAMPLE_TEXT      := UPPER(SAMPLE_TEXT);
                SELF.SAMPLE_TEXT2      := 'CONSTRUCTOR_USED';
                RETURN;
                END;

                END;
                /

                -- --------------------------------------------------------------------------
                -- TABLE DEFINITION
                -- --------------------------------------------------------------------------
                CREATE TABLE MY_TABLE(SAMPLE_COLUMN     SAMPLE_TY)
                /


                -- --------------------------------------------------------------------------
                -- STORED PROCEDURE - INSERT
                -- --------------------------------------------------------------------------
                CREATE PROCEDURE SAMPLE_INSERT_SP(
                p_sample_var          SAMPLE_TY) IS

                BEGIN

                -- Insert the record
                INSERT INTO MY_TABLE (SAMPLE_COLUMN) VALUES (p_sample_var);

                END;
                /

                -- --------------------------------------------------------------------------
                -- PL/SQL EXECUTION - INSERT
                -- --------------------------------------------------------------------------
                DECLARE
                v_sample_var          SAMPLE_TY;
                BEGIN

                -- Define the type attribute values
                v_sample_var := SAMPLE_TY(1, 'lowercase');

                -- Insert the record
                SAMPLE_INSERT_SP(v_sample_var);

                END;
                /

                -- --------------------------------------------------------------------------
                -- TYP_SAMPLE .NET Class Code
                -- --------------------------------------------------------------------------
                Imports Oracle.DataAccess.Client
                Imports Oracle.DataAccess.Types

                Public Class TYP_SAMPLE

                Implements ICustomOracleObject

                #Region "Variable Declarations"

                Private mSampleID As Integer
                Private mSampleText As String

                #End Region

                #Region "Class Properties"

                Public Property SampleID() As Int32
                Get
                Return mSampleID
                End Get
                Set(ByVal value As Int32)
                mSampleID = value
                End Set
                End Property

                Public Property SampleText() As String
                Get
                Return mSampleText
                End Get
                Set(ByVal value As String)
                mSampleText = value
                End Set
                End Property

                #End Region

                #Region "Constructor"

                Public Sub New()
                End Sub

                #End Region

                #Region "Oracle Type Conversion Methods"

                Public Sub FromOracleObject(ByVal oraObject As Oracle.DataAccess.Types.OracleObject) Implements Oracle.DataAccess.Types.ICustomOracleObject.FromOracleObject
                With oraObject
                If NotNullOrEmpty(.Item("SAMPLE_ID")) Then mSampleID = .Item("SAMPLE_ID")
                If NotNullOrEmpty(.Item("SAMPLE_TEXT")) Then mSampleText = .Item("SAMPLE_TEXT")
                End With
                End Sub

                Public Function ToOracleObject(ByVal con As Oracle.DataAccess.Client.OracleConnection) As Oracle.DataAccess.Types.OracleObject Implements Oracle.DataAccess.Types.ICustomOracleObject.ToOracleObject
                Dim obj As New OracleObject(OracleUdtDescriptor.GetOracleUdtDescriptor(con, "DBOWNER.SAMPLE_TY"))
                With obj
                If NotNullOrEmpty(mSampleID) Then .Item("SAMPLE_ID") = mSampleID
                If NotNullOrEmpty(mSampleText) Then .Item("SAMPLE_TEXT") = mSampleText
                End With
                Return obj
                End Function

                #End Region

                #Region "Oracle Post Method"

                Public Sub PostToDatabase(ByRef Database As HL7Database)
                Dim cmd As New OracleCommand("SAMPLE_INSERT_SP", Database.Connection)
                cmd.CommandType = CommandType.StoredProcedure

                'Define a new object of type TYP_SAMPLE (me)
                Dim sampleObject As New TYP_SAMPLE
                sampleObject.SampleID = 2
                sampleObject.SampleText = "lowercase"

                'Append the object to the parameters collection and execute
                cmd.Parameters.Add(New OracleParameter("P_SAMPLE_VALUE", OracleDbType.Object, sampleObject, ParameterDirection.Input))
                cmd.ExecuteNonQuery()
                cmd.Dispose()

                End Sub

                #End Region

                End Class


                -- --------------------------------------------------------------------------
                -- VIEW RESULTS OF INSERTS (1=NATIVE PL/SQL CALL, 2=ODP.NET)
                -- --------------------------------------------------------------------------
                SELECT
                mt.sample_column.SAMPLE_ID
                , mt.sample_column.SAMPLE_TEXT
                , mt.sample_column.SAMPLE_TEXT2
                from my_table mt
                /

                SAMPLE_COLUMN.SAMPLE_ID SAMPLE_COLUMN.SAMPLE SAMPLE_COLUMN.SAMPLE
                ----------------------- -------------------- --------------------
                1 LOWERCASE CONSTRUCTOR_USED
                2 lowercase
                • 5. Re: ODP.net 10.2.0.2
                  528831
                  Sir,
                  We had installed ODAC 10.2.0.2.20 by downloading from http://www.oracle.com/technology/software/tech/windows/odpnet/index.html.
                  We are using ODP.NET (Oracle.DataAccess.dll in 2.x folder) and visual studio 2005. But we are unable to use OracleDbType.Object .
                  (not containing object in OracleDbType.)

                  As in the example
                  cmd.Parameters.Add(New OracleParameter("P_SAMPLE_VALUE", OracleDbType.Object, sampleObject, ParameterDirection.Input))


                  Oracle.DataAccess.Types.OracleObject

                  So that we unable to use User defined oracle Types from .NET applications .

                  we had included the following
                  using Oracle.DataAccess.Client;
                  using Oracle.DataAccess.Types;

                  Please send us a reply regarding as early as possible.

                  Regards,
                  Aneesh Kumar P.
                  • 6. Re: ODP.net 10.2.0.2
                    kakiyama
                    Unfortunately, ODP.NET 10.2.0.2.20 does not support Oracle UDT/Objects.
                    • 7. Re: ODP.net 10.2.0.2
                      528831
                      Sir,
                      Which version of ODP.NET support Oracle UDT/Objects and from where it can be downloaded ?

                      Thank you for replying .

                      Regards,
                      Aneesh kumar P.
                      • 8. Version of ODP.NET support Oracle UDT/Objects
                        529286
                        Hi,

                        Which version of ODP.NET support Oracle UDT/Objects and from where it can be downloaded ?

                        Regards,
                        Dileep S.
                        • 9. Re: Version of ODP.NET support Oracle UDT/Objects
                          Alex.Keh .Product.Manager-Oracle
                          We plan to support UDTs/objects in a future release.

                          We received customer feedback about the UDTs/objects functionality in the beta, but were not able to address all the major issues by the time the ADO.NET 2.0 version of ODP.NET was planned to be released this summer. We are currently putting in these changes, but I don't have a date for release that I can share publicly at this time.

                          If you wish to be informed when UDTs/object will be available next in ODP.NET, send me an email alex.keh [at] oracle.com.
                          • 10. Re: ODP.net 10.2.0.2
                            529286
                            Sir,
                            We use beta version of ODP.Net ( Oracle.DataAccess.dll 2.102.2.10) and got the sample UDTSampleStoreProcedure.
                            But we are getting the error

                            OCI-22303: type "CDINEW"."cons_master" not found
                            when executing the con.UdtMappings.Add("CDINEW.cons_master", typeof(ConsMaster));

                            The type exist in the data base like
                            CREATE OR REPLACE Type Cons_Master as object
                            ( PatientId VARCHAr2(30),
                            KarteId Varchar2(14),
                            FacilityType char(2),
                            Revision VARCHAR2(14)
                            )

                            Please give a solution to this problem.

                            Regards,
                            Dileep S.
                            • 11. Re: ODP.net 10.2.0.2
                              425389
                              Try to use uppercase type names.

                              Message was edited by:
                              mrowca@tatry.pl
                              • 12. Re: ODP.net 10.2.0.2
                                529286
                                Sir,

                                It works fine Now .

                                Thanks for the reply.

                                Regards,
                                Dileep S.
                                • 13. Re: ODP.net 10.2.0.2
                                  529286
                                  Sir,
                                  Is it possible to use oracle Nested Types using ODP.NET .

                                  for example

                                  Create Type Cons_Master as object
                                  ( PatientId VARCHAr2(30),
                                  KarteId Varchar2(14),
                                  FacilityType char(2),
                                  Revision VARCHAR2(14)
                                  );

                                  create or replace type ConsList AS VARRAY(50) OF cons_master;


                                  OracleUdtDescriptor udtdes1=
                                  OracleUdtDescriptor.GetOracleUdtDescriptor(con, "CDINEW.CONSLIST");
                                  OracleObject obj 1= new OracleObject(udtdes1);

                                  OracleUdtDescriptor udtdes =
                                  OracleUdtDescriptor.GetOracleUdtDescriptor(con, "CDINEW.CONS_MASTER");
                                  OracleObject obj = new OracleObject(udtdes);

                                  And how each cons_Master obects be got from Obj1 .

                                  Expecting Your reply,
                                  Regards,

                                  Dileep S.
                                  • 14. Re: ODP.net 10.2.0.2
                                    gdarling - oracle
                                    Hi Dileep,

                                    It was possible in the previous beta, but UDT support has been removed in the latest production release of ODP. As such, it is not recommended that you use the beta version. I saw a hint by development that when UDT support is re-introduced, it may be in a different form, so any code you write now may not work in the future anyway.

                                    Cheers,
                                    Greg
                                    1 2 Previous Next