Re: Coding Question

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 29 Jul 2007 11:12:20 -0500
Message-ID: <n72ri.31868$C96.7097@newssvr23.news.prodigy.net>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1185685555.912743_at_bubbleator.drizzle.com...
> Dereck L. Dietz wrote:
> > Oracle 10g v 10.2.0.3.0, 8 BG RAM, Win 2003 Server
> >
> > I've been trying to clean up some existing code and I want to add
exception
> > processing. For an exception row I want to write the SQLCODE, SQLERRM
and
> > the source table's rows to the exception table.
> >
> > I know I can do it if I move each column individually but I was
> > wondering/hoping that I could do it on the row level. The following
example
> > code is how I'm hoping I can set up my variables and do the moves.
> >
> > The one big question I have is if I'm going to have problems moving the
> > t_table_2 type into the table_2 table row. Unfortunately I'm working
> > off-site without access to do more than write the code for actual
testing on
> > Monday.
> >
> > Thanks.
> >
> > Tables:
> >
> > Table_1
> > field_one VARCHAR2(10),
> > field_two VARCHAR2(10),
> > field_three VARCHAR2(10)
> >
> > Table_2
> > sql_code NUMBER(6),
> > sql_error VARCHAR2(512),
> > field_one VARCHAR2(10),
> > field_two VARCHAR2(10),
> > field_three VARCHAR2(10)
> >
> > Declarations:
> >
> > TYPE t_table_2 IS RECORD
> > (
> > sql_code NUMBER(6),
> > sql_error VARCHAR2(512),
> > exception_info table_1%ROWTYPE
> > );
> >
> > v_table_1 table_1%ROWTYPE;
> > v_table_2 table_2%ROWTYPE;
> > v_rec t_table_2;
> >
> > Code Section:
> >
> > v_rec.sql_code := SQLCODE;
> > v_rec.sql_error := SQLERRM(SQLCODE);
> > v_rec.exception_info := v_table_1;
> >
> > v_table_2 := v_rec;
>
> From what you've posted I've no idea what it is you are doing but
> you can certainly do it with array processing by handling the
> exception array created by SAVE EXCEPTIONS. Check out
> http://www.psoug.org/reference/array_processing.html and look for
> "Bulk Collection Exception Handling."
>
> If doing something else then consider working with records rather than
> individual variables. For example:
>
> CREATE TABLE t AS
> SELECT table_name, tablespace_name
> FROM all_tables;
>
> SELECT COUNT(*)
> FROM t;
>
> DECLARE
> trec t%ROWTYPE;
> BEGIN
> trec.table_name := 'NEW';
> trec.tablespace_name := 'NEW_TBSP';
>
> INSERT INTO t
> VALUES trec;
>
> COMMIT;
> END;
> /
>
> SELECT COUNT(*)
> FROM t;
>
> Needless to say trec could be dumped into an exceptions table.
>
> I'd also advise including instance_name if there is any possibility
> that the code may some day be required to run on RAC. You can capture
> RAC related information by using the DBMS_UTILITIES package also
> documented in Morgan's Library.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

I"m already using BULK COLLECT and working with records. That is why I want to know if I can move a structure containing a nested table (T_TABLE_2 type based record) into a structure which does not contain a nested table (TABLE_2) so I can move columns around prior to inserting into my exception table.

• insert rows into Stage Member Address table

    BEGIN

      FORALL v_row IN INDICES OF v_load_rec SAVE EXCEPTIONS
        INSERT INTO stg_mbr_addr VALUES v_load_rec(v_row);
    EXCEPTION
      WHEN e_bulk_errors THEN
        FOR v_row IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
          write_addr_exception( SQL%BULK_EXCEPTIONS(v_row).ERROR_CODE,
                                v_load_rec(
SQL%BULK_EXCEPTIONS(v_row).ERROR_INDEX ) );
        END LOOP;

    END; ...

• -----------------------------------------------------------------------

  ---

• WRITE ADDRESS EXCEPTION
• -----------------------------------------------------------------------

  ---

  PROCEDURE write_addr_exception( p_error_code IN stg_mbr_addr_exception.sql_code%TYPE,

                                  p_rec        IN stg_mbr_addr%ROWTYPE )

  IS

    TYPE t_except_rec IS RECORD
    (

      sql_code   stg_mbr_addr_exception.sql_code%TYPE,
      sql_error   stg_mbr_addr_exception.sql_error%TYPE,
      exception_info  stg_mbr_addr%ROWTYPE
     );

    v_except_rec stg_mbr_addr_exception%ROWTYPE;     v_rec t_except_rec;

    PRAGMA AUTONOMOUS_TRANSACTION;   BEGIN

    v_rec.sql_code  := p_error_code;
    v_rec.sql_error  := SQLERRM( -1 * p_error_code );
    v_rec.exception_info := p_rec;

    v_except_rec := v_rec;

    INSERT INTO stg_mbr_addr_exception VALUES v_except_rec;

    COMMIT;   EXCEPTION
    WHEN OTHERS THEN

      dbms_output.put_line( dbms_utility.format_error_backtrace );
      RAISE;

  END write_addr_exception;

... Received on Sun Jul 29 2007 - 11:12:20 CDT