10 Replies Latest reply: Nov 22, 2013 5:19 AM by Kgronau-Oracle RSS

    Conncet Oracle 11gR2 to Postgres 9.1.3

    1054048

      Hi Mates,

       

      I am trying to connect from oracle to a postgres database I will post what I did so far to see if you guys can help me:

       

      [oracle@bi-db admin]$ cat /etc/odbc.ini

      [DB_PGSQL]

      Description = DB_PGSQL

      Driver = /usr/lib64/psqlodbcw.so

      Trace = Yes

      TraceFil = /tmp/odbc.log

      Database = sascar_bi

      Servername = 10.0.100.81

      Username = usr

      Password = pwd

      Port = 5432

      ReadOnly = Yes

       

      #####################################################

       

      [oracle@bi-db admin]$ ls -ltr  /usr/lib64/psqlodbcw.so

      -rwxr-xr-x 1 root root 519496 Jul  7  2010 /usr/lib64/psqlodbcw.so

       

      #####################################################

       

      [oracle@bi-db admin]$ cat /etc/odbcinst.ini

      # Example driver definitions

       

      # Driver from the postgresql-odbc package

      # Setup from the unixODBC package

      [PostgreSQL]

      Description        = ODBC for PostgreSQL

      Description        = Postgresql driver for Linux

      Driver                = /usr/lib64/psqlodbcw.so

      UsageCount      = 1

       

      #####################################################

       

      [oracle@bi-db admin]$ ls -ltr /usr/lib64/psqlodbcw.so

      -rwxr-xr-x 1 root root 519496 Jul  7  2010 /usr/lib64/psqlodbcw.so

       

      #####################################################

      [oracle@bi-db admin]$ cat tnsnames.ora

       

      ..

      ..

      ..

       

      DB_PGSQL =

        (DESCRIPTION =

          (ADDRESS_LIST =

           (ADDRESS = (PROTOCOL = TCP)(HOST = bi-db)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVER = DEDICATED)

             (SID = DB_PGSQL)

           )

         (HS = OK)

        )

       

      #####################################################

       

      [oracle@bi-db admin]$ cat listener.ora

       

      SID_LIST_LISTENER =

      (SID_LIST =

         (SID_DESC =

           (SID_NAME = DB_PGSQL)

           (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

           (PROGRAM = dg4odbc)

           (ENVS=LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/11.2.0/db_1/lib)

         )

      )

       

      #####################################################

       

      [oracle@bi-db admin]$ cat /u01/app/oracle/product/11.2.0/db_1/hs/admin/initDB_PGSQL.ora

       

      HS_FDS_CONNECT_INFO = DB_PGSQL

      HS_FDS_TRACE_LEVEL = off

      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

      #

      # ODBC specific environment variables

      #

      set ODBCINI= /etc/odbc.ini

       

      #####################################################

       

      [oracle@bi-db admin]$ ls -ltr /usr/lib64/libodbc.so

       

      lrwxrwxrwx 1 root root 16 Nov 11 14:26 /usr/lib64/libodbc.so -> libodbc.so.2.0.0

       

      #####################################################

       

      create public database link pg_link connect to "usr" identified by "pwd" using 'DB_PGSQL';

       

      #####################################################

       

      [oracle@bi-db admin]$ tnsping DB_PGSQL

       

       

      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-NOV-2013 11:03:26

       

       

      Copyright (c) 1997, 2009, Oracle.  All rights reserved.

       

       

      Used parameter files:

       

       

       

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bi-db)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = DB_PGSQL)) (HS = OK))

      OK (0 msec)

       

      #####################################################

       

      SQL> select * from "temp"@pg_link;

      select * from "temp"@pg_link

                           *

      ERROR at line 1:

      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

      [

       

      #####################################################

       

      Could you guys help me out on this?

       

      Thanks in advance.

        • 1. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
          1054048

          ORACLE

          IP: 10.0.100.91

          hostname: bi-db.sascar.br

          db_name: bidw

           

          POSTGRESQL

          IP: 10.0.100.81

          hostname: sascar_D-1

          db_name: sascar_bi

          port: 5432

          • 2. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
            1054048

            [root@bi-db ~]# rpm -qa | grep unixODBC

            unixODBC-2.2.14-12.el6_3.x86_64

            unixODBC-devel-2.2.14-12.el6_3.x86_64

            • 3. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
              1054048

              vi /u01/app/oracle/product/11.2.0/db_1/hs/log/DB_PGSQL_agt_26733.trc

               

              Entered hgosuec at 2013/11/12-11:39:15

              Exiting hgosuec, rc=0 at 2013/11/12-11:39:15

              HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"

              HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"

              HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"

              HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"

              HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"

              HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"

              HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"

              HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"

              HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"

              HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"

              using oracle_dblink as default value for "HS_FDS_DEFAULT_OWNER"

              HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

              Entered hgocont at 2013/11/12-11:39:15

              HS_FDS_CONNECT_INFO = "DB_PGSQL"

              RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"

              Entered hgogenconstr at 2013/11/12-11:39:15

              dsn:DB_PGSQL, name:oracle_dblink

              optn:

              Entered hgocip at 2013/11/12-11:39:15

              dsn:DB_PGSQL

              Exiting hgocip, rc=0 at 2013/11/12-11:39:15

              Exiting hgogenconstr, rc=0 at 2013/11/12-11:39:15

              Entered hgopoer at 2013/11/12-11:39:15

              hgopoer, line 233: got native error 0 and sqlstate I; message follows...

              [

              Exiting hgopoer, rc=0 at 2013/11/12-11:39:15

              hgocont, line 2752: calling SqlDriverConnect got sqlstate I

              Exiting hgocont, rc=28500 at 2013/11/12-11:39:15 with error ptr FILE:hgocont.c LINE:2772 FUNCTION:hgocont() ID:Something other than invalid authorization

              Exiting hgolgon, rc=28500 at 2013/11/12-11:39:15 with error ptr FILE:hgolgon.c LINE:781 FUNCTION:hgolgon() ID:Calling hgocont

              Entered hgoexit at 2013/11/12-11:39:15

              Exiting hgoexit, rc=0

              • 4. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
                Kgronau-Oracle

                Unfortunately you didn't add an ODBC trace, but the error might be related to NLS settings (unixODBC does not handle multibyte DSNs correctly).

                So please add to your gateway init file "initDB_PGSQL.ora" the parameters:

                HS_LANGUAGE=american_america.we8iso8859p1

                HS_NLS_NCHAR=UCS2

                 

                Then open a new SQL*Plus session and test the select again.

                 

                - Klaus

                • 5. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
                  1054048

                  Hi kgronau

                   

                   

                   

                  Thanks mate this was the problem I got it working by trying too many time until it works.

                   

                   

                   

                  HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 <- This is my previuosly conf

                  HS_NLS_NCHAR=UCS2 <- I added it as you told

                   

                   

                   

                  But now my problem is that when I execute this query below it returns the column "clinome "with just 3 characters and it is suppose to return the full name of a client.

                   

                   

                   

                  SQL> SELECT b."clioid", b."clicidade_com", b."clicidade_res", b."clinome", b."clitipo", b."cliuf_com", b."cliuf_res"

                  FROM "clientes"@SASCAR_D1  b where rownum < 5;

                   

                      clioid clicidade_com                  clicidade_res                  clinome               cli cliuf_com  cliuf_res

                  ---------- ------------------------------ ------------------------------ ---------------------------------------- ---- ---------------- ----------

                  51331                                          NULL                           TES                           F

                  146983                                                                           LUI                             F

                  158403                                                                           FAB                           F

                  267201 MAUA                                                                 JUL                           J   SP

                                                                                                          l

                                                                                                          l-> This field contains the client's full name but it returning just the three first letters

                   

                   

                   

                  When I omit the rownum clause it executes successfully as expected.

                   

                  Thanks in advance.

                  • 6. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
                    Mkirtley-Oracle

                    Hi,

                      So if you issue the query as -

                     

                    SELECT b."clioid", b."clicidade_com", b."clicidade_res", b."clinome", b."clitipo", b."cliuf_com", b."cliuf_res"FROM "clientes"@SASCAR_D1  b ;

                     

                    you get the correct results ?
                    What is the Postgres datatype of the clinome column and what is returned by -

                     

                    describe "clientes"@SASCAR_D1

                     

                    - do not add a semicolon at the end.

                     

                    Regards,

                    Mike

                    • 7. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
                      Mkirtley-Oracle

                      Hi,

                        Could you also get 2 gateway 255 level traces so we can see the SQL sent with and witout the 'where rownum <5".   Run the select with the 'where rownum <5' then end that session, start a new session and run the select without the 'where'.
                      To get the trace set the gateway init<sid>.ora parameter -

                       

                      HS_FDS_TRACE_LEVEL = 255

                       

                      and run the selects from a new SQLPLUS session.

                      The main part we are interested in is the section that begins - Entered hgopars,.....

                       

                      You could also compare the datatype in each trace for the clinome column which should be in a section beginning - The hoada for table.....

                       

                      Are they the same in both traces ?

                       

                      Regards,

                      Mike

                      • 8. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
                        1054048

                        Hi Mike,

                         

                        First of all thanks for helping me.

                         

                        Here is the log using rownum there is indeed a error.

                         

                        Entered hgopars, cursor id 1 at 2013/11/21-13:49:21

                        type:0

                        SQL text from hgopars, id=1, len=107 ...

                             00: 53454C45 43542022 636C696F 6964222C  [SELECT "clioid",]

                             10: 22636C69 7469706F 222C2263 6C696E6F  ["clitipo","clino]

                             20: 6D65222C 22636C69 75665F72 6573222C  [me","cliuf_res",]

                             30: 22636C69 63696461 64655F72 6573222C  ["clicidade_res",]

                             40: 22636C69 75665F63 6F6D222C 22636C69  ["cliuf_com","cli]

                             50: 63696461 64655F63 6F6D2220 46524F4D  [cidade_com" FROM]

                             60: 2022636C 69656E74 657322             [ "clientes"]

                        Exiting hgopars, rc=0 at 2013/11/21-13:49:22

                        hostmstr:          0:   HOA After hoxpars

                        hostmstr:          0:   HOA Before hoxopen

                        Entered hgoopen, cursor id 1 at 2013/11/21-13:49:22

                        hgoopen, line 86: NO hoada to print

                        Deferred open until first fetch.

                        Exiting hgoopen, rc=0 at 2013/11/21-13:49:22

                        hostmstr:          0:   HOA After hoxopen

                        hostmstr:          0:   HOA Before hoxdscr

                        Entered hgodscr, cursor id 1 at 2013/11/21-13:49:22

                        Allocate hoada @ 0x10f2878

                        Entered hgopcda at 2013/11/21-13:49:22

                        Column:1(clioid): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0

                        Exiting hgopcda, rc=0 at 2013/11/21-13:49:22

                        Entered hgopcda at 2013/11/21-13:49:22

                        Column:2(clitipo): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:1, octet:1, sign:1, radix:0

                        Exiting hgopcda, rc=0 at 2013/11/21-13:49:22

                        Entered hgopcda at 2013/11/21-13:49:22

                        Column:3(clinome): dtype:-1 (LONGVARCHAR), prc/scl:8190/0, nullbl:1, octet:8190, sign:1, radix:0

                        Exiting hgopcda, rc=0 at 2013/11/21-13:49:22

                        Entered hgopcda at 2013/11/21-13:49:22

                        Column:4(cliuf_res): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:1, octet:2, sign:1, radix:0

                        Exiting hgopcda, rc=0 at 2013/11/21-13:49:22

                        Entered hgopcda at 2013/11/21-13:49:22

                        Column:5(clicidade_res): dtype:12 (VARCHAR), prc/scl:65/0, nullbl:1, octet:65, sign:1, radix:0

                        Exiting hgopcda, rc=0 at 2013/11/21-13:49:22

                        Entered hgopcda at 2013/11/21-13:49:22

                        Column:6(cliuf_com): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:1, octet:2, sign:1, radix:0

                        Exiting hgopcda, rc=0 at 2013/11/21-13:49:22

                        Entered hgopcda at 2013/11/21-13:49:22

                        Column:7(clicidade_com): dtype:12 (VARCHAR), prc/scl:65/0, nullbl:1, octet:65, sign:1, radix:0

                        Exiting hgopcda, rc=0 at 2013/11/21-13:49:22

                        hgodscr, line 880: Printing hoada @ 0x10f2878

                        MAX:7, ACTUAL:7, BRC:100, WHT=5 (SELECT_LIST)

                        hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x20:NEGATIVE_HOADADTY)

                        DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                          4 INTEGER     N          4          4   0/  0    0   0   0 clioid

                        12 VARCHAR     Y          1          1   0/  0    0   0  200 clitipo

                        -1 LONGVARCHAR Y          0          0   0/  0    0   0  220 clinome

                        12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_res

                        12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_res

                        12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_com

                        12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_com

                        Exiting hgodscr, rc=0 at 2013/11/21-13:49:22

                        hostmstr:          0:   HOA After hoxdscr

                        hostmstr:          0: RPC After SQL Bundling

                        hostmstr:          0: RPC Before Fetch Row

                        hostmstr:          0:   HOA Before hoaftch

                        Entered hgoftch, cursor id 1 at 2013/11/21-13:49:22

                        hgoftch, line 130: Printing hoada @ 0x10f2878

                        MAX:7, ACTUAL:7, BRC:100, WHT=5 (SELECT_LIST)

                        hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x20:NEGATIVE_HOADADTY)

                        DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                          4 INTEGER     N          4          4   0/  0    0   0   0 clioid

                        12 VARCHAR     Y          1          1   0/  0    0   0  200 clitipo

                        -1 LONGVARCHAR Y          3          0   0/  0    0   0  220 clinome

                        12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_res

                        12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_res

                        12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_com

                        12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_com

                        Performing delayed open.

                        SQLBindCol: column 1, cdatatype: -16, bflsz: 4

                        SQLBindCol: column 2, cdatatype: 1, bflsz: 2

                        SQLBindCol: column 3, cdatatype: 1, bflsz: 4

                        SQLBindCol: column 4, cdatatype: 1, bflsz: 3

                        SQLBindCol: column 5, cdatatype: 1, bflsz: 66

                        SQLBindCol: column 6, cdatatype: 1, bflsz: 3

                        SQLBindCol: column 7, cdatatype: 1, bflsz: 66

                        Entered hgopoer at 2013/11/21-13:49:22

                        hgopoer, line 233: got native error -2 and sqlstate 01004; message follows...

                        Fetched item was truncated. {01004,NativeErr = -2}

                        Exiting hgopoer, rc=0 at 2013/11/21-13:49:22

                        hgoftch, line 740: calling SQLFetch got sqlstate 01004

                        SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4

                        SQLFetch: row: 1, column 1, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)

                          0: 234F0200                             [#O..]

                        SQLFetch: row: 1, column 2, bflsz: 2, bflar: 1

                        SQLFetch: row: 1, column 2, bflsz: 2,  bflar: 1, (bfl: 1, mbl: 1)

                          0: 46                                   [F]

                        SQLFetch: row: 1, column 3, bflsz: 4, bflar: 5

                        SQLFetch: row: 1, column 3, bflsz: 4,  bflar: 5, (bfl: 3, mbl: 0)

                          0: 54455300 4C                          [TES.L]

                        SQLFetch: row: 1, column 4, bflsz: 3, bflar: 0

                        SQLFetch: row: 1, column 4, bflsz: 3,  bflar: 0, (bfl: 2, mbl: 2)

                        SQLFetch: row: 1, column 5, bflsz: 66, bflar: 4

                        SQLFetch: row: 1, column 5, bflsz: 66,  bflar: 4, (bfl: 65, mbl: 65)

                          0: 4E554C4C                             [NULL]

                        SQLFetch: row: 1, column 6, bflsz: 3, bflar: -1

                        SQLFetch: row: 1, column 6, bflsz: 3,  bflar: SQL_NULL_DATA

                        SQLFetch: row: 1, column 7, bflsz: 66, bflar: -1

                        SQLFetch: row: 1, column 7, bflsz: 66,  bflar: SQL_NULL_DATA

                        SQLFetch: row: 2, column 1, bflsz: 4, bflar: 4

                        SQLFetch: row: 2, column 1, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)

                          0: 273E0200                             ['>..]

                        SQLFetch: row: 2, column 2, bflsz: 2, bflar: 1

                        SQLFetch: row: 2, column 2, bflsz: 2,  bflar: 1, (bfl: 0, mbl: 1)

                          0: 46                                   [F]

                        SQLFetch: row: 2, column 3, bflsz: 4, bflar: 19

                        SQLFetch: row: 2, column 3, bflsz: 4,  bflar: 19, (bfl: 3, mbl: 0)

                          00: 4C554900 46414200 4A554C00 4C554300  [LUI.FAB.JUL.LUC.]

                          10: 464552                               [FER]

                        SQLFetch: row: 2, column 4, bflsz: 3, bflar: 0

                        SQLFetch: row: 2, column 4, bflsz: 3,  bflar: 0, (bfl: 0, mbl: 2)

                        SQLFetch: row: 2, column 5, bflsz: 66, bflar: 0

                        SQLFetch: row: 2, column 5, bflsz: 66,  bflar: 0, (bfl: 0, mbl: 65)

                        SQLFetch: row: 2, column 6, bflsz: 3, bflar: -1

                        SQLFetch: row: 2, column 6, bflsz: 3,  bflar: SQL_NULL_DATA

                        SQLFetch: row: 2, column 7, bflsz: 66, bflar: -1

                        SQLFetch: row: 2, column 7, bflsz: 66,  bflar: SQL_NULL_DATA

                        SQLFetch: row: 3, column 1, bflsz: 4, bflar: 4

                        ...

                        • 9. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
                          1054048

                          And here not using rownum:

                           

                          Entered hgopars, cursor id 1 at 2013/11/21-13:49:48

                          type:0

                          SQL text from hgopars, id=1, len=131 ...

                               00: 53454C45 43542041 312E2263 6C696F69  [SELECT A1."clioi]

                               10: 64222C41 312E2263 6C696369 64616465  [d",A1."clicidade]

                               20: 5F636F6D 222C4131 2E22636C 69636964  [_com",A1."clicid]

                               30: 6164655F 72657322 2C41312E 22636C69  [ade_res",A1."cli]

                               40: 6E6F6D65 222C4131 2E22636C 69746970  [nome",A1."clitip]

                               50: 6F222C41 312E2263 6C697566 5F636F6D  [o",A1."cliuf_com]

                               60: 222C4131 2E22636C 6975665F 72657322  [",A1."cliuf_res"]

                               70: 2046524F 4D202263 6C69656E 74657322  [ FROM "clientes"]

                               80: 204131                               [ A1]

                          Exiting hgopars, rc=0 at 2013/11/21-13:49:49

                          hostmstr:          0:   HOA After hoxpars

                          hostmstr:          0:   HOA Before hoxopen

                          Entered hgoopen, cursor id 1 at 2013/11/21-13:49:49

                          hgoopen, line 86: NO hoada to print

                          Deferred open until first fetch.

                          Exiting hgoopen, rc=0 at 2013/11/21-13:49:49

                          hostmstr:          0:   HOA After hoxopen

                          hostmstr:          0:   HOA Before hoxdscr

                          Entered hgodscr, cursor id 1 at 2013/11/21-13:49:49

                          Allocate hoada @ 0x1226878

                          Entered hgopcda at 2013/11/21-13:49:49

                          Column:1(clioid): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0

                          Exiting hgopcda, rc=0 at 2013/11/21-13:49:49

                          Entered hgopcda at 2013/11/21-13:49:49

                          Column:2(clicidade_com): dtype:12 (VARCHAR), prc/scl:65/0, nullbl:1, octet:65, sign:1, radix:0

                          Exiting hgopcda, rc=0 at 2013/11/21-13:49:49

                          Entered hgopcda at 2013/11/21-13:49:49

                          Column:3(clicidade_res): dtype:12 (VARCHAR), prc/scl:65/0, nullbl:1, octet:65, sign:1, radix:0

                          Exiting hgopcda, rc=0 at 2013/11/21-13:49:49

                          Entered hgopcda at 2013/11/21-13:49:49

                          Column:4(clinome): dtype:-1 (LONGVARCHAR), prc/scl:8190/0, nullbl:1, octet:8190, sign:1, radix:0

                          Exiting hgopcda, rc=0 at 2013/11/21-13:49:49

                          Entered hgopcda at 2013/11/21-13:49:49

                          Column:5(clitipo): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:1, octet:1, sign:1, radix:0

                          Exiting hgopcda, rc=0 at 2013/11/21-13:49:49

                          Entered hgopcda at 2013/11/21-13:49:49

                          Column:6(cliuf_com): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:1, octet:2, sign:1, radix:0

                          Exiting hgopcda, rc=0 at 2013/11/21-13:49:49

                          Entered hgopcda at 2013/11/21-13:49:49

                          Column:7(cliuf_res): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:1, octet:2, sign:1, radix:0

                          Exiting hgopcda, rc=0 at 2013/11/21-13:49:49

                          hgodscr, line 880: Printing hoada @ 0x1226878

                          MAX:7, ACTUAL:7, BRC:100, WHT=5 (SELECT_LIST)

                          hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x20:NEGATIVE_HOADADTY)

                          DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                            4 INTEGER     N          4          4   0/  0    0   0   0 clioid

                          12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_com

                          12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_res

                          -1 LONGVARCHAR Y          0          0   0/  0    0   0  220 clinome

                          12 VARCHAR     Y          1          1   0/  0    0   0  200 clitipo

                          12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_com

                          12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_res

                          Exiting hgodscr, rc=0 at 2013/11/21-13:49:49

                          hostmstr:          0:   HOA After hoxdscr

                          hostmstr:          0: RPC After SQL Bundling

                          hostmstr:          0: RPC Before Fetch Row

                          hostmstr:          0:   HOA Before hoaftch

                          Entered hgoftch, cursor id 1 at 2013/11/21-13:49:49

                          hgoftch, line 130: Printing hoada @ 0x1226878

                          MAX:7, ACTUAL:7, BRC:100, WHT=5 (SELECT_LIST)

                          hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x20:NEGATIVE_HOADADTY)

                          DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                            4 INTEGER     N          4          4   0/  0    0   0   0 clioid

                          12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_com

                          12 VARCHAR     Y         65         65   0/  0    0   0  200 clicidade_res

                          -1 LONGVARCHAR Y        196          0   0/  0    0   0  220 clinome

                          12 VARCHAR     Y          1          1   0/  0    0   0  200 clitipo

                          12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_com

                          12 VARCHAR     Y          2          2   0/  0    0   0  200 cliuf_res

                          Performing delayed open.

                          SQLBindCol: column 1, cdatatype: -16, bflsz: 4

                          SQLBindCol: column 2, cdatatype: 1, bflsz: 66

                          SQLBindCol: column 3, cdatatype: 1, bflsz: 66

                          SQLBindCol: column 4, cdatatype: 1, bflsz: 197

                          SQLBindCol: column 5, cdatatype: 1, bflsz: 2

                          SQLBindCol: column 6, cdatatype: 1, bflsz: 3

                          SQLBindCol: column 7, cdatatype: 1, bflsz: 3

                          SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4

                          SQLFetch: row: 1, column 1, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)

                            0: 234F0200                             [#O..]

                          SQLFetch: row: 1, column 2, bflsz: 66, bflar: -1

                          SQLFetch: row: 1, column 2, bflsz: 66,  bflar: SQL_NULL

                          • 10. Re: Conncet Oracle 11gR2 to Postgres 9.1.3
                            Kgronau-Oracle

                            I've just checked it with a table I created on my own where my character varying is 8192 and 200000 - for both the content is displayed fine.

                             

                            Could you please post the table definition as it is defined at your Postgres database as well as upload a gateway trace level 255 to a public file server like Dropbox or Google?

                             

                            - Klaus