Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Coding Question

Re: Coding Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 29 Jul 2007 12:46:10 -0700
Message-ID: <1185738366.433573@bubbleator.drizzle.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US