3 Replies Latest reply on Dec 24, 2017 9:10 AM by DurgadasHaldar

    Using 12c DB adapter to read from Oracle 9i database

    Jithesh Shetty

      Hi Guys,

       

      Does anyone know of a workaround to read from Oracle 9i database using DB adapter version 12.2.1.2.0?

      I want to use only the "select" operation to read from a single table.

       

      I know that as per certification matrix 9i is not supported by 12c. And upgrading the target DB is not a feasible option at the moment.

      Just wanted to know if there is way to get around the limitations and make this work?

       

      Thanks

      Jithesh

        • 1. Re: Using 12c DB adapter to read from Oracle 9i database
          Srinu1477

          Hello Jithesh,

           

          Good Day!!!

           

          Before going to comment on this, would like to know couple of queries.

           

          1. Did you try calling 9i database from 12c JCA adapter. If yes, pls provide the error you are getting.

          2. As I know, certificate matrix tells only SOA components installation supported DB version, not JCA DB adapter DB version. Experts can correct me on this.

           

          Thanks,

          Sreeni

          • 2. Re: Using 12c DB adapter to read from Oracle 9i database
            Jithesh Shetty

            Hi Srinu,

             

            Thanks for your reply.

            You might be right about point no 2.

             

            Below is the error seen when I query for the 9i db table thought the JCA adapter. Please let me know your thoughts on this.

             

             

            ORA-01460: unimplemented or unreasonable conversion requested

             

            SELECT /*OracleDictionaryQueries.ALL_ORACLE_OBJECT_QUERY(3)*/

            O.OBJECT_NAME, O.OBJECT_TYPE, O.OBJECT_ID, O.LAST_DDL_TIME

            FROM SYS.ALL_OBJECTS O

            WHERE O.OWNER = ?

            AND O.OBJECT_NAME LIKE ?

            AND O.OBJECT_TYPE IN (?, ?, ?)

            AND O.SUBOBJECT_NAME IS NULL

            AND O.SECONDARY = 'N'

            AND ( O.OBJECT_TYPE <> 'INDEX' OR

            (

            EXISTS (SELECT 1

            FROM   SYS.ALL_INDEXES I

            WHERE  I.OWNER = O.OWNER

            AND    I.INDEX_NAME = O.OBJECT_NAME

            --10g AND    I.DROPPED = 'NO'

            )

            )

            )

            AND ( O.OBJECT_TYPE <> 'TRIGGER' OR

            (

            EXISTS (SELECT 1

            FROM   SYS.ALL_TRIGGERS TR

            WHERE  TR.OWNER = O.OWNER

            AND    TR.TRIGGER_NAME = O.OBJECT_NAME

            AND   (   TR.BASE_OBJECT_TYPE <> 'TABLE'

            OR EXISTS ( SELECT 1 FROM SYS.ALL_TABLES T2

            WHERE  T2.OWNER = TR.TABLE_OWNER

            AND    T2.TABLE_NAME = TR.TABLE_NAME

            --10g AND T2.DROPPED = 'NO'

            )

            )

            )

            )

            )

            AND ( O.OBJECT_TYPE <> 'TYPE' OR

            EXISTS (SELECT 1

            FROM   SYS.ALL_TYPES T

            WHERE  T.OWNER = O.OWNER

            AND    T.TYPE_NAME = O.OBJECT_NAME)

            )

            AND ( O.OBJECT_NAME NOT LIKE 'AQ$%' OR

                     NOT EXISTS

            ( SELECT 1

            FROM   SYS.ALL_QUEUE_TABLES QT

            WHERE  O.OWNER = QT.OWNER

            AND    (   O.OBJECT_NAME = 'AQ$'||QT.queue_table

                               OR O.OBJECT_NAME like 'AQ$'||QT.queue_table||'^_%' escape '^'

            OR O.OBJECT_NAME like 'AQ$^_'||QT.queue_table||'^_%' escape '^')

                      )

            )

            UNION ALL

            SELECT /*OracleDictionaryQueries.ALL_TABLE_ORACLE_QUERY*/

            X.TABLE_NAME

            , 'TABLE' OBJECT_TYPE

            , (SELECT O.OBJECT_ID

            FROM   SYS.ALL_OBJECTS O

            WHERE  O.OWNER = X.OWNER

            AND O.OBJECT_TYPE = 'TABLE'

            AND O.OBJECT_NAME = X.TABLE_NAME) OBJECT_ID

            , NULL LAST_DDL_TIME

            FROM (

            SELECT T.TABLE_NAME, T.OWNER

            FROM SYS.ALL_TABLES T

            WHERE T.OWNER = ?

            AND T.NESTED = 'NO'

            AND T.TABLE_NAME LIKE ?

            AND T.IOT_NAME IS NULL

            AND T.SECONDARY = 'N'

            --10g AND    T.DROPPED = 'NO'

            MINUS

            SELECT QT.QUEUE_TABLE, QT.OWNER

            FROM SYS.ALL_QUEUE_TABLES QT

            WHERE QT.OWNER = ?

            AND QT.QUEUE_TABLE LIKE ?

            MINUS

            SELECT MV.MVIEW_NAME, MV.OWNER

            FROM SYS.ALL_MVIEWS MV

            WHERE MV.OWNER = ?

            AND MV.MVIEW_NAME LIKE ?

            MINUS

            SELECT MVL.LOG_TABLE, MVL.LOG_OWNER

            FROM SYS.ALL_SNAPSHOT_LOGS MVL

            WHERE MVL.LOG_OWNER = ?

            AND MVL.LOG_TABLE LIKE ?

            MINUS

            SELECT RU.TABLE_NAME, RU.OWNER

            FROM SYS.ALL_TABLES RU

            WHERE RU.TABLE_NAME LIKE 'RUPD%'

            AND RU.TEMPORARY = 'Y'

            AND RU.OWNER = ?

            AND EXISTS (SELECT 1

            FROM   SYS.ALL_TAB_COLUMNS ATC

            WHERE  ATC.OWNER = RU.OWNER

            AND    ATC.TABLE_NAME = RU.TABLE_NAME

            AND    ATC.COLUMN_NAME = 'CHANGE_VECTOR$$')

            ) X

            UNION ALL

            SELECT T.TABLE_NAME

            , 'TABLE' OBJECT_TYPE

            , (SELECT O.OBJECT_ID

            FROM   SYS.ALL_OBJECTS O

            WHERE  O.OWNER = T.OWNER

            AND O.OBJECT_TYPE = 'TABLE'

            AND O.OBJECT_NAME = T.TABLE_NAME) OBJECT_ID

            , NULL LAST_DDL_TIME

            FROM SYS.ALL_OBJECT_TABLES T

            WHERE T.OWNER = ?

            AND T.TABLE_NAME LIKE ?

            AND T.IOT_NAME IS NULL

            AND T.NESTED = 'NO'

            AND T.SECONDARY = 'N'

            --10g AND    T.DROPPED = 'NO'

            UNION ALL

            SELECT /*OracleDictionaryQueries.ALL_MVIEW_LOG_QUERY*/

            L.MASTER, 'MATERIALIZED VIEW LOG', NULL OBJECT_ID, NULL LAST_DDL_TIME

            FROM SYS.ALL_MVIEW_LOGS L

            WHERE L.LOG_OWNER = ? AND L.MASTER LIKE ?

            • 3. Re: Using 12c DB adapter to read from Oracle 9i database
              DurgadasHaldar

              Did you try creating Adapter by Selecting Table or you tried writing pure Sql.

               

              I dont have Oracle 9i installed but if you write simple query as "Select EMployee_id from employee" that should work