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;

alexglaros (AT) gmail (DOT) com wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Nov 1, 11:26 am, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
It can save you a lot of typing. Is it worth an extra 1000 keystrokes
in code to save one or two microseconds in execution time?

Ken Denny wrote:

YES!

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Ken Denny wrote:
Lets see ... with cursor loop.

CREATE OR REPLACE PROCEDURE slow_way IS

BEGIN
FOR r IN (SELECT * FROM parent) LOOP
-- modify record values
r.part_num := r.part_num * 10;
-- store results
INSERT INTO child
VALUES
(r.part_num, r.part_name);
END LOOP;
COMMIT;
END slow_way;
/

-- 7.71 seconds

without cursor loop.

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;

CURSOR r IS
SELECT part_num, part_name
FROM parent;

BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO l_data LIMIT 1000;

FOR j IN 1 .. l_data.COUNT
LOOP
l_data(1).part_num := l_data(1).part_num * 10;
END LOOP;

FORALL i IN 1..l_data.COUNT
INSERT INTO child VALUES l_data(i);

EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END fast_way;
/

-- 0.50 seconds

<SARCASM>
Oh my gawd my fingers are killing me. All the heavy lifting and hard
work. Please excuse me for a minute. I need to sit down and catch my
breath ... make an appointment to have my carpel tunnel syndrome
treated ... make out my will.

And all I did was speed the code up by a lousy 15.42 times. Hardly
worth it eh.
</SARCASM>

Good thing the web is near anonymous Ken. You might have just talked
yourself out of quite a few job opportunities if recruiters look for
you on the web. A few microseconds my ....
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan" <damorgan (AT) psoug (DOT) org> schreef in bericht
news:1193950181.169048 (AT) bubbleator (DOT) drizzle.com...
Daniel,

I think error handling should be at the start of the loop and could be

EXIT WHEN l_data.COUNT = 0;
which might save another millisecond... <g>

Picked this from the net:
"Also, within the loop (using LIMIT), you cannot rely on cursor%FOUND to
determine if the last fetch returned any rows."

But maybe this has changed over the years?

Shakepeare

shakespeare wrote:
Seem to work ... but of course one should pay remember that there
are environment variables just for BULK COLLECT such as SQL%BULK_ROWCOUNT.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Nov 1, 3:49 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Sorry. I didn't mean to imply that the savings was never more than a
few microseconds. There are many times where the savings using bulk
collect and avoiding cursor loops are enormous. What I meant to say
was that *SOMETIMES* the savings are minimal while the increase in
keystrokes are huge. Consider in your example if your insert had to
reference 100 or more individual columns and your parent table had no
more than 100 rows.

Ken Denny wrote:

It's a state of mind - as soon as you fail to see that, lame excuses
like the above start appearing over and over, as well as for that
crucial bit of code that needed it.
The other option would be to buy more iron, at a huge cost.

Side note: millisecs of speed gain, multiplied by hundreds
of calls per hour, multiplied by eight (hrs/day), multiplied
by hundreds of users, multiplied by 270 (work days/yr),
multiplied by 5 (yrs) makes?

That's the average life span of a major application.

Now, do you see your 'minor' winnings actually have quite an impact?
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...