node-oracledb
node-oracledb copied to clipboard
oracle
Serialized LOB Object output instead of content when object contains a LOB
See https://www.oracle.com/corporate/security-practices/assurance/vulnerability/reporting.html for how to report security issues.
-
With the async/await programming style, make sure you are using 'await' in the right places.
-
Is it an error or a hang or a crash? Error
-
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
-
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'
>
- What is your Oracle Database version? 18,19,20
Given that the FORMATTED_DOC is a BLOB I would have expected a HexBinary representation of the LOB in the JSON object.
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.
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.
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. :-)
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>
You're missing the equals sign in your code. You need this:
oracledb.fetchAsBuffer = [oracledb.BLOB];
oracledb.fetchAString = [oracledb.CLOB];
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();
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.
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...
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.
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.
Understoood. I'm going to mark this as an enhancement now that I actually understand what you were trying to do!
It is worth considering whether conversion to JSON should not always be performed by runtime environment methods. For Node:
import { json } from 'node:stream/consumers';
const data = await json( blob );
If you are referring to converting database objects to "pojo" (plain old javascript object), then that is already available. However, trying it with this scenario seems to indicate that the LOB values are not transformed -- which should be done, I think! Another enhancement to implement. :-)
For some cases, the node-oracledb 6.0 fetch type handler converter functionality could be used. This would be easiest where the object structure is known in advance and your converter can directly fetch the LOB data using the attribute names like await obj.TEXTDOC_TYP.FORMATTED_DOC.getData() but it should be possible to write a converter that traverses the object and fetches data for any LOB attribute seen.
