When I had to do this over ten years ago, I use the Ingres' bcp utility. Does Oracle have a similar tool that you could use instead of writing a Perl script?

Yes, sqlldr.

I'm trying to populate fresh data into the DB. So, I don't have any source from where I can simply dump it using sqlldr.

I cordially advise that you are going about this the wrong way ... that “parallel tasks/threads” will not make things go faster, but substantially slower.   You should be using the bulk-loading tool that Oracle provides for this purpose, since it is optimized to do exactly this.

If you find that for whatever reason you do need to load large amounts of data into an SQL database (of any sort) by conventional means, there are several things that I suggest you consider:

1. Parallelism is probably not your friend, probably will not help, because it just makes more work for the SQL engine at the other end of the wire.
2. You must do the work within a transaction, and you should carefully choose that transaction’s “isolation level.”   (This sort of work needs to push other concurrent work aside as much as possible, through an aggressive choice of isolation level, which is why it is often done in the wee hours...)   Post a few thousand records, say, then commit, then open up another one.   (This will allow the database to do “lazy writing.”)   prepare the statement-handle, with placeholders, and use it repeatedly.
3. Consider how the process can be made restartable.   Once you have successfully committed, record where you are in the input-file so that you could potentially restart at that checkpoint if the program should unexpec...   ;-)

But, once again, there are “bulk loader” programs that are specifically designed to do this.

I'd look into what these two guys said above...
To avoid changing your code too much, just do all your data preparation as you currently do, but rather than executing the resutling INSERT SQL through the handle, append it to a file(s) or print it to STDOUT (and redirect it to a file) then push the resulting SQL files to your DB's buck loader.

Hope that gives you some ideas...

"then push the resulting SQL files to your DB's buck loader."

A minor point, bulk loaders don't take sql files as input. They load text formatted files, for example CSV.

Thanx monx... This will definitely help me to tackle my situation.

You are in a world of pain and I doubt you will insert one row at a time using Parallel::ForkManager any quicker than inserting all the rows in one process. You should probably look for some sort of Oracle bulk insert program of look at execute_for_fetch/execute_array in DBI (both are which are natively implemented in DBD::Oracle).

If you want to persue P::FM then you at least need to know that a) as soon as the first child exits, your DBI handle will be destroyed b) when child processes exit they often cause a SIGCHLD to be sent to the parent and Oracle client does things by default with SIGCHLD. You can solve (a) by setting AutoInactiveDestroy on your DBI handle. (b) is trickier as you have already found in your search.

Does each fork create it's own database connection ?

How do you insert the records? one at a time or in batches? If you're looking for speed it's usually best to batch your inserts into one transaction. So turn autocommit off and handle them yourself.

Yes. I'm opening a DB connection for each thread and closing the connection. When I run the script... it just runs fine for first few seconds.... and then throws the above error...

Wow. That's a completely brutal way of doing it. Think of how much work is involved in setting up a database connection, doing one insert, and then tearing down that connection.

A better way might be to split the data into 20 chunks, and then pass each of the chunks to a separate process.

Alex / talexb / Toronto

Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Perhaps this might be also interesting: SQL*Loader FAQ

Regards, Karl