Node-oracledb: Long datatype

[dhsingh]

I have an oracle database.I am trying to pull the data of X column and data type of X column is LONG.I am getting error from driver.Please check the error below:

"NJS-010: unsupported data type in select list"

Thanks

[pgkehle]

I'm in the same boat, except it's the LONG RAW data type (24).

[cjbj]

Both LONG and LONG RAW are not supported (yet).

[cjbj]

@pgkehle LONG RAW is being tracked in https://github.com/oracle/node-oracledb/issues/218

[pgkehle]

This would be really helpful, even if just to pull the data. I did a simple fork of strongloop's older driver to implement this, at least for the LONG RAW data type. This is a required feature to support legacy systems.

https://github.com/pgkehle/strong-oracle/tree/feature-long-raw

[cjbj]

@pgkehle I'd love to see you implement it for node-oracledb and submit a PR under the CONTRIBUTING guidelines?

[cyates81]

I also have a need for LONG data type. I'm replicating data from a database outside of my control and they are using LONG columns so I am stuck at the moment.

[caerphoto]

I also need LONG support – our database stores the TEXT column in USER_VIEWS as LONG type, and there's nothing I can do about this, so for now the app I'm working on can't display this information.

[dmcghan]

@caerphoto Are you able to use the TO_LOB function to convert the LONG to a LOB and then use the Lob class in the driver?

[caerphoto]

@dmcghan I've added a workaround for now to create a temporary table using TO_LOB (since it can't be used in a plain SELECT statement unfortunately), select from that, then drop it, but it's pretty clunky and it'd be much nicer to be able to select directly from USER_VIEWS.

[pleerock]

Without long support how can I do, lets say:

SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS

[avingochea]

@caerphoto Would you happen to have an example you could share for your workaround? I read something to the tune of what you've described, but I can't seem to figure it out. Any clunky workaround would be welcome at this point

[cjbj]

We hear you. But LOB improvements is higher on our task list. (Well, on my list!)

[caerphoto]

@avingochea see here, specifically the getViewText function:
https://github.com/caerphoto/dbbrowser/blob/master/app/controllers.js

It's a bit of a hassle, but not nearly as bad as the code required to read CLOBs. It's a convoluted mess, but I don't really see any way around it at present. There was another package that made it easier to read CLOB data but it was missing node-oracledb's metaData stuff so for example I couldn't output the columns in the order specified in a user-written query.

[avingochea]

@caerphoto Thanks for the example! Unfortunately I wasn't able to use it because I don't have proper permissions to create the temp table. I had to come up with another hack that involves spawning a child process to run a vbscript to use the adodb driver >_< Oh well, it works for now :)

@cjbj I know you have a long list and your hard work is very much appreciated!

[rwillians]

To whom it might be useful, I have managed to work around my problem on the fly using this trick from Ask Tom:

WITH 
  xml AS (
    SELECT XMLTYPE(
      DBMS_XMLGEN.GETXML(q'{SELECT * FROM dba_views where  view_name = 'DBA_OBJECTS'}')
    ) AS xml
    FROM   dual
  )
, parsed_xml AS (
    SELECT extractValue(xs.object_value, '/ROW/TEXT')             AS text_as_string
      FROM 
        xml x
      , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
  )
SELECT text_as_string -- do not try to convert TO_CLOB because it will fail
FROM   parsed_xml

[cjbj]

Out of interest, how long are the LONGs everyone is using?

It shouldn't be hard to add fetchAsString/fetchAsBuffer support for LONG and LONG RAW to node-oracledb 2.0, but Node would have its normal memory limitations on how big JS Strings & Buffers can be (I couldn't push Strings past 256M).

[rwillians]

3k average... 21k max

I have no prior experience with oracle db, so I had a hard time trying to workaround this since I'm unable to make changes to my legacy database schema.

[pgkehle]

FWIW: I submitted a PR for strongloop's implementation here: https://github.com/strongloop/strong-oracle/pull/55

@cjbj I haven't had the time to do much else, and nothing in this native package.

My example are pictures, which can get pretty large, 1M, as you can imagine.

[cjbj]

The 'fetchAs' support should work fine at those sizes (so hold off on any PRs!)

[cjbj]

node-oracledb v2 now supports LONG and LONG RAW, fetched as String and Buffer respectively. See https://blogs.oracle.com/opal/node-oracledb-2013-development-is-now-on-github and check the doc for details.