Node-oracledb: Stored procedure - IN type CLOB

[kgajowy]

Hello,

I am currently experiencing issue with executing stored procedure which has CLOB as input parameter.

Table:

--------------------------------------------------------
--  DDL for Table TRANSCRIPT
--------------------------------------------------------

  CREATE TABLE "MSE_ORA_D"."TRANSCRIPT" 
   (    "TRANSCRIPT_ID" NUMBER, 
    "CONFIG_ITEM_NAME" VARCHAR2(4000 BYTE), 
    "TRANSCRIPT" CLOB, 
    "DEBUG" CLOB, 
    "START_TIME" TIMESTAMP (0), 
    "END_TIME" TIMESTAMP (0), 
    "ERROR_MSG" VARCHAR2(4000 BYTE), 
    "SUCCESS" CHAR(1 BYTE), 
    "PARAMETRIZATION_ID" NUMBER
   ) 

Procedure:

create or replace PROCEDURE SP_ADD_TRANSCRIPT(
  SENT_CONFIG_ITEM_NAME IN TRANSCRIPT.CONFIG_ITEM_NAME%TYPE,
  SENT_TRANSCRIPT IN TRANSCRIPT.TRANSCRIPT%TYPE,
  SENT_DEBUG IN TRANSCRIPT.DEBUG%TYPE,
  SENT_START_TIME IN VARCHAR2,
  SENT_END_TIME IN VARCHAR2,
  SENT_ERROR_MSG IN TRANSCRIPT.ERROR_MSG%TYPE,
  SENT_IS_SUCCESS IN CHARACTER DEFAULT NULL,
  SENT_OCCURRENCES_IDS IN VARCHAR2 DEFAULT NULL,
  SENT_PARAMETRIZATION_ID IN CONFITEM_INSTANCE_ON_SERVER.PARAMETRIZATION_ID%TYPE DEFAULT NULL
)AS
v_start timestamp;
v_end timestamp;
BEGIN
--...
END SP_ADD_TRANSCRIPT;

Node.js code:

connection.execute(
        statement,
        options,
        [],
        function (err, result) {
            console.log("execution done", err, result);
            connection.release(function (err_release) {
                //notify somehow!
                if (err_release) {
                    console.log("ERROR > RELEASE CONNECTION", err_release);
                }
            });
            callback(err, result);
        });

Statement (executed in Node.js):

BEGIN SP_ADD_TRANSCRIPT(:SENT_CONFIG_ITEM_NAME,:SENT_TRANSCRIPT,:SENT_DEBUG,:SENT_START_TIME,:SENT_END_TIME,:SENT_ERROR_MSG,:SENT_IS_SUCCESS,:SENT_OCCURRENCES_IDS,:SENT_PARAMETRIZATION_ID); END;

Options:

{ SENT_CONFIG_ITEM_NAME: { val: 'Cos tam cos', dir: 3001, type: 2001 },
  SENT_TRANSCRIPT: { val: 'jakeis tam wiadomosci', dir: 3001, type: 2006 },
  SENT_DEBUG: { val: 'informacje dodatkowe', dir: 3001, type: 2006 },
  SENT_START_TIME: { val: '1441274497', dir: 3001, type: 2001 },
  SENT_END_TIME: { val: '1441275497', dir: 3001, type: 2001 },
  SENT_ERROR_MSG: { val: '', dir: 3001, type: 2001 },
  SENT_IS_SUCCESS: { val: '1', dir: 3001, type: 2001 },
  SENT_OCCURRENCES_IDS: { val: '', dir: 3001, type: 2001 },
  SENT_PARAMETRIZATION_ID: { val: '903712', dir: 3001, type: 2001 } }

Error produced:

[Error: NJS-011: encountered bind value and type mismatch in parameter 2]

which is obviously a CLOB (this is, 2nd parameter). type 2006 == oracledb.CLOB;

oracledb version: 1.1
node version: v0.12.7
arch: 32bit
OS: Windows

Could you please assist?

//edit

create or replace PROCEDURE NODE_CLOB_TEST(
  inVarchar IN VARCHAR2,
  inClob IN CLOB
)AS
vDummy integer;
BEGIN
  select 1 into vDummy from dual;
END NODE_CLOB_TEST;

var statement = "BEGIN NODE_CLOB_TEST(:inVarchar, :inClob); END;";
var options = {
    inVarchar : 'test',
    inClob : {
        val : 'test2',
        dir : 3001,
        type: 2006
    }
};

connection.execute(
        statement,
        options,
        [],
        function (err, result) {
            console.log("execution done", err, result);
            connection.release(function (err_release) {
                if (err_release) {
                    console.log("ERROR > RELEASE CONNECTION", err_release);
                }
            });
            callback(err, result);
        });

[cjbj]

Thanks for the detailed testcase - I wish everyone was as thorough.

LOBs can currently only be bound as OUT parameters (see the documentation). We have not yet looked at IN support.

[doberkofler]

This would be an incredibly important feature for me as well.

[mchavarriae]

Is there any way to send a very large string to the DATABASE? I can't use a varchar2

[cjbj]

@mchavarriae there are examples of inserting LOBs with INSERT in https://github.com/oracle/node-oracledb/tree/master/examples. Also see the node-oracledb doc Working with CLOB and BLOB Data

[sagiegurari]

@mchavarriae see https://github.com/sagiegurari/simple-oracledb#usage-insert for INSERT with string to CLOB support.

[mchavarriae]

Thanks for the help, but I need to call an stored procedure who receives a CLOB as IN parameter.

[sagiegurari]

@cjbj stated it is not supported yet. I think you won't get it in 1.4 either.

[steunix]

+1 for me.

Lack of CLOB IN binding for procedures is pretty awkward and limiting. I make extensive use of CLOB for logging pourposes, and 4k VARCHAR2 are not enough...

[wmertens]

Argh, I just noticed that this is not supported. +1 for me too, and could you put in the Readme that IN LOBs are not supported?

[wmertens]

I had to do an update, not an insert, so had to do

UPDATE mytable
SET myblobfield = EMPTY_BLOB()
WHERE myid = :id
RETURNING myblobfield INTO :blob`

with parameters { id, blob: { dir: oracledb.BIND_OUT, type: oracledb.BLOB, }, }

and then process the result from the execution with

(result, conn) => new BP((resolve, reject) => {
        if (result.rowsAffected !== 1 || result.outBinds.blob.length !== 1) {
            return BP.reject('Error getting a LOB locator')
        }
        stream.on('end', () => resolve(BP.fromNode(cb => conn.commit(cb))))
        stream.on('error', err => reject(err))

        const blob = result.outBinds.blob[0]
        blob.on('error', err => reject(err))

        // send stream to blob, on completion will complete promise
        stream.pipe(blob)
    })

(I made a wrapper that gets a connection from the pool and waits for the promise of the processing function to resolve before closing the connection)

[KmlSch]

We are using clobs for passing json into db. We had to go back from node to PHP beacuse driver wont work with clob in param > 4k. So very important feature for us.

[metavine]

Just out of curiosity, is there any particular reasons that CLOB/BLOB IN parameter for stored procedure hasn't been supported?

[cjbj]

@metavine the answer is simply time.

[metavine]

@cjbj Would that be possible for you to say this feature will be implemented in the near future like 3 months, 6 months, ..., something like that?

Thanks.

[cjbj]

@metavine Once 1.9.1 is out we'll do a review of priorities. Getting prebuilt Windows binaries is still top of my list.

[metavine]

@cjbj Great. Hopefully, this will go to the top of your list.

Regards,

[wmertens]

@metavine note that the workaround works fine, and it's not that much code…

[metavine]

Hi @wmertens , thanks for the note, but my situation won't allow me to apply the "workaround" method for the call.

[tttest25]

+1

[khanhhaduc]

+1

[emoracle]

+1

[gavinmarkglynn]

Hi Guys.

For our current project, the lack of "in" parameter support for CLOB/BLOBs means I can't implement a database integration layer in nodejs. I am going to have to resort to writing my own layer in C++, which leads to additional deployment headaches.

The reality is making these "in" parameter CLOB/BLOB calls is straightforward to do in C++ with OCI. For the time you have spent discussing the issue with the group, one of your developers could have had the feature implemented. As a developer manager, I would estimate less than a days work. Maybe it is time to just get this one done and move on.

Regards

Gavin Glynn :)

[cjbj]

It's certainly high on my list. If you are are working on this, you could consider contributing an implementation? My time estimate is very different to yours.

[Jongchana-P]

+1

[cjbj]

Coming soon...

[cjbj]

node-oracledb 1.12.0-dev on GitHub has LOB bind support. Check out the doc . Please try it out and let give us feedback so we can freeze and release to npm

[MobileAppVault]

Hi @cjbj,

this is currently not working with 1.12.2

"bindVars":
"pi_CLOB_XML": {
"val": "hello world",
"type": oracledb.CLOB,
"dir": oracledb.BIND_IN
}

Error: NJS-011: encountered bind value and type mismatch in parameter 2

Do I have to test it on 1.12.0-dev ?

[MobileAppVault]

Sorry this works when I replace the type oracledb.CLOB to oracledb.STRING