Re: Coding Question

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