FAQ
Oracle 11.2.0.1.0 (Linux x86-64).

I am writing stored procedure which copies information from one table to another, doing some transformations in the process. I am using the %rowtype construct for the fetches and inserts and doing a large number of assignment statements in the form:

rec1.col1 := rec2.cola;

Since there is a possibility of type conversion errors in some cases, I need to be able to trap errors and identify the offending column in the input table. The Oracle documentation suggests something like this:

step_num:=
rec1.col1 := rec2.cola;
step_num:=
rec1.col2 :=rec2.colb;

exception

when dbms_output.put_line('error at '||step_num);
raise;

end;

Is there a better way for me to identify the location of the error?

Thanks,
Peter Schauss

Search Discussions

  • David Fitzjarrell at Jun 9, 2011 at 7:21 pm
    You might think about using the format_error_backtrace and format_error_stack
    functions from dbms_utility:

    exception
    --
    -- Let's handle the exception here
    --
    when others then
    dbms_output.put_line('Displaying the error stack:');
    dbms_output.put(dbms_utility.format_error_stack);
    dbms_output.put_line(dbms_utility.format_error_backtrace);

    Here's an example:

    SQL> create or replace procedure myproc 2 is 3 begin 4 -- 5
    -- Informative text to the end user 6 -- 7
    dbms_output.put_line('Happily executing myproc'); 8 -- 9 --
    But let's raise an error anyway 10 -- 11 raise no_data_found;
    12 end; 13 /
    Procedure created.
    SQL> SQL> create or replace procedure yourproc 2 is 3 begin 4
    -- 5 -- Yet more informative text 6 -- 7
    dbms_output.put_line('Calling myproc'); 8 -- 9 -- A guaranteed
    error occurs here 10 -- 11 myproc; 12 end; 13 /
    Procedure created.
    SQL> SQL> create or replace procedure allproc 2 is 3 begin 4 --
    5 -- And another helpful message 6 -- 7
    dbms_output.put_line('Calling yourproc'); 8 -- 9 -- Call the
    proc which calls the proc 10 -- that generates the guaranteed 11
    -- error 12 -- 13 yourproc; 14 exception 15 -- 16
    -- Let's handle the exception here 17 -- 18 when others then 19
    dbms_output.put_line('Displaying the error stack:'); 20
    dbms_output.put(dbms_utility.format_error_stack); 21
    dbms_output.put_line(dbms_utility.format_error_backtrace); 22 end; 23 /
    Procedure created.
    SQL> SQL> -- SQL> -- Let's get this ball rolling SQL> -- SQL> -- The error
    text should point back to SQL> -- the source of the error (line 11 of myproc)
    SQL> -- SQL> SQL> exec allproc Calling yourproc Calling myproc Happily executing
    myproc Displaying the error stack: ORA-01403: no data found ORA-06512: at
    "BING.MYPROC", line 11 ORA-06512: at "BING.YOURPROC", line 11 ORA-06512: at
    "BING.ALLPROC", line 13
    PL/SQL procedure successfully completed.
    SQL>
    David Fitzjarrell

    ________________________________
    From: "Schauss, Peter (ESS)"
    To: "oracle-l@freelists.org"
    Sent: Thu, June 9, 2011 12:12:35 PM
    Subject: PLSQL exception handling

    Oracle 11.2.0.1.0 (Linux x86-64).

    I am writing stored procedure which copies information from one table to
    another, doing some transformations in the process.  I am using the %rowtype
    construct for the fetches and inserts and doing a large number of assignment
    statements in the form:

    rec1.col1 := rec2.cola;

    Since there is a possibility of type conversion errors in some cases, I need to
    be able to trap errors and identify the offending column in the input table.
    The Oracle documentation suggests something like this:

    step_num:=
    rec1.col1 := rec2.cola;
    step_num:=
    rec1.col2 :=rec2.colb;

    exception
    when dbms_output.put_line('error at '||step_num);
    raise;
    end;

    Is there a better way for me to identify the location of the error?

    Thanks,
    Peter Schauss
  • Stephane Faroult at Jun 9, 2011 at 8:01 pm
    Peter,

    What about :

    insert into new_table
    select duly_transformed_data
    from old_table
    log errors into errlog('migr')
    reject limit ... ?

    There are very few transformations that a good use of case ... end and a
    couple of functions cannot take care of, this would allow you to process
    massively without having to commit every line for fear of losing all the
    work done at the first error, then to fix all the errors at one and load
    again. Just what you would do with SQL*Loader, actually.

    My EUR 0.02.

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
    On 06/09/2011 09:12 PM, Schauss, Peter (ESS) wrote:
    Oracle 11.2.0.1.0 (Linux x86-64).

    I am writing stored procedure which copies information from one table to another, doing some transformations in the process. I am using the %rowtype construct for the fetches and inserts and doing a large number of assignment statements in the form:

    rec1.col1 := rec2.cola;

    Since there is a possibility of type conversion errors in some cases, I need to be able to trap errors and identify the offending column in the input table. The Oracle documentation suggests something like this:

    step_num:=
    rec1.col1 := rec2.cola;
    step_num:=
    rec1.col2 :=rec2.colb;

    exception
    when dbms_output.put_line('error at '||step_num);
    raise;
    end;

    Is there a better way for me to identify the location of the error?

    Thanks,
    Peter Schauss
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Gints Plivna at Jun 9, 2011 at 8:09 pm
    It seems you are doing some kind of data migration. I suggest you to
    try simple SQL approach if it is possible. Instead of pseudocode:
    For each record in source table:
    Copy it to pl/sql;
    Make transformation on column1;
    ...
    Make transformation on column n;
    Insert into target table
    End for;

    Better create some temporary table (or several temporary tables, might
    be global temporary tables or not, doesn't matter so much) with
    necessary structure and do something like:
    Copy all records from source into temporary table doing as much
    transformations already as possible;
    Do transformation 1 on all records;
    ...
    Do transformation n on all records;
    Insert all records into target table.

    If you need to fix some kind of logical error for each record why it
    fails transformations, then I suggest to use concept of validations -
    before making transformation mark all invalid records with your error
    number BEFORE transformation.
    Even if you'll scan all the data several times (for validations,
    transformations etc), it still will be better than doing row by row
    approach.
    I have done a few such migrations and even wrote an article about them
    http://www.gplivna.eu/papers/legacy_app_migration.htm

    Of course each case is different, but use records and cursors (even
    bulks and foralls) only as a last resort.

    Gints Plivna
    http://www.gplivna.eu

    2011/6/9 Schauss, Peter (ESS) :
    Oracle 11.2.0.1.0 (Linux x86-64).

    I am writing stored procedure which copies information from one table to another, doing some transformations in the process.  I am using the %rowtype construct for the fetches and inserts and doing a large number of assignment statements in the form:

    rec1.col1 := rec2.cola;

    Since there is a possibility of type conversion errors in some cases, I need to be able to trap errors and identify the offending column in the input table.  The Oracle documentation suggests something like this:

    step_num:=
    rec1.col1 := rec2.cola;
    step_num:=
    rec1.col2 :=rec2.colb;

    exception
    when dbms_output.put_line('error at '||step_num);
    raise;
    end;

    Is there a better way for me to identify the location of the error?

    Thanks,
    Peter Schauss
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Moore at Jun 9, 2011 at 8:36 pm
    *Peter,*
    *When you said:*
    " I am using the %rowtype construct for the fetches and inserts and doing a
    large number of assignment statements in the form:

    rec1.col1 := rec2.cola;"

    *Did you actually mean:*

    rec1 := rec2;

    I get the impression that you are doing record level assignments and what
    you are tying to avoid is doing the assignment on a column by column basis
    in order to determine which column's data has a problem. Am I right?

    Mike
    On Thu, Jun 9, 2011 at 1:09 PM, Gints Plivna wrote:

    It seems you are doing some kind of data migration. I suggest you to
    try simple SQL approach if it is possible. Instead of pseudocode:
    For each record in source table:
    Copy it to pl/sql;
    Make transformation on column1;
    ...
    Make transformation on column n;
    Insert into target table
    End for;

    Better create some temporary table (or several temporary tables, might
    be global temporary tables or not, doesn't matter so much) with
    necessary structure and do something like:
    Copy all records from source into temporary table doing as much
    transformations already as possible;
    Do transformation 1 on all records;
    ...
    Do transformation n on all records;
    Insert all records into target table.

    If you need to fix some kind of logical error for each record why it
    fails transformations, then I suggest to use concept of validations -
    before making transformation mark all invalid records with your error
    number BEFORE transformation.
    Even if you'll scan all the data several times (for validations,
    transformations etc), it still will be better than doing row by row
    approach.
    I have done a few such migrations and even wrote an article about them
    http://www.gplivna.eu/papers/legacy_app_migration.htm

    Of course each case is different, but use records and cursors (even
    bulks and foralls) only as a last resort.

    Gints Plivna
    http://www.gplivna.eu

    2011/6/9 Schauss, Peter (ESS) :
    Oracle 11.2.0.1.0 (Linux x86-64).

    I am writing stored procedure which copies information from one table to
    another, doing some transformations in the process. I am using the %rowtype
    construct for the fetches and inserts and doing a large number of assignment
    statements in the form:
    rec1.col1 := rec2.cola;

    Since there is a possibility of type conversion errors in some cases, I
    need to be able to trap errors and identify the offending column in the
    input table. The Oracle documentation suggests something like this:
    step_num:=
    rec1.col1 := rec2.cola;
    step_num:=
    rec1.col2 :=rec2.colb;

    exception
    when dbms_output.put_line('error at '||step_num);
    raise;
    end;

    Is there a better way for me to identify the location of the error?

    Thanks,
    Peter Schauss
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Richard at Jun 14, 2011 at 2:51 pm
    Peter,

    You might want to try dbms_utility.format_error_backtrace as
    follows:

    declare a integer;
    begin

    a := 'ABC';
    exception when others then
    dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
    end;
    /
    ORA-06512: at line 3

    PL/SQL procedure successfully completed.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Schauss, Peter (ESS)
    Sent: Thursday, June 09, 2011 3:13 PM
    To: oracle-l@freelists.org
    Subject: PLSQL exception handling

    Oracle 11.2.0.1.0 (Linux x86-64).

    I am writing stored procedure which copies information from one table to
    another, doing some transformations in the process. I am using the
    %rowtype construct for the fetches and inserts and doing a large number
    of assignment statements in the form:

    rec1.col1 := rec2.cola;

    Since there is a possibility of type conversion errors in some cases, I
    need to be able to trap errors and identify the offending column in the
    input table. The Oracle documentation suggests something like this:

    step_num:=
    rec1.col1 := rec2.cola;
    step_num:=
    rec1.col2 :=rec2.colb;

    exception

    when dbms_output.put_line('error at '||step_num);
    raise;

    end;

    Is there a better way for me to identify the location of the error?

    Thanks,
    Peter Schauss
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 9, '11 at 7:12p
activeJun 14, '11 at 2:51p
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase