Serialized LOB Object output instead of content when object contains a LOB

[markddrake]

See https://www.oracle.com/corporate/security-practices/assurance/vulnerability/reporting.html for how to report security issues.

1. With the async/await programming style, make sure you are using 'await' in the right places.

2. Is it an error or a hang or a crash?
   Error

3. What error(s) you are seeing?
   Cut and paste text showing the command you ran. No screenshots. Use a gist for long screen output and logs: see https://gist.github.com/.
   Incorrect output

4. Include a runnable Node.js script that shows the problem.
   Include all SQL needed to create the database schema. Use Markdown syntax, see https://help.github.com/github/writing-on-github/basic-writing-and-formatting-syntax

"use strict"
const oracledb = require('oracledb');

async function main() {

  const connectionDetails = {
    user          : "system",
    password      : "oracle",
    connectString : "YDB21803"
  };
  
  const sql1 = 
`select CUST_ADDRESS from OE.CUSTOMERS where rownum < 2`;
  const sql2 = 
`select AD_TEXTDOCS_NTAB from PM.PRINT_MEDIA where rownum < 2`;
    
  let results;
  let conn;  
  let i;
  try {
      
    conn = await oracledb.getConnection(connectionDetails);
    results = await conn.execute(sql1);
	const addr = results.rows[0][0];
	console.log(JSON.stringify(addr))
	
/*
SQL> desc PM.PRINT_MEDIA
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                                NOT NULL NUMBER(6)
 AD_ID                                     NOT NULL NUMBER(6)
 AD_COMPOSITE                                       BLOB
 AD_SOURCETEXT                                      CLOB
 AD_FINALTEXT                                       CLOB
 AD_FLTEXTN                                         NCLOB
 AD_TEXTDOCS_NTAB                                   PM.TEXTDOC_TAB
 AD_PHOTO                                           BLOB
 AD_GRAPHIC                                         BINARY FILE LOB
 AD_HEADER                                          PM.ADHEADER_TYP

SQL> desc PM.TEXTDOC_TAB
 PM.TEXTDOC_TAB TABLE OF PM.TEXTDOC_TYP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DOCUMENT_TYP                                       VARCHAR2(32)
 FORMATTED_DOC                                      BLOB

SQL>
*/	
    results = await conn.execute(sql2);
	const obj = results.rows[0][0];
	console.log(JSON.stringify(obj))
	
	
 } catch(e) {
   console.log('Failed',e);    
 } 
}
main();

