Bart Degryse wrote:
> I'm writing a function that fetches data in an Oracle database and stores it in postgresql database.
>
> The fetching from Oracle and the inserting in PostgreSQL both work correctly. I know this because with an empty
targettable and without an insert trigger the source data gets 'copied' perfectly to the target table.
>
> Of course the target won't always be empty. So the unique index might cause some inserts to fail. So I wanted to put
someerror handling in my function. To test what kind of information I would get I added an insert trigger to the target
table.This trigger raises to different errors based on the data inserted: if dataareaid is 'lil' an error is raised, if
dataareaid= 'bol' another error is raised, other values for dataareaid don't raise an error.
> So the plperl function should receive these error messages and handle them appropriatly. For this test the function
reportsthem just as info to the screen.
>
> When I 'manually' (without using the function and without interference of Oracle) insert a record that should trigger
theerror raising, the correct error is raised. When I use the function (see below) but change the query that fetches
theOracle data so that only one type of error is triggered (eg "SELECT * FROM AddressFormatHeading WHERE dataareaid =
'lil'"or "SELECT * FROM AddressFormatHeading WHERE dataareaid = 'bol'") the correct error message is passed from the
triggerto my function.
> However when both error triggering dataareaids exist in the set of data fetched from Oracle only one type of error
messageseems to get passed from the trigger to my function. Apparently it is the one that is caused first. Obviously
thatis not what I expect. This is what I get:
> executing 14 generated 4 errors
> ERROR: lil foutje Address Belgium
> ERROR: lil foutje Address Belgium
> ERROR: lil foutje Address Belgium
> ERROR: lil foutje Address Belgium
> And this is what I expect to get:
> executing 14 generated 4 errors
> ERROR: lil foutje Address Belgium
> ERROR: lil foutje Address France
> ERROR: bol nog een foutje Italie
> ERROR: bol nog een foutje Beglie
I don't think this is the cause, but you're using DBI->err|errstr - do
you not want $dbh_pg->err|errstr? Otherwise, you can't identify
different errors on the oracle vs pg connections.
Second - it's not just that your function does the inserts in the
context of a single transaction, is it? That would mean you're just
seeing the original error repeated.
--
Richard Huxton
Archonet Ltd
