Oracle Database Discussions

I have set up a database link between Oracle XE and PostgreSQL using unixODBC, but I seem to be having trouble with certain data types.

Here's what I mean...

First I execute the following DDL in postgres:

create table "TEST1" (
"TEST1ID" integer,
"TESTDATE" timestamp,
"TESTVC" varchar(20)
);

create table "TEST2" (
"A" varchar(125),
"B" varchar(126),
"C" varchar(127),
"D" varchar(128),
"E" varchar(129),
"F" varchar(130),
"G" varchar(200)
);

The tables look fine when I check them using unixODBC's isql tool.

But here's what SQLPlus tells me...

SQL> desc test1@prism
Name Null? Type
----------------------------------------- -------- ----------------------------
TEST1ID NUMBER(10)
TESTDATE DATE
TESTVC VARCHAR2(20)

SQL> desc test2@prism
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(125)
B VARCHAR2(126)
C VARCHAR2(127)
D VARCHAR2(128 CHAR)
E VARCHAR2(256 CHAR)
F VARCHAR2(512 CHAR)
G VARCHAR2(18432 CHAR)

The "TEST1" table looks fine, but, as you can see from the "TEST2" table, varchar columns longer than 128 characters are really screwy.

Any ideas about how to fix this?

If it helps, here's the trace from the above SQLPlus session:

