Re: DBMS_ERRLOG will not compile within a cursor loop

alexglaros_at_gmail.com wrote:
> DBMS_ERRLOG will not compile within a cursor loop. Depending upon
> where I put the
> semicolons, I get these types of error messages.
>
> PLS-00103: Encountered the symbol "ERRORS" when expecting one of the
> following: := . ( @ % ;
> or
>
> (S6019) Expecting: ; RETURN RETURNING
>
> Any ideas on how I re-write to code below to work?
>
> I can make it work with a regular non-looping insert statement from
> sqlPlus or PL/SQL
>
> Thanks!
>
> Alex Glaros
>
> FOR c1_rec IN c1_accu_actions LOOP
> INSERT INTO ACCUSATION_ACTIONS
> ( id,
> acn_id,
> case_type,
> ate_code,
> ate_pgm_code,
> action_date,
> description,
> css_copy_id_create,
> reference )
> VALUES ( c1_rec.seq,
> c1_rec.id,
> c1_rec.case_type,
> c1_rec.action_code,
> c1_rec.agency,
> c1_rec.action_date,
> c1_rec.action_desc,
> c1_rec.p_copy_id,
> c1_rec.reference )
> LOG ERRORS INTO ACCUS_ACTIONS_ERR_LOG REJECT LIMIT
> 100;
> v_recount := v_recount + 1;
> IF (v_recount > 100) THEN
> v_recount := 0;
> COMMIT;
> END IF;
> END LOOP;
> COMMIT;
Sure it will:

SQL> CREATE TABLE t AS

   2 SELECT *
   3 FROM all_tables
   4 WHERE 1=2; Table created.

SQL>
SQL> ALTER TABLE t

   2 ADD CONSTRAINT pk_t
   3 PRIMARY KEY (owner, table_name)
   4 USING INDEX; Table altered.

SQL>
SQL> ALTER TABLE t

   2 ADD CONSTRAINT cc_t
   3 CHECK (blocks < 11);

Table altered.

SQL> exec dbms_errlog.create_error_log('T');

PL/SQL procedure successfully completed.

SQL> BEGIN
   2 INSERT /*+ APPEND */ INTO t
   3 SELECT *
   4 FROM all_tables
   5 LOG ERRORS
   6 REJECT LIMIT UNLIMITED;
   7 END;
   8 /

PL/SQL procedure successfully completed.

SQL>

-- 
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