Coding Question

Giganews Newsgroups
Subject: Coding Question
Posted by:  Dereck L. Dietz (dietz…@ameritech.net)
Date: Sat, 28 Jul 2007

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;

Replies