Multiple issues with oracle

[jpalus]

When trying to create reproducer for:

error: oracle: abnormal data representation for date

which I ultimately failed to reproduce I've countered multiple other issues, see output:

=> create table test ( xml xmltype , d date);
CREATE TABLE
=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
=> select * from test;
 XML |           D    
-----+------------------------
 $   | -10066-02-17T22:19:15Z 
(1 row)

=> commit;
COMMIT
=> select * from test;
 XML |           D                
-----+------------------------
 $   | -10066-02-17T22:19:15Z 
(1 row)

Issues observed:

• even though 6 records were inserted usql finds only one (sqlplus finds 6)
• XML shows '$' instead of XML content
• date is completely broken

Oracle version: 12.2 usql version: 0.9.2

[kenshaw]

@jpalus Thanks for pointing this out. It's an issue with the go-ora driver, and I can verify that it's a problem. Please note that the godror driver works fine:

$ usql gr://
Connected with driver godror (Oracle Database 19.0.0.0.0)
Type "help" for help.

gr:system@localhost/orasid=> select sysdate from dual;
       SYSDATE        
----------------------
 2021-07-15T09:37:31Z 
(1 row)

gr:system@localhost/orasid=> select * from test;
   XML   |          D           
---------+----------------------
 <test/>+| 2021-07-15T09:36:46Z 
         |  
 <test/>+| 2021-07-15T09:36:53Z 
         |  
(2 rows)

gr:system@localhost/orasid=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
gr:system@localhost/orasid=> select * from test;
   XML   |          D           
---------+----------------------
 <test/>+| 2021-07-15T09:36:46Z 
         |  
 <test/>+| 2021-07-15T09:36:53Z 
         |  
 <test/>+| 2021-07-15T09:38:44Z 
         |  
(3 rows)

gr:system@localhost/orasid=> select sysdate from dual;
       SYSDATE        
----------------------
 2021-07-15T09:39:05Z 
(1 row)

gr:system@localhost/orasid=> insert into test values(xmltype.createxml('<test></test>'), sysdate);
INSERT 1
gr:system@localhost/orasid=> select sysdate from dual;
       SYSDATE        
----------------------
 2021-07-15T09:39:18Z 
(1 row)

gr:system@localhost/orasid=> select * from test;
   XML   |          D           
---------+----------------------
 <test/>+| 2021-07-15T09:36:46Z 
         |  
 <test/>+| 2021-07-15T09:36:53Z 
         |  
 <test/>+| 2021-07-15T09:38:44Z 
         |  
 <test/>+| 2021-07-15T09:39:10Z 
         |  
(4 rows)

gr:system@localhost/orasid=>

It's fairly easy to build usql with godror if you already have the Oracle instantclient setup. Additionally, there's a grab-instantclient.sh in the contrib/godror directory that should retrieve and install instantclient in a way that godror can build against. I made the decision to move over to go-ora so that there could be a pure Go driver available for Oracle, and thus it'd be available (although limited, and somewhat broken) for usql in built binaries.

Try building/installing usql like this:

$ git clone https://github.com/xo/usql && cd usql
$ ./most.sh -i -t godror

That will do a go install of a version of usql that is built very similar to the way the releases are built.

[jpalus]

@kenshaw godror indeed works much better thanks! Pitty it does not fix #268 though.

[kenshaw]

@jpalus PRs are always welcome :)

[nineinchnick]

Can we do anything to improve this when using go-ora? If not, can we close this issue?

[kenshaw]

The ora driver now reports this as an unsupported data type. Closing this, as the godror driver works without issue, and it's not within the scope of the usql project to fix the underlying database drivers. @jpalus if you'd like for usql to work with XMLTYPE columns, then you'll need to ask nicely and/or submit a PR to the ora driver.