Re: Strange behaviour calling stored procedure from Pro*C

On 11 Feb 2006 06:24:50 -0800, "william" <ewilliam_at_singnet.com> wrote:

>Hi,
>
>I have a multi-threaded pro*c program and one of the thread is calling
>a stored procedure to insert a single record in a partitioned table.
>The stored procedure is called with in a loop to insert all the records
>from a file.
>
>However, it seems that not all records are inserted when I query the
>partitioned table. As if it didn't execute the stored procedure.
>Strangely, this doesn't happen to a particular record but instead
>randomly when I reload the file. For example, record 20 (out of 5000)
>is not inserted and record 31 is inserted but after cleaning up the
>table and reloads the file, record 20 is inserted and record 31 is not.
>
>The table is partitioned by date (day to day basis) and all records
>belong to the same day. I noticed that the temp tablespace is full but
>does it affect anything in my loading to partitioned table?
>
>
>The weird thing is that there is no error returned from the stored
>procedure to indicated that the record failed to insert.
>
> Need help on this. Please..Please.. Thanks in advance.
>
>
>By the way I'm using 10g in AIX 5.3 os

[Quoted] This is one of the many many posts here where the problem description is so vague, that the post boils down to 'It doesn't work. HELP'. How can anyone help, when the actual code isn't posted and the problem description is full of riddles?

for example:
>However, it seems that not all records are inserted when I query the
>partitioned table.
How did you establishe this? By querying in a *different* session, prior to any commit in the session loading the data?

And what is
>Strangely, this doesn't happen to a particular record but instead
>randomly when I reload the file.
telling anyone who doesn't look over your shoulder? What is 'reloading the file'. Did you commit the data? Or did you just start the procedure again? Riddles, riddles!

And
>The weird thing is that there is no error returned from the stored
>procedure to indicated that the record failed to insert.
>
You agree we can't verify you have proper error trapping and we also can't verify whether you are not having
exception when others then null
in your code?

[Quoted] 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.

--
Sybrand Bakker, Senior Oracle DBA