Dereck L. Dietz wrote:
> "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;
>
> ...
In a FOR LOOP ... loop through the array or perform a FORALL insert.
--
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
Received on Sun Jul 29 2007 - 14:46:10 CDT
