DA Morgan wrote:
> 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>
Now my examples does not include a cursor loop. But if you have
DMBS_ERRLOG then you are in a version of Oracle more recent than 8i.
And that means you've no reason for ever writing a cursor loop: Ever!
There is nothing in your example that requires a cursor ... or a loop
... and your incremental commit inside the loop is a manufacturing
facility designed to generate ORA-01555 and poor performance. My
recommendation would be to learn proper PL/SQL coding. Not to be
intentionally hash but the code you posted wasn't even good in 7.3.4.
--
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 Thu Nov 01 2007 - 10:26:53 CDT
