How to get data back from oracle server with oraclebulkcopy

Question

5.00/5 (1 vote)

See more:

I'm trying to figure out if I can get data back from the server for rows that are inserted with OracleBulkCopy Class[^]

I can successfully load data from client to the server but the problem is that I cannot define a mapping that would return data back to the client from the server.

The situation is that the primary key get's it's value from a sequence on the server side so for further operations at client side, I would need the generated value back for each inserted row.

What I have tried:

Reading documentation and googling.

Posted 10-Dec-16 22:58pm

Wendelius

Add a Solution

Comments

Afzaal Ahmad Zeeshan 11-Dec-16 6:17am

If you know how many were inserted, can you do the SELECT TOP n ... ORDER BY pk DESC? Does that work?

Wendelius 11-Dec-16 6:29am

Thanks for the suggestion but I'm afraid that wouldn't work. There's no way to control parallel operations so the TOP query could return rows that are inserted by another, simultaneous connection.

Afzaal Ahmad Zeeshan 11-Dec-16 7:31am

Continuing from comments, right, I tried figuring it out, but figured that the WriteToServer functions return void. Poorly written API.

https://docs.oracle.com/cd/E17666_01/doc/win.112/e17357/OracleBulkCopyClass.htm#CHDHHABB
%ROWCOUNT also doesn't work. Major factor is this parallelism in this. I would suggest remove one factor — consider semaphores, locks etc. Otherwise this is a bit difficult problem.

Wendelius 11-Dec-16 7:49am

Parallelism is always present with databases, you never know who's connected and doing what, so we just have to live with that :)

I can work around this problem by using other approaches, but I'm just interested if this requirement can be satisfied with bulk copy.

Afzaal Ahmad Zeeshan 11-Dec-16 7:51am

Indeed, which can be minimized with semaphores. ;-)

Mehdi Gholam 11-Dec-16 8:24am

How about inserting into a "temp table" and moving the data when all ok.

Wendelius 11-Dec-16 8:47am

How would I get the generated keys back to client side for each row?

Mehdi Gholam 12-Dec-16 0:32am

How about adding a column like "bulk batch no"

Wendelius 12-Dec-16 0:48am

Yes, I've thought about that and it could be a solution. Not directly comparable to other technologies where each inserted row can be linked to an object from the client side but with a little tweak it could work...

It really seems that there's no way to get the data back and that there's no guarantee for the order of the insertion. So calling BulkCopy is just one-way traffic in this sense. Funny.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)