diannagibbs
asked on
Oracle PL/SQL insert, commit statement DECLARE error
Hello - I'm trying to write a simple PL/SQL statement to speed up inserting rows into a table. I have a 5bil row table. Below is my code and I keep getting DECLARE errors. Any help appreciated! Or if there is a better/faster way, please let me know. thanks!
SQL> create or replace procedure AC_NOTE
2 as
3 DECLARE
4 Col1 ACC_LOG_DTL_IX_OLD.ACCESS_ INSTANT%TY PE;
5 Col2 ACC_LOG_DTL_IX_OLD.PROCESS _ID%TYPE;
6 Col3 ACC_LOG_DTL_IX_OLD.DATA_MN EMONIC_ID% TYPE;
7 Col4 ACC_LOG_DTL_IX_OLD.STRING_ VALUE%TYPE ;
8 Col5 ACC_LOG_DTL_IX_OLD.INTEGER _VALUE%TYP E;
9 INSERT_COUNT NUMBER := 1;
10 BEGIN
11 CURSOR c1 IS select * from ACC_LOG_DTL_IX_OLD where DATA_MNEMONIC_ID = 'NOTE';
12 FOR r1 IN c1 LOOP
13 INSERT
14 INTO ACC_LOG_DTL_IX
15 (ACCESS_INSTANT,
16 PROCESS_ID,
17 DATA_MNEMONIC_ID,
18 STRING_VALUE,
19 INTEGER_VALUE)
20 VALUES
21 (r1.Col1, r1.Col2,r1.Col3,r1.Col4,r1 .Col5);
22 IF INSERT_COUNT = 1000 THEN
23 COMMIT;
24 INSERT_COUNT := 1;
25 ELSE
26 INSERT_COUNT := INSERT_COUNT + 1;
27 END IF;
28 END LOOP;
29 COMMIT;
30 END
31 ;
32 /
Warning: Procedure created with compilation errors.
Elapsed: 00:00:00.12
show errors
Errors for PROCEDURE AC_NOTE:
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior external language
The symbol "begin" was substituted for "DECLARE" to continue.
11/8 PLS-00103: Encountered the symbol "C1" when expecting one of the
following:
:= . ( @ % ;
SQL> create or replace procedure AC_NOTE
2 as
3 DECLARE
4 Col1 ACC_LOG_DTL_IX_OLD.ACCESS_
5 Col2 ACC_LOG_DTL_IX_OLD.PROCESS
6 Col3 ACC_LOG_DTL_IX_OLD.DATA_MN
7 Col4 ACC_LOG_DTL_IX_OLD.STRING_
8 Col5 ACC_LOG_DTL_IX_OLD.INTEGER
9 INSERT_COUNT NUMBER := 1;
10 BEGIN
11 CURSOR c1 IS select * from ACC_LOG_DTL_IX_OLD where DATA_MNEMONIC_ID = 'NOTE';
12 FOR r1 IN c1 LOOP
13 INSERT
14 INTO ACC_LOG_DTL_IX
15 (ACCESS_INSTANT,
16 PROCESS_ID,
17 DATA_MNEMONIC_ID,
18 STRING_VALUE,
19 INTEGER_VALUE)
20 VALUES
21 (r1.Col1, r1.Col2,r1.Col3,r1.Col4,r1
22 IF INSERT_COUNT = 1000 THEN
23 COMMIT;
24 INSERT_COUNT := 1;
25 ELSE
26 INSERT_COUNT := INSERT_COUNT + 1;
27 END IF;
28 END LOOP;
29 COMMIT;
30 END
31 ;
32 /
Warning: Procedure created with compilation errors.
Elapsed: 00:00:00.12
show errors
Errors for PROCEDURE AC_NOTE:
LINE/COL ERROR
-------- --------------------------
3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior external language
The symbol "begin" was substituted for "DECLARE" to continue.
11/8 PLS-00103: Encountered the symbol "C1" when expecting one of the
following:
:= . ( @ % ;
Just remove the declare keyword. This is only used when creating an anonymous PL/SQL block not a stored procedure.
You can't use the word "declare" inside a named PL\SQL procedure. You only need that keyword if you want to run an anonymouse (or unnamed) PL\SQL block of code.
Also cursor c1 needs to come after the begin keyword.
I meant to say before the begin keyword.
Also, if you really have a 5 billion row table (that's large!), are you sure that you want to use a cursor loop to copy individual records one-at-a-time to a new table? That process may keep your server busy for a LONG time!
SQL> create or replace procedure AC_NOTE
as
Col1 ACC_LOG_DTL_IX_OLD.ACCESS_ INSTANT%TY PE;
Col2 ACC_LOG_DTL_IX_OLD.PROCESS _ID%TYPE;
Col3 ACC_LOG_DTL_IX_OLD.DATA_MN EMONIC_ID% TYPE;
Col4 ACC_LOG_DTL_IX_OLD.STRING_ VALUE%TYPE ;
Col5 ACC_LOG_DTL_IX_OLD.INTEGER _VALUE%TYP E;
INSERT_COUNT NUMBER := 1;
CURSOR c1 IS select * from ACC_LOG_DTL_IX_OLD where DATA_MNEMONIC_ID = 'NOTE';
BEGIN
FOR r1 IN c1 LOOP
INSERT
INTO ACC_LOG_DTL_IX
(ACCESS_INSTANT,
PROCESS_ID,
DATA_MNEMONIC_ID,
STRING_VALUE,
INTEGER_VALUE)
VALUES
(r1.Col1, r1.Col2,r1.Col3,r1.Col4,r1 .Col5);
IF INSERT_COUNT = 1000 THEN
COMMIT;
INSERT_COUNT := 1;
ELSE
INSERT_COUNT := INSERT_COUNT + 1;
END IF;
END LOOP;
COMMIT;
END
;
/
as
Col1 ACC_LOG_DTL_IX_OLD.ACCESS_
Col2 ACC_LOG_DTL_IX_OLD.PROCESS
Col3 ACC_LOG_DTL_IX_OLD.DATA_MN
Col4 ACC_LOG_DTL_IX_OLD.STRING_
Col5 ACC_LOG_DTL_IX_OLD.INTEGER
INSERT_COUNT NUMBER := 1;
CURSOR c1 IS select * from ACC_LOG_DTL_IX_OLD where DATA_MNEMONIC_ID = 'NOTE';
BEGIN
FOR r1 IN c1 LOOP
INSERT
INTO ACC_LOG_DTL_IX
(ACCESS_INSTANT,
PROCESS_ID,
DATA_MNEMONIC_ID,
STRING_VALUE,
INTEGER_VALUE)
VALUES
(r1.Col1, r1.Col2,r1.Col3,r1.Col4,r1
IF INSERT_COUNT = 1000 THEN
COMMIT;
INSERT_COUNT := 1;
ELSE
INSERT_COUNT := INSERT_COUNT + 1;
END IF;
END LOOP;
COMMIT;
END
;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Any ideas on a faster way of doing this? I've been trying to use SQLPlus but it's taking forever, not to mention running out of UNDO, etc. I'm open to any ideas? Thanks!
ASKER
Took out DECLARE...now this error?
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
12/3 PL/SQL: SQL Statement ignored
20/39 PL/SQL: ORA-00984: column not allowed here
20/39 PLS-00302: component 'COL5' must be declared
LINE/COL ERROR
-------- --------------------------
12/3 PL/SQL: SQL Statement ignored
20/39 PL/SQL: ORA-00984: column not allowed here
20/39 PLS-00302: component 'COL5' must be declared
The example I just gave you is for SQL*Plus, since it includes the DDL command "alter table ..." that PL\SQL cannot handle directly. And, it avoids the undo and redo overhead through the combination of the "nologging" command and "insert /*+APPEND */ ..." syntax.
If you've never used the "nologging" command before, don't worry that this will leave your data vulnerable to a disk failure. "Nologging" only applies to a very tiny sub-set of SQL activites (inserts) and then only if the insert command includes the "APPEND" hint. So, all "normal" inserts, updates and deletes will be logged anyway.
If you've never used the "nologging" command before, don't worry that this will leave your data vulnerable to a disk failure. "Nologging" only applies to a very tiny sub-set of SQL activites (inserts) and then only if the insert command includes the "APPEND" hint. So, all "normal" inserts, updates and deletes will be logged anyway.
if you can't do it in a single statement as markgeer showed above, then use bulk operations.
Definitely don't do it row-by-row.
Assuming it finishes one row in 1 millisecond (that's unrealistically optimistic) it'll take you 5 million seconds to finish, or about 57 days.
Definitely don't do it row-by-row.
Assuming it finishes one row in 1 millisecond (that's unrealistically optimistic) it'll take you 5 million seconds to finish, or about 57 days.
ASKER
I'm an Oracle DBA and have learned to never use nologging. Recovery can be an issue. As for your statement, that's what I've been doing in SqlPlus and just taking too long. I always understood a procedure/cursor would be faster. You're saying this is not true?
>>> understood a procedure/cursor would be faster. You're saying this is not true?
"faster" than what? pulling the data out row by row to a java/c#/vb.net routine that then inserts the rows one at a time? yes, the pl/sql routine will be faster. Don't laugh, I've seen people do that.
but, a single sql statement will always (provided you have the resources to let it complete) be faster and overall more efficient than procedural code, especially with looping.
"faster" than what? pulling the data out row by row to a java/c#/vb.net routine that then inserts the rows one at a time? yes, the pl/sql routine will be faster. Don't laugh, I've seen people do that.
but, a single sql statement will always (provided you have the resources to let it complete) be faster and overall more efficient than procedural code, especially with looping.
"I always understood a procedure/cursor would be faster."
Definitely *NOT* true!
A simple SQL statement is almost always faster than a PL\SQL procedure that includes a cursor! If you use "bulk collect' in PL\SQL, then the perormance of PL\SQL can approach that of simple SQL.
Definitely *NOT* true!
A simple SQL statement is almost always faster than a PL\SQL procedure that includes a cursor! If you use "bulk collect' in PL\SQL, then the perormance of PL\SQL can approach that of simple SQL.
not just bulk collect on the select, but also FORALL (yes, that's one word) on the insert
ASKER
Great thanks for news. I have been running the basic statement so will continue with that route. Will also look into your "no logging" but have heard too many horror stories of not being able to recover - months later - with using it. One more question, I've also been using "set autocommit 2500" but doesn't appear to be commiting every 2500 rows. Am I msising somethng?
>>> too many horror stories of not being able to recover
after you complete a nologging operation, take a backup.
That's really all there is to keeping it recoverable.
after you complete a nologging operation, take a backup.
That's really all there is to keeping it recoverable.
If you use "nologging" and are concerned about recovery, you simply need to do a backup of the affected tablespace(s) after the "nologging" operation.
The "set autocommit" command is a SQL*Plus command, not a PL\SQL command. So, that has no affect inside a PL\SQL procedure.
The "set autocommit" command is a SQL*Plus command, not a PL\SQL command. So, that has no affect inside a PL\SQL procedure.
ASKER
Yes, I understand the set autocommit...that's what I have been using with sqlplus in my original statements before I tried PL/SQL. We do backups nightly, so should be good. Thanks - I'll test timings. Appreciate it.