Re: Strange behaviour calling stored procedure from Pro*C

On 13 Feb 2006 07:31:13 -0800, "william" <ewilliam_at_singnet.com> wrote:

>For small number of records, around 1000-3000 per file, summary loading
>has no problem. Is there some limit to for SQL transaction to do
>commit... for example after inserting 1000, the program must do a
>commit? where does my inserted records go before doing a commit? it
>seems I have inserted everything but when commiting it looks like not
>all records are made permanent in the table.
>
>When I rerun the loading, some of the 1000 records missing is loaded in
>but some of the 5000 records loaded in is now missing.
>
>> Finally, you are aware your approach is very inefficient?
>> At least you should use BULK-INSERT, but better still you should set
>> up an appropiate external table, and just MERGE the external table
>> into the destination table.
>
>
>if you think the approach is not efficient, what's the alternative?
>Really need help.. thanks.. hope didn't confuse you further

You actually did, because the picture is still not yet complete. And of course, 15000 records is nothing, but they shouldn't be dealt with record for record.
Your approach is not only inefficient, it is also dangerous, as Oracle has statement level rollback by default. As soon as an exception is raised, you already got *statement* level rollback. So if you commit afterwards, all the other records are committed, except for the records that ended up in error.
Also there is NO limit at all for SQL transaction to commit, of course, that is : unless you run out of UNDO tablespace. But as you are suppressing all proper error handling, you won't ever see that. You are supressing error handling, as you do pass the error message to the calling level, but you don't sprintf it to stdout anywhere. So in you case: ignorance is bliss. You simply don't know what is going on, as your program supresses everything. And evidently, assuming those threads are separate Oracle connections, prior to committing, the other threads won't see anything.

The alternative of course is, as I already outlined,

create table ...
organization external

This is to make sure Oracle can directly SELECT from your ASCII file. Look up 'external tables' in your documentation. Secondly you need to use the MERGE statement to UPDATE existing records, and INSERT new ones. Actually *exactly* what your procedure does for one single record. MERGE will operate on the complete table/file. And then it should be really a piece of cake, and as everything is done serverside, it will be much more efficient.

--
Sybrand Bakker, Senior Oracle DBA