HOAUTIL.C (1259): ; ------ hoadtab (hoat) -------:
HOAUTIL.C (1262): ; hoatnam: TEST1, hoatnml: 5, hoatnrw: -1, hoatarl: 30
HOAUTIL.C (1273): ; ------ hoadtab (hoai) -------:
HOAUTIL.C (1274): ; n_index_stat: 0
HOAUTIL.C (1224): ; -------- hoadtab for table TEST1---------:
HOAUTIL.C (1225): ; hoadamsz: 3, hoadasiz: 3, hoadambr: 1, hoadabrc: 0
HOAUTIL.C (1228): ; row 0 - hoadambl: 4, hoadadty: 8, hoadaprc: 10, hoadacst: 0
HOAUTIL.C (1232): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 7, hoadanam:
TEST1ID, hoadabfl: 4, hoadamod: 0
HOAUTIL.C (1228): ; row 1 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst:
0
HOAUTIL.C (1232): ; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 8, hoadanam:
TESTDATE, hoadabfl: 7, hoadamod: 0
HOAUTIL.C (1228): ; row 2 - hoadambl: 20, hoadadty: 108, hoadaprc: 20,
hoadacst: 0
HOAUTIL.C (1232): ; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 6, hoadanam:
TESTVC, hoadabfl: 20, hoadamod: 0
hoadtab (26): ; hoadtab Exited with retcode = 0.
hoadafr (23): ; hoadafr Entered. id = 0.
hoadafr (23): ; hoadafr Exited with retcode = 0.
hoadtab (26): ; hoadtab Entered. count = 1
hoadtab (26): ; schema_name = , table_name = TEST2
odbc_rec: select * from "TEST2"
r: 165od 0 SQLAllocStmt:- hdbc=8266C78,pst=FF93C95C
r: 188od 0 SQLPrepare:- hst=829D848, css=-7091304
sss = select * from "TEST2"
r:1067od 0 SQLNumResultCols:- hst=829D848,pcol=7
r:1090od 0 SQLDescribeCol:- hst=829D848,icol=1,scn=A,cnm=64,pcn=1,pst=12,
pcd=125,psc=0,pnul=1
r:1118od 0 SQLColAttributes:- hst=829D848,icol=1,fdt=10,rgbd=0,cbdm=0,pcb
d=FF93C822,pfd=FF93C824
r:1090od 0 SQLDescribeCol:- hst=829D848,icol=2,scn=B,cnm=64,pcn=1,pst=12,
pcd=126,psc=0,pnul=1
r:1118od 0 SQLColAttributes:- hst=829D848,icol=2,fdt=10,rgbd=0,cbdm=0,pcb
d=FF93C822,pfd=FF93C824
r:1090od 0 SQLDescribeCol:- hst=829D848,icol=3,scn=C,cnm=64,pcn=1,pst=12,
pcd=127,psc=0,pnul=1
r:1118od 0 SQLColAttributes:- hst=829D848,icol=3,fdt=10,rgbd=0,cbdm=0,pcb
d=FF93C822,pfd=FF93C824
r:1090od 0 SQLDescribeCol:- hst=829D848,icol=4,scn=D,cnm=64,pcn=1,pst=12,
pcd=128,psc=0,pnul=1
r:1118od 0 SQLColAttributes:- hst=829D848,icol=4,fdt=10,rgbd=0,cbdm=0,pcb
d=FF93C822,pfd=FF93C824
r:1090od 0 SQLDescribeCol:- hst=829D848,icol=5,scn=E,cnm=64,pcn=1,pst=12,
pcd=129,psc=0,pnul=1
r:1118od 0 SQLColAttributes:- hst=829D848,icol=5,fdt=10,rgbd=0,cbdm=0,pcb
d=FF93C822,pfd=FF93C824
r:1090od 0 SQLDescribeCol:- hst=829D848,icol=6,scn=F,cnm=64,pcn=1,pst=12,
pcd=130,psc=0,pnul=1
r:1118od 0 SQLColAttributes:- hst=829D848,icol=6,fdt=10,rgbd=0,cbdm=0,pcb
d=FF93C822,pfd=FF93C824
r:1090od 0 SQLDescribeCol:- hst=829D848,icol=7,scn=G,cnm=64,pcn=1,pst=12,
pcd=200,psc=0,pnul=1
r:1118od 0 SQLColAttributes:- hst=829D848,icol=7,fdt=10,rgbd=0,cbdm=0,pcb
d=FF93C822,pfd=FF93C824
r: 225od 0 SQLFreeStmt:- hst=829D848, fop=1
r:1473od 0 SQLAllocStmt:- hdbc=8266C78,pst=FF93C5A4
r:1486od 0 SQLStatistics:- hst=829D848,scn=0,ccn=0,ssn=0,csn=0,stn=FF93C6
38,ctn=-3,funq=1,fcc=0
scn=, ssn=, stn=TEST2
r:1493od 0 SQLBindCol:- hst=829D848,icl=2,fct=1,rgv=FF93CB88,cbvm=129,pcv
=-7092832
r:1494od 0 SQLBindCol:- hst=829D848,icl=4,fct=5,rgv=FF93C59A,cbvm=0,pcv=-
7092836
r:1495od 0 SQLBindCol:- hst=829D848,icl=6,fct=1,rgv=FF93CAF8,cbvm=129,pcv
=-7092844
r:1496od 0 SQLBindCol:- hst=829D848,icl=7,fct=5,rgv=FF93C58E,cbvm=0,pcv=-
7092848
r:1497od 0 SQLBindCol:- hst=829D848,icl=9,fct=1,rgv=FF93CA68,cbvm=129,pcv
=-7092856
r:1498od 0 SQLBindCol:- hst=829D848,icl=10,fct=1,rgv=FF93C582,cbvm=2,pcv=
-7092860
r:1499od 0 SQLBindCol:- hst=829D848,icl=11,fct=4,rgv=FF93C578,cbvm=0,pcv=
-7092868
r:1500od 0 SQLBindCol:- hst=829D848,icl=12,fct=4,rgv=FF93C570,cbvm=0,pcv=
-7092876
r:1507od 100 SQLFetch:- hst=829D848
r:1589od 0 SQLFreeStmt:- hst=829D848, fop=1
nvOUT (qp_sqtxt.c 55): select NON_UNIQUE, INDEX_NAME, TYPE, SEQ_IN_INDEX,
COLLATION, CARDINALITY, COLUMN_NAME from NAV_PROC:SP_STATISTICS('prism', '%',
'TEST2', 1, 0, 0) order by 3, 1, 2, 4
<<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
Original SQL:
select NON_UNIQUE , INDEX_NAME , TYPE , SEQ_IN_INDEX , COLLATION , CARDINALITY , COLUMN_NAME from NAV_PROC : SP_STATISTICS ( 'prism' , '%' , 'TEST2' , 1 , 0 , 0 ) order by 3 , 1 , 2 , 4




Accessing saved query spec SP_STATISTICS()
from NAV_PROC DB

HOAUTIL.C (1259): ; ------ hoadtab (hoat) -------:
HOAUTIL.C (1262): ; hoatnam: TEST2, hoatnml: 5, hoatnrw: -1, hoatarl: 484
HOAUTIL.C (1273): ; ------ hoadtab (hoai) -------:
HOAUTIL.C (1274): ; n_index_stat: 0
HOAUTIL.C (1224): ; -------- hoadtab for table TEST2---------:
HOAUTIL.C (1225): ; hoadamsz: 7, hoadasiz: 7, hoadambr: 1, hoadabrc: 0
HOAUTIL.C (1228): ; row 0 - hoadambl: 125, hoadadty: 108, hoadaprc: 125,
hoadacst: 0
HOAUTIL.C (1232): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 1, hoadanam:
A, hoadabfl: 125, hoadamod: 0
HOAUTIL.C (1228): ; row 1 - hoadambl: 126, hoadadty: 108, hoadaprc: 126,
hoadacst: 0
HOAUTIL.C (1232): ; row 1 - hoadascl: 0, hoadanul: 1, hoadanml: 1, hoadanam:
B, hoadabfl: 126, hoadamod: 0
HOAUTIL.C (1228): ; row 2 - hoadambl: 127, hoadadty: 108, hoadaprc: 127,
hoadacst: 0
HOAUTIL.C (1232): ; row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 1, hoadanam:
C, hoadabfl: 127, hoadamod: 0
HOAUTIL.C (1228): ; row 3 - hoadambl: 128, hoadadty: 108, hoadaprc: 128,
hoadacst: 0
HOAUTIL.C (1232): ; row 3 - hoadascl: 0, hoadanul: 1, hoadanml: 1, hoadanam:
D, hoadabfl: 128, hoadamod: 0
HOAUTIL.C (1228): ; row 4 - hoadambl: 129, hoadadty: 108, hoadaprc: 129,
hoadacst: 0
HOAUTIL.C (1232): ; row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 1, hoadanam:
E, hoadabfl: 129, hoadamod: 0
HOAUTIL.C (1228): ; row 5 - hoadambl: 130, hoadadty: 108, hoadaprc: 130,
hoadacst: 0
HOAUTIL.C (1232): ; row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 1, hoadanam:
F, hoadabfl: 130, hoadamod: 0
HOAUTIL.C (1228): ; row 6 - hoadambl: 200, hoadadty: 108, hoadaprc: 200,
hoadacst: 0
HOAUTIL.C (1232): ; row 6 - hoadascl: 0, hoadanul: 1, hoadanml: 1, hoadanam:
G, hoadabfl: 200, hoadamod: 0
hoadtab (26): ; hoadtab Exited with retcode = 0.
hoadafr (23): ; hoadafr Entered. id = 0.
hoadafr (23): ; hoadafr Exited with retcode = 0.
hoacomm (11): ; hoacomm Entered. keepinfo = FALSE, tflag = 1.
e:1328od 0 SQLTransact:- henv=0,hdbc=8266C78, ftp=0
e:1334od 0 SQLSetConnectOption:- hdbc=8266C78, fop=102 , vprm=1
hoacomm (11): ; hoacomm Exited with retcode = 0.
hoalgof (8): ; hoalgof Entered. tflag = 1.
nvRETURN (drviunwn.c 779): -1210
i:1231od 0 SQLDisconnect:- hdbc=8266C78
i:1238od 0 SQLFreeConnect:- hdbc=8266C78
(Last message occurred 2 times)
hoalgof (8): ; hoalgof Exited with retcode = 0.
hoaexit (6): ; hoaexit Entered.
hoaexit (6): ; hoaexit Exited with retcode = 0.