This discussion is archived
10 Replies Latest reply: Nov 22, 2013 3:19 AM by kgronau RSS

Conncet Oracle 11gR2 to Postgres 9.1.3

81cd8ea6-576d-433c-b8c9-2a1c033fb4d4 Newbie
Currently Being Moderated

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
    81cd8ea6-576d-433c-b8c9-2a1c033fb4d4 Newbie
    Currently Being Moderated

    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
    81cd8ea6-576d-433c-b8c9-2a1c033fb4d4 Newbie
    Currently Being Moderated

    [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
    81cd8ea6-576d-433c-b8c9-2a1c033fb4d4 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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
    81cd8ea6-576d-433c-b8c9-2a1c033fb4d4 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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
    81cd8ea6-576d-433c-b8c9-2a1c033fb4d4 Newbie
    Currently Being Moderated

    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
    81cd8ea6-576d-433c-b8c9-2a1c033fb4d4 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points