DB Loader not working with Oracle Connection

I am continuously getting this error while using DB Loader node to load data from a CSV file to an Oracle Database -

Configure failed (NullPointerException): Cannot invoke "org.knime.database.node.io.load.DBLoaderNode2Factory.get()" because the return value of "java.util.Map.get(Object)" is null

Knime Version - 5.2.3

Here is the image of the workflow -

Hi @dhruv_saarthee,

This DB Loader uses database specific bulk loading functionality that only some databases (e.g. Hive, MySQL, PostgreSQL and H2) support to load large amounts of data into an existing database table. KNIME Analytics Platform does not support bulk insertion into Oracle.

Can you try replacing the DB Loader node with the DB Writer node and check if it works.

Best,
Keerthan

2 Likes

Hey k10shetty1,
Thanks for your reply.
DB Writer works fine. But my objective was to load bulk data (nearly 40 million records) in the Oracle database. DB Writer was very slow in this respect. That’s why I turned towards DB Loader node. Any other solution?

Hi @dhruv_saarthee,

I found this thread explaing the issue discussed here. I hope this helps.

Best,
Keerthan

2 Likes

Thanks Keerthan.

You will be surprised to know that for me, when I tried to load bulk data using SQLLoader in the Oracle Database it took longer than DB Writer node. LOL. :laughing:
Also the tutorial mentioned in the post didn’t work for me. But a video tutorial on SQL
Loader on YouTube surely worked.
Thanks again for your time.

Hi @dhruv_saarthee and @k10shetty1 , I can see why the DB Loader won’t necessarily work with Oracle SQL Loader, but I’m surprised at the information in that post, saying that this is because Oracle SQLLoader must run on the Database Server machine*, rather than on a client. I haven’t used it in a few years, but that never used to be the case. I used to load data from my local machine.

However, unless it has been installed on the client machine, it won’t be available.

I unfortunately am not in a position to test out the theory, but I would expect that a solution similar to the one mentioned in that post could be achieved on a client machine if SQL Loader installed, without the need to transfer the files to the db server. (i.e. write a file locally containing the data to be loaded, and then execute an instruction to call SQL Loader to actually perform the upload).

It obviously wouldn’t be using the DB Loader node though.

I’m not in a position to test this out (currently no writeable Oracle database available to me, although I could try installing one at home later) but @dhruv_saarthee , do you have Oracle SQL Loader installed on your client? If so, can you try out using it directly (outside of KNIME) from your client machine for performing a small bulk load?

If it can be done, it should be possible to integrate calling it from the KNIME workflow, by executing a command line script. What OS are you using?

1 Like

Hi takbb,
I am using Windows OS and I have tried SQL*Loader. It is also slow as well.

OK. Unfortunately SQL*Loader is likely to be the fastest option since it is Oracle’s bulk-load utility, so even if KNIME could use DB Loader with Oracle, it is not likely to outperform Oracle’s product.

How slow is “slow”, as a matter of interest? Do you have a gauge for rows/minute?

Thinking this through, if it were possible to call sql*loader from KNIME, as far as I can see, it would have to pass the connection details (user/password) in clear text which probably wouldn’t be desirable in most circumstances!

3 Likes

Yes I measured the time taken by SQL*Loader. It was approx. 700K to 800K rows per minute.