Coding Question

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sat, 28 Jul 2007 22:16:59 -0500
Message-ID: <tMSqi.170$rG7.55@nlpi061.nbdc.sbc.com>

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;

Received on Sat Jul 28 2007 - 22:16:59 CDT