C:\Development\YADAMU>node scratch\oracle\objectTest1.js
{"STREET_ADDRESS":"514 W Superior St","POSTAL_CODE":"46901","CITY":"Kokomo","STATE_PROVINCE":"IN","COUNTRY_ID":"US"}
[{"DOCUMENT_TYP":"Simple Text (.txt)","FORMATTED_DOC":{"_readableState":{"objectMode":false,"highWaterMark":16384,"buffer":{"head":null,"tail":null,"length":0},"length":0,"pipes":null,"pipesCount":0,"flowing":null,"ended":false,"endEmitted":false,"reading":false,"sync":true,"needReadable":false,"emittedReadable":false,"readableListening":false,
"resumeScheduled":false,"paused":true,"emitClose":true,"autoDestroy":false,"destroyed":false,"defaultEncoding":"utf8","awaitDrain":0,"readingMore":false,"decoder":null,"encoding":null},"readable":true,"_events":{},"_eventsCount":1,"_writableState":{"objectMode":false,"highWaterMark":16384,"finalCalled":false,"needDrain":false,"ending":false,"en
ded":false,"finished":false,"destroyed":false,"decodeStrings":false,"defaultEncoding":"utf8","length":0,"writing":false,"corked":0,"sync":true,"bufferProcessing":false,"writecb":null,"writelen":0,"bufferedRequest":null,"lastBufferedRequest":null,"pendingcb":0,"prefinished":false,"errorEmitted":false,"emitClose":true,"autoDestroy":false,"buffere
dRequestCount":0,"corkedRequestsFree":{"next":null,"entry":null}},"writable":true,"allowHalfOpen":true,"offset":1}},{"DOCUMENT_TYP":"Rich Text (.rtf)","FORMATTED_DOC":{"_readableState":{"objectMode":false,"highWaterMark":16384,"buffer":{"head":null,"tail":null,"length":0},"length":0,"pipes":null,"pipesCount":0,"flowing":null,"ended":false,"endE
mitted":false,"reading":false,"sync":true,"needReadable":false,"emittedReadable":false,"readableListening":false,"resumeScheduled":false,"paused":true,"emitClose":true,"autoDestroy":false,"destroyed":false,"defaultEncoding":"utf8","awaitDrain":0,"readingMore":false,"decoder":null,"encoding":null},"readable":true,"_events":{},"_eventsCount":1,"_
writableState":{"objectMode":false,"highWaterMark":16384,"finalCalled":false,"needDrain":false,"ending":false,"ended":false,"finished":false,"destroyed":false,"decodeStrings":false,"defaultEncoding":"utf8","length":0,"writing":false,"corked":0,"sync":true,"bufferProcessing":false,"writecb":null,"writelen":0,"bufferedRequest":null,"lastBufferedR
equest":null,"pendingcb":0,"prefinished":false,"errorEmitted":false,"emitClose":true,"autoDestroy":false,"bufferedRequestCount":0,"corkedRequestsFree":{"next":null,"entry":null}},"writable":true,"allowHalfOpen":true,"offset":1}},{"DOCUMENT_TYP":"Word File (.doc)","FORMATTED_DOC":{"_readableState":{"objectMode":false,"highWaterMark":16384,"buffe
r":{"head":null,"tail":null,"length":0},"length":0,"pipes":null,"pipesCount":0,"flowing":null,"ended":false,"endEmitted":false,"reading":false,"sync":true,"needReadable":false,"emittedReadable":false,"readableListening":false,"resumeScheduled":false,"paused":true,"emitClose":true,"autoDestroy":false,"destroyed":false,"defaultEncoding":"utf8","a
waitDrain":0,"readingMore":false,"decoder":null,"encoding":null},"readable":true,"_events":{},"_eventsCount":1,"_writableState":{"objectMode":false,"highWaterMark":16384,"finalCalled":false,"needDrain":false,"ending":false,"ended":false,"finished":false,"destroyed":false,"decodeStrings":false,"defaultEncoding":"utf8","length":0,"writing":false,
"corked":0,"sync":true,"bufferProcessing":false,"writecb":null,"writelen":0,"bufferedRequest":null,"lastBufferedRequest":null,"pendingcb":0,"prefinished":false,"errorEmitted":false,"emitClose":true,"autoDestroy":false,"bufferedRequestCount":0,"corkedRequestsFree":{"next":null,"entry":null}},"writable":true,"allowHalfOpen":true,"offset":1}}]

C:\Development\YADAMU>
5. Run node and show the output of:

C:\Development\YADAMU\app>node
> process.platform
'win32'
> process.version
'v11.9.0'
> process.arch
'x64'
> require('oracledb').versionString
'4.0.1'
> require('oracledb').oracleClientVersionString
'12.2.0.1.0'
>

6. What is your Oracle Database version?
   18,19,20

[markddrake]

Given that the FORMATTED_DOC is a BLOB I would have expected a HexBinary representation of the LOB in the JSON object.

[anthony-tuininga]

That's a lot of text and I'm not sure what exactly you are trying to say. Can you create a test case that contains the table creation statement, insert statements to populate with data and the full code required to process it -- along with the output you expect and the output you are getting. Please make it as simple as possible so we don't have to wade through piles of text! Thanks.

[markddrake]

Anthony

The SQL setup is the standard Oracle Sample Schemas. I thought that would make life simple
See https://github.com/oracle/db-sample-schemas

The reason there is a lot of text in the output is that JSON.stringify has serialized the instance of the oracledb lob object class at the content of the attribute "FORMATTED_DOC'" rather than the contents of the LOB. In this case I would expect to see a HeXBinary representation of the content of the LOB as the value of "FORMATTTED_DOC", since the attribute is of type BLOB. If the attribute were of type CLOB then I would expect to see the contents of the CLOB.

[anthony-tuininga]

Ok. I don't use those sample schemas much myself! In any case, what I am seeing is that you are simply dumping the output of the call to connection.execute(). The LOB values are returned as LOB instances, not LOB data. You can use the fetchAsString and fetchAsBuffer options to retrieve the actual data in the result rather than LOB instances. If you want to use the LOB instances you'll need to use streaming reads to get the data. See the documentation on LOBs for more information.

Instead of using JSON.stringify() which produces a nasty set of text which is hard for human beings to read, perhaps I can suggest you use this instead: console.dir(result, { depth: null }). That produces pretty printed output which is much easier to follow. :-)

[markddrake]

Anthony

I modified my code as follows

"use strict"
const util = require('util')
const oracledb = require('oracledb');
oracledb.fetchAsBuffer [oracledb.BLOB]
oracledb.fetchAString [oracledb.CLOB]

async function main() {

  const connectionDetails = {
    user          : "system",
    password      : "oracle",
    connectString : "YDB21903"
  };
  
  const sql = 
`select AD_TEXTDOCS_NTAB from PM.PRINT_MEDIA where rownum < 2`;    
  try {
      
    const conn = await oracledb.getConnection(connectionDetails);
	const results = await conn.execute(sql);
	console.log(util.inspect(results,{colors:true}))
	
 } catch(e) {
   console.log('Failed',e);    
 } 
}
main();

And I get this..

{ metaData: [ { name: 'AD_TEXTDOCS_NTAB' } ],
  rows:
   [ [ [PM.TEXTDOC_TAB] [ [PM.TEXTDOC_TYP] { DOCUMENT_TYP: 'Simple Text (.txt)',
           FORMATTED_DOC:
            Lob {
              _readableState:
               ReadableState {
                 objectMode: false,
                 highWaterMark: 16384,
                 buffer: BufferList { head: null, tail: null, length: 0 },
                 length: 0,
                 pipes: null,
                 pipesCount: 0,
                 flowing: null,
                 ended: false,
                 endEmitted: false,
                 reading: false,
                 sync: true,
                 needReadable: false,
                 emittedReadable: false,
                 readableListening: false,
                 resumeScheduled: false,
                 paused: true,
                 emitClose: true,
                 autoDestroy: false,
                 destroyed: false,
                 defaultEncoding: 'utf8',
                 awaitDrain: 0,
                 readingMore: false,
                 decoder: null,
                 encoding: null },
              readable: true,
              _events: [Object: null prototype] { finish: [Function] },
              _eventsCount: 1,
              _maxListeners: undefined,
              _writableState:
               WritableState {
                 objectMode: false,
                 highWaterMark: 16384,
                 finalCalled: false,
                 needDrain: false,
                 ending: false,
                 ended: false,
                 finished: false,
                 destroyed: false,
                 decodeStrings: false,
                 defaultEncoding: 'utf8',
                 length: 0,
                 writing: false,
                 corked: 0,
                 sync: true,
                 bufferProcessing: false,
                 onwrite: [Function: bound onwrite],
                 writecb: null,
                 writelen: 0,
                 bufferedRequest: null,
                 lastBufferedRequest: null,
                 pendingcb: 0,
                 prefinished: false,
                 errorEmitted: false,
                 emitClose: true,
                 autoDestroy: false,
                 bufferedRequestCount: 0,
                 corkedRequestsFree: [Object] },
              writable: true,
              allowHalfOpen: true,
              offset: 1 } },
         [PM.TEXTDOC_TYP] { DOCUMENT_TYP: 'Rich Text (.rtf)',
           FORMATTED_DOC:
            Lob {
              _readableState:
               ReadableState {
                 objectMode: false,
                 highWaterMark: 16384,
                 buffer: BufferList { head: null, tail: null, length: 0 },
                 length: 0,
                 pipes: null,
                 pipesCount: 0,
                 flowing: null,
                 ended: false,
                 endEmitted: false,
                 reading: false,
                 sync: true,
                 needReadable: false,
                 emittedReadable: false,
                 readableListening: false,
                 resumeScheduled: false,
                 paused: true,
                 emitClose: true,
                 autoDestroy: false,
                 destroyed: false,
                 defaultEncoding: 'utf8',
                 awaitDrain: 0,
                 readingMore: false,
                 decoder: null,
                 encoding: null },
              readable: true,
              _events: [Object: null prototype] { finish: [Function] },
              _eventsCount: 1,
              _maxListeners: undefined,
              _writableState:
               WritableState {
                 objectMode: false,
                 highWaterMark: 16384,
                 finalCalled: false,
                 needDrain: false,
                 ending: false,
                 ended: false,
                 finished: false,
                 destroyed: false,
                 decodeStrings: false,
                 defaultEncoding: 'utf8',
                 length: 0,
                 writing: false,
                 corked: 0,
                 sync: true,
                 bufferProcessing: false,
                 onwrite: [Function: bound onwrite],
                 writecb: null,
                 writelen: 0,
                 bufferedRequest: null,
                 lastBufferedRequest: null,
                 pendingcb: 0,
                 prefinished: false,
                 errorEmitted: false,
                 emitClose: true,
                 autoDestroy: false,
                 bufferedRequestCount: 0,
                 corkedRequestsFree: [Object] },
              writable: true,
              allowHalfOpen: true,
              offset: 1 } },
         [PM.TEXTDOC_TYP] { DOCUMENT_TYP: 'Word File (.doc)',
           FORMATTED_DOC:
            Lob {
              _readableState:
               ReadableState {
                 objectMode: false,
                 highWaterMark: 16384,
                 buffer: BufferList { head: null, tail: null, length: 0 },
                 length: 0,
                 pipes: null,
                 pipesCount: 0,
                 flowing: null,
                 ended: false,
                 endEmitted: false,
                 reading: false,
                 sync: true,
                 needReadable: false,
                 emittedReadable: false,
                 readableListening: false,
                 resumeScheduled: false,
                 paused: true,
                 emitClose: true,
                 autoDestroy: false,
                 destroyed: false,
                 defaultEncoding: 'utf8',
                 awaitDrain: 0,
                 readingMore: false,
                 decoder: null,
                 encoding: null },
              readable: true,
              _events: [Object: null prototype] { finish: [Function] },
              _eventsCount: 1,
              _maxListeners: undefined,
              _writableState:
               WritableState {
                 objectMode: false,
                 highWaterMark: 16384,
                 finalCalled: false,
                 needDrain: false,
                 ending: false,
                 ended: false,
                 finished: false,
                 destroyed: false,
                 decodeStrings: false,
                 defaultEncoding: 'utf8',
                 length: 0,
                 writing: false,
                 corked: 0,
                 sync: true,
                 bufferProcessing: false,
                 onwrite: [Function: bound onwrite],
                 writecb: null,
                 writelen: 0,
                 bufferedRequest: null,
                 lastBufferedRequest: null,
                 pendingcb: 0,
                 prefinished: false,
                 errorEmitted: false,
                 emitClose: true,
                 autoDestroy: false,
                 bufferedRequestCount: 0,
                 corkedRequestsFree: [Object] },
              writable: true,
              allowHalfOpen: true,
              offset: 1 } } ] ] ] }

C:\Development\YADAMU>

[anthony-tuininga]

You're missing the equals sign in your code. You need this:

oracledb.fetchAsBuffer = [oracledb.BLOB];
oracledb.fetchAString = [oracledb.CLOB];

[markddrake]

Sorry I missed that, but it makes no difference..

"use strict"
const util = require('util')
const oracledb = require('oracledb');
oracledb.fetchAsBuffer = [oracledb.BLOB]
oracledb.fetchAsString = [oracledb.CLOB]

async function main() {

  const connectionDetails = {
    user          : "system",
    password      : "oracle",
    connectString : "YDB21903"
  };
  
  const sql = 
`select AD_TEXTDOCS_NTAB from PM.PRINT_MEDIA where rownum < 2`;    
  try {
      
    const conn = await oracledb.getConnection(connectionDetails);
	const results = await conn.execute(sql);
	console.log(util.inspect(results,{colors:true}))
	
 } catch(e) {
   console.log('Failed',e);    
 } 
}
main();

[anthony-tuininga]

Can you try this example? This demonstrates the use of fetchAsString and fetchAsBuffer. If that works for you then you can find out what you did wrong.

[markddrake]

Anthony the scenario I am describing is different. In my case the LOB is an attribute of a PL/SQL Type, not a column in the database. If you look at the describe of the table the column I am fetching is

AD_TEXTDOCS_NTAB PM.TEXTDOC_TAB

Which is a PL/SQL Collection Type. The collection is defined as

QL> desc PM.TEXTDOC_TAB
PM.TEXTDOC_TAB TABLE OF PM.TEXTDOC_TYP
Name Null? Type

---

DOCUMENT_TYP VARCHAR2(32)
FORMATTED_DOC BLOB

QL>

So it is a collection on PM.TEXTDOC_TYP objects.

Each TEXTDOC_TYP object has an attribute call FORMATTED_DOC of type BLOB.

The error I am seeing is when I try to serialize the contents of D_TEXTDOCS_NTAB column...

[anthony-tuininga]

Ah! That makes more sense. Database objects are not affected by fetchAsString and fetchAsBuffer. You will need to get the data from the LOB yourself directly. Something like this:

await obj.TEXTDOC_TYP.FORMATTED_DOC.getData()

There is no other way to deal with this at the moment. We are considering a means for returning the objects as plain JavaScript objects and expanding the LOB values directly, but have not implemented such yet. It can't be simply included in the object serialization as it requires a round trip to the database to get the data.

[markddrake]

Unfortunately since I am writing generic code that has to handle any object without knowing it's structure this is not really feasible in my particular use case. Combined with not yet supporting type hierarchies I'll have to roll my own PL/SQL based solution for the moment.

[anthony-tuininga]

Understoood. I'm going to mark this as an enhancement now that I actually understand what you were trying to do!