New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How about LOB type support? #19

Closed
zorrofox opened this Issue Feb 11, 2015 · 46 comments

Comments

Projects
None yet
@zorrofox
Copy link

zorrofox commented Feb 11, 2015

Hi,
I know node-oracledb has many new features to be support in schedule, but I just want to know the LOB type will be on the top of the list? Because we want to use this feature in our Apps.

@cjbj cjbj added the enhancement label Feb 11, 2015

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Feb 11, 2015

LOB support is high on our list.

@cjbj cjbj self-assigned this Feb 11, 2015

@hexkode

This comment has been minimized.

Copy link

hexkode commented Feb 18, 2015

Would love to see LOB supports as well.
LOB is a requirement when using the new oracle 12c JSON feature with large JSON data objects.

Thanks!

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Feb 18, 2015

@hexkode It's being worked on. How are you accessing and what are you doing with LOBs in Node.js?

@hexkode

This comment has been minimized.

Copy link

hexkode commented Feb 18, 2015

I'm currently using the node-oracle module but is encountering issue with inconsistent results being returned when working with large data. joeferner/node-oracle#143

In my app I just need to be able to read and write the entire JSON document stored in a CLOB column.

There is currently no need to use JSON Path Expression.

Example table for storing JSON documents:
CREATE TABLE json_doc_test (
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
json_doc CLOB
CONSTRAINT json_doc_chk CHECK (json_doc IS JSON)
);

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Feb 18, 2015

@hexkode How big are your typical LOBS?

@hexkode

This comment has been minimized.

Copy link

hexkode commented Feb 18, 2015

@cjbj Typical CLOBS ranges from 10kB to 250KB.

FYI: The data inconsistency issue occurs when the data reaches around 9KB for the node-oracle driver (https://github.com/joeferner/node-oracle)

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Feb 18, 2015

@hexkode what are your Oracle NLS settings in the DB and for Node.js? I wonder if that factors into it?

@hexkode

This comment has been minimized.

Copy link

hexkode commented Mar 3, 2015

@cjbj I decided to abandon the node-oracle driver and switch over to the vendor driver (node-oracledb 0.3). Currently using varchar2 (32000) for storing JSON, no inconsistency issues there.

Will most likely exceed the 32k limit in the next phase of the project. hoping to see CLOB support by then. Do you think CLOB support will make it in the next release in the next month or 2? thanks!

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Mar 3, 2015

@hexkode LOBS are being worked on - but so are several other things like RETURNING INTO. Sorry I can't commit to timeframes.

@hellboy81

This comment has been minimized.

Copy link

hellboy81 commented Mar 30, 2015

what about OCCICLOB output parameters support? Work still in progress?

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Mar 30, 2015

@hellboy81 yes, still WIP.

@highflying

This comment has been minimized.

Copy link

highflying commented Apr 7, 2015

+1

@hellboy81

This comment has been minimized.

Copy link

hellboy81 commented Apr 8, 2015

+infinity

@jordabi

This comment has been minimized.

Copy link

jordabi commented Apr 21, 2015

@cjbj I know you don't want to commit to s date, but can you throw me bone and speculate if you believe this will be implemented within, say the next 60 days? If no, then please just say so, so that I may begin working on a different solution. Thanks.

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Apr 21, 2015

@jordabi I really can't give guidance - there are too many variables, a lot of them related to non Node.js projects, that may impact timing.

@dmcghan

This comment has been minimized.

Copy link
Member

dmcghan commented Apr 21, 2015

@jordabi Though not ideal, could you tell us a little more about what you're doing? Perhaps we could come up with a temporary workaround that you could use until proper LOB support is added?

@jordabi

This comment has been minimized.

Copy link

jordabi commented Apr 22, 2015

@dmcghan I'm not doing anything special, I just need to store large text strings (JSON) and retrieve them. I have basically created a db abstraction layer around our existing mongoDb implementation as a customer needs us to persist data to their Oracle 11 instance. I was hoping that this functionality would be complete prior to us delivering to them so I did not need to work around the issue. If it is not going to be, then I will simply split the text into multiple rows and abstract that from the user/APIs.

@dmcghan

This comment has been minimized.

Copy link
Member

dmcghan commented Apr 22, 2015

@jordabi I would recommend doing things in such a way that moving to the builtin LOB support later is easier. The limitation in your case is in the size of the bindings in the driver, not the size of the rows in the database. For that reason, I wouldn't recommend splitting the text into multiple rows. I would recommend using multiple VARCHAR2s for bindings.

When data is going from Node.js to Oracle Database you'd serialize the JSON and then break it into pieces, bind the pieces via the driver, then join the pieces back together before inserting them. Of course when going from Oracle Database to Node.js you would do the reverse.

The implementation could be done serval ways different ways. Checkout this post for an example of how I overcame the limitation to send images through the driver: https://jsao.io/2015/03/throttled-file-loading-with-node-js-oracle-database/

In my demo I needed to do base64 encoding/decoding that I don't think you'll need to do so it should be a bit simpler for you. Let me know if you have any questions - I'm happy to help!

@andrewbrereton

This comment has been minimized.

Copy link

andrewbrereton commented May 6, 2015

+1

@marlic7

This comment has been minimized.

Copy link

marlic7 commented May 11, 2015

It would be perfect to me, if normal SELECT could fetch CLOB field.

Max bytes should be driver parameter, if CLOB size in record exceeded max bytes param then driver should throw error (with NJS prefix or similar).

This is only one critical missing feature that holds me from using this wonderful driver.

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented May 11, 2015

@marlic7 thanks for the comments. Is there anything in particular that makes the driver wonderful for you?

@marlic7

This comment has been minimized.

Copy link

marlic7 commented May 12, 2015

It's simple, stable (in my test), from Oracle, and has pool implementation, i think its enough :-)

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented May 12, 2015

@marlic7 thanks for the comment.

@atiertant atiertant referenced this issue May 15, 2015

Closed

adapter status? #2

@cjbj cjbj referenced this issue May 21, 2015

Closed

BLOB Support #101

@mtikmani

This comment has been minimized.

Copy link

mtikmani commented Jun 12, 2015

relly need CLOB support.

Murari,
An Oracle Employee :)

