Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PLS-00103 error after declare in oracle procedure

719719Aug 27 2009 — edited Aug 28 2009
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;
/

Comments

Answer

rqEval can accept non-numeric types as input.

In your example, 'select 1 "Industry" forces a returned numeric value.  It fails because the data returned contains 1 non-numeric column. The output doesn't match the specification in the SQL provided, and an error is returned.

Instead, use cast to return as varchar as follows:

SQL> select * from table(rqEval(

        NULL,

        'select cast(''Industry'' as varchar2(8)) "Industry" from dual',

       'Test'));

Returns:

1 Text

Marked as Answer by 3631507 · Sep 27 2020
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 25 2009
Added on Aug 27 2009
9 comments
36,598 views