Hi, guys.
I have a simple stored proc. which got me PLS-00103 error. Try to search a solution, but not found.
24/12 PLS-00103: Encountered the symbol "LOOP" when expecting one of
the following:
:= . ( @ % ; not null range default character
If move BEGIN after declare: error will be:
9/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one
of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor external language
The symbol "begin" was substituted for "DECLARE" to continue.
156/4 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
LINE/COL ERROR
-------- -----------------------------------------------------------------
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
Here is:
CREATE OR REPLACE PROCEDURE my_SCHEMA.proc_purge_all_ned
(
bld_tran_type_cd IN VARCHAR2 DEFAULT NULL,
v_purge_age IN NUMBER DEFAULT NULL,
row_count IN INTEGER DEFAULT NULL
)
AS
DECLARE
TYPE PKG_RECORD IS RECORD
(
pkg_trkng_nbr VARCHAR2(12) DEFAULT NULL,
pkg_trkng_unique_id VARCHAR2(10) DEFAULT NULL
);
TYPE pkg_record_table IS TABLE OF PKG_RECORD
index by binary_integer;
stage_item_status pkg_record_table;
rec_nbr INTEGER := 0;
BEGIN
WHILE TRUE LOOP
INSERT INTO stage_item_status
SELECT DISTINCT sis.pkg_trkng_nbr,
FROM EC_NRB_SCHEMA.staging_item_status sis,
EC_NRB_SCHEMA.staging_control sc,
EA_SCHEMA.code cd
WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
AND sis.pkg_trkng_unique_id = sc.pkg_trkng_unique_id
AND sc.cmplt_flg = 'Y'
AND sis.bld_tran_type_cd = cd.cd_id
AND sis.last_updt_dt <= TO_DATE(SYSDATE - CAST(cd.decode_desc AS INTEGER))
UNION
SELECT DISTINCT sis.pkg_trkng_nbr,
sis.pkg_trkng_unique_id
FROM EC_NRB_SCHEMA.staging_item_status sis,
EC_NRB_SCHEMA.staging_control sc,
EA_SCHEMA.code cd
WHERE sis.pkg_trkng_nbr = sc.pkg_trkng_nbr
... ....
rec_nbr := stage_item_status.COUNT;
IF ( rec_nbr < 1 ) THEN EXIT;
END IF;
...
...
-- CLEAR ALL RECORD IN temp table stage_item_status
stage_item_status.DELETE;
END LOOP;
END;
/