@nelreina

This comment has been minimized.

Copy link

nelreina commented Jun 12, 2015

+1

@hellboy81

This comment has been minimized.

Copy link

hellboy81 commented Jun 12, 2015

-infinity

@tmanolat

This comment has been minimized.

Copy link

tmanolat commented Jun 16, 2015

really need CLOB support please

@tmanolat

This comment has been minimized.

Copy link

tmanolat commented Jun 18, 2015

Maybe an intermediate solution could be (I haven't check this myself) to:

@randomsock

This comment has been minimized.

Copy link

randomsock commented Jun 19, 2015

Any ETA on this? This is now a BLOCKER for us, and the original node-oracle is no longer viable.

In our case, the CLOBs are not huge, just too big for VARCHAR2s - 64K is a technical limit from elsewhere, but in reality not exceeding 10K anyway.

Of course, any suggestions for a viable work around would be gratefully received. Note though that this is a legacy corporate db so changes at that end would not be an acceptable option (risk, as much as anything).

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Jun 20, 2015

I can't give an ETA - there are too many variables. Last time I hinted a feature was near, it got delayed.
I can say that when code is released it will support CLOB only at first. BLOBs will be looked at later.
Until there is native support for CLOB, you could convert smaller CLOBs to VARCHAR via SQL or PL/SQL. There are various solutions on the web. Dan's post #19 (comment) is worth looking at too.

@randomsock

This comment has been minimized.

Copy link

randomsock commented Jun 20, 2015

Sure. Understood. Because ours are small I've sliced the CLOB into 4 x 4000 in the SQL for now and concatenated again client side.

Appreciate the pressure you're under - that's what you get for doing a good job :)

@SargoDarya

This comment has been minimized.

Copy link

SargoDarya commented Jul 14, 2015

Blocker for us now too. It's the only reason we have to use PHP at the moment for syncing

@hellboy81

This comment has been minimized.

Copy link

hellboy81 commented Jul 14, 2015

Oracle is so Oracle...

@jordabi

This comment has been minimized.

Copy link

jordabi commented Jul 14, 2015

This has been a blocker for months now. This driver seemed promising and we had adopted it early on. That being said, we had to abandon it about a month ago for this reason. We chose to go with node-oracle (which is no longer maintained due to this project) until such time as this project becomes mature enough to use in a production system. Hopefully that won't take too much longer. We have had no issues with the "legacy" driver, and it does support LOBs quite well.

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Jul 14, 2015

@jordabi you highlight a risk we took when we released the first
preview to get user feedback and allow developers with simpler
requirements to make use of the driver.

@SargoDarya, @jordabi
What exact LOB support do you need? CLOB? BLOB? BFILE? Are you
simply inserting/selecting? Are you binding LOBs variables? Do you
need Oracle LOB locator operations like trim, erase, copy, append?

@tmanolat

This comment has been minimized.

Copy link

tmanolat commented Jul 15, 2015

If the CLOB is rather small, say a 32K html article, a VERY dirty approach that seems to work is to split the CLOB into small chunks of eg 2000 characters, to actually split the CLOB in VARCHAR(4000) segments in your sql query.

And then simply concat the columns back in Javascript

For example:

select id,
dbms_lob.substr(clobcolumn, 2000,1) x1,
dbms_lob.substr(clobcolumn,2000,2001) x2,
dbms_lob.substr(clobcolumn,2000,4001) x3
from yourtable

Again, this is brutally dirty, but so far the only way for me to have a working approach...

EDIT: I just saw that @randomsock actually said the same

@sagiegurari

This comment has been minimized.

Copy link

sagiegurari commented Jul 15, 2015

@cjbj I believe the most needed use case would be CLOB type.
naturally select/insert/update.
that would solve my requirements.
Basically we store/fetch JSONs from oracle (don't ask me why we are not using mongo for that).

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Jul 15, 2015

@tmanolat don't forget that Oracle 12c has 32K varchars

@sagiegurari Thanks for the usage info. Since Oracle 12.1.0.2 has native JSON support, I can see good reasons why you would use Oracle for JSON.

I'm sure you all want a status report on LOBS. We have a good prototype. It needs tidying up and fixes. It needs a lot of testing. It won't be included in 0.7. Repeat: 'will not'.

@nelreina

This comment has been minimized.

Copy link

nelreina commented Jul 16, 2015

@sagiegurari You have to keep in mind that not every company will migrate there Oracle DB every release. So our company is still on the 11 version. And this package works fine on Windows and Linux.
But the CLOB support is essential for our business. Trying to convince the management to use NodeJS as a solution but with no LOB support is kinda hard to sell it for me.

As a work around I use our legacy java backend to fetch the LOB data.

@sagiegurari

This comment has been minimized.

Copy link

sagiegurari commented Jul 16, 2015

@nelreina I believe you are referring to @cjbj and not to me with that comment.

@nelreina

This comment has been minimized.

Copy link

nelreina commented Jul 17, 2015

@sagiegurari yes correct ! Sorry for the confusion :)

@Fufusulier

This comment has been minimized.

Copy link

Fufusulier commented Jul 29, 2015

Hello, I would like to use this driver has well but I need the support of the CLOB (inserting,updating,deleting,selecting). The migration to oracle 12c is not yet scheduled in the environment we are using.
Thank you for your work. :)

@randomsock

This comment has been minimized.

Copy link

randomsock commented Jul 30, 2015

In answer to your "What type ...?" question @cjbj (and to keep bumping this) I would add to @sagiegurari's vote that CLOB is probably most common and should be highest priority, certainly from here. For those of us tied to a corporate environment with no control over legacy backends, this is definitely something that needs to be supported in the client adapter.

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Jul 31, 2015

@randomsock @fufu-be we hear you.

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Aug 17, 2015

We added CLOB and BLOB support to node-oracledb 1.0, see https://github.com/oracle/node-oracledb/blob/master/doc/api.md#lobhandling
Note for PL/SQL parameters, only OUT binds are supported in this release. We will work on IN binds.

@rameshpalipi

This comment has been minimized.

Copy link

rameshpalipi commented Mar 4, 2016

Any update on support CLOB in IN binds...Please?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment