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%TYPE;
5 Col2 ACC_LOG_DTL_IX_OLD.PROCESS_ID%TYPE;
6 Col3 ACC_LOG_DTL_IX_OLD.DATA_MNEMONIC_ID%TYPE;
7 Col4 ACC_LOG_DTL_IX_OLD.STRING_VALUE%TYPE;
8 Col5 ACC_LOG_DTL_IX_OLD.INTEGER_VALUE%TYPE;
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:
:= . ( @ % ;