PLS-00103 error after declare in oracle procedure

719719 · Aug 27, 2009 6:15PM

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;
/

Peter Gjelstrup · Aug 27, 2009 6:18PM

Hi,

Remove DECLARE.

Regards
Peter

damorgan · Aug 27, 2009 6:50PM

DECLARE sections are only valid in two contexts:

1. Triggers
2. The local declaration of variables within a nested block

719719 · Aug 28, 2009 8:54AM

It still not working after remove DECLARE.

SQL> show error
Errors for PROCEDURE EC_NRB_SCHEMA.PROC_CHNB_PURGE_ALL_NED:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/9 PLS-00103: Encountered the symbol "PKG_RECORD" when expecting
one of the following:
:= . ( @ % ;

Boneist · Aug 28, 2009 9:14AM

Works for me:

CREATE OR REPLACE PROCEDURE test
(
bld_tran_type_cd IN VARCHAR2 DEFAULT NULL,
v_purge_age IN NUMBER DEFAULT NULL,
row_count IN INTEGER DEFAULT NULL
)
AS

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 
  NULL; --- removed this bit as I don't have your tables etc
END;
/

Procedure created.

You obviously haven't removed the correct bit or something.

It also looks like you're trying to insert into your associative array... you can't do that. They aren't tables and SQL doesn't know about them, so standard DML operations will not work on them. If you do actually have a table called stage_item_status in your schema, why call your associative array by the same name?!

What is it you're ultimately trying to do? Update a table somewhere? Insert data somewhere? Pull data out for a report? What?

719719 · Aug 28, 2009 10:26AM

I really appreciate your kind help on this.

You notes are correct.
The table or array is not recognized. so i got error below.
Basically, I am trying to convert some proc from Syb to Oracle without touching too much logic change.
The proc tries to get a batch of pkg_nbr/id, then delete all related record in the other tables based on the nbr/id collected. again get another batch do the same thing. (I don't know why previous people do that.) They used to do it in sybase in temp table.They want to keep it.(I don't care.)
I have to create a pl/sql table to do that.

SQL> show error
Errors for PROCEDURE EC_NRB_SCHEMA.PROC_CHNB_PURGE_ALL_NED:

LINE/COL ERROR
-------- -----------------------------------------------------------------
25/6 PL/SQL: SQL Statement ignored
25/18 PL/SQL: ORA-00942: table or view does not exist
61/11 PL/SQL: SQL Statement ignored
64/27 PL/SQL: ORA-00942: table or view does not exist
69/11 PL/SQL: SQL Statement ignored
72/27 PL/SQL: ORA-00942: table or view does not exist
77/11 PL/SQL: SQL Statement ignored
80/27 PL/SQL: ORA-00942: table or view does not exist
85/11 PL/SQL: SQL Statement ignored
88/27 PL/SQL: ORA-00942: table or view does not exist
93/11 PL/SQL: SQL Statement ignored

Sven W. · Aug 28, 2009 12:29PM

You might need direct grants (not via a role) from the schema with your tables (sis or whatever) to the schema with your procedure.

Boneist · Aug 28, 2009 12:47PM

So, rather than using an array, you could use a Global Temp Table and effectively mimic what you do in sybase, or you could simply do several deletes:

DELETE FROM tablea
WHERE col1 in (SELECT cola FROM main_table WHERE ...);

DELETE FROM tableb
WHERE col1 in (SELECT cola FROM main_table WHERE ...);

....

DELETE FROM main_table
WHERE ...;

That would be my preferred solution.

Or you could just create a cursor to collect the data that in sybase you would put in the temp table, and then loop through that to delete your data. This is not the preferred method (nor is copying the sybase way of doing things by using the GTT!) as it is row-by-row (aka slow by slow).

Peter Gjelstrup · Aug 28, 2009 12:48PM

They used to do it in sybase in temp table.They want to keep it.(I don't care.)

I don't know much about Sybase, but based on what you are showing it must be a different beast.

To me it sounds like a really bad idea, to try and do a "one-to-one" mapping of a Sybase approach into Oracle.

Why the use of temp tables (Oracle do have those, you know) or collections for that matters. Not knowing what you are trying to do, just do it. In SQL that is.

And some details:
Don't (Not that it matters) use DISTINCT when using UNION. UNION will make everything distinct itself.

this

TO_DATE(SYSDATE - CAST(cd.decode_desc AS INTEGER))

is considered wrong. Sysdate is already of type DATE and DATE - NUMBER is DATE in Oracle. And don't CAST, use TO_NUMBER
So change into

SYSDATE - TO_NUMBER(cd.decode_desc)

Unless, of course, decode_desc is already a NUMBER.

Regards
Peter

John Spencer · Aug 28, 2009 1:27PM

The best way to do this on Oracle would depend on a number of factors. Among the most important would be the number of rows returned by the query that gets the pkg_trkng_nbr, pkg_trkng_unique_id to be deleted, and how quickly it executes. If this query is relatively quick to run, then I would be extremely likely to just do a set of straight deltes in the procedure. Something alnog the lines of:

CREATE 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
BEGIN
   DELETE FROM related1
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN 
               (SELECT 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
                  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 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
                  AND ...);

   DELETE FROM related2
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN 
               (SELECT 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
                  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 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
                  AND ...);
END;

If the driving query is relatively expensive that is, takes significant time to run, then I would likely go with Boneist's suggestion and use a global temporary table. You would create the GTT once in sql before creating the procedure, then use it over and over. A GTT will get emptied when when the session exits (with ON COMMIT PRESERVE ROWS), or when the session commits (with ON COMMIT DELETE ROWS which is the default behaviour). So, you procedure would look something like:

-- Do this once before the procedure
CREATE GLOBAL TEMPORARY TABLE stage_item_status (
   pkg_trkng_nbr VARCHAR2(12),
   pkg_trkng_unique_id VARCHAR2(10))
ON COMMIT PRESERVE ROWS;
 
--Then create your procedure like
CREATE 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
BEGIN
   INSERT /*+ APPEND */ INTO stage_item_status
   SELECT 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
     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 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
     AND ...
 
   DELETE FROM related1
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN (SELECT pkg_trkng_nbr, pkg_trkng_unique_id
                                                  FROM stage_item_status);

   DELETE FROM related2
   WHERE (pkg_trkng_nbr, pkg_trkng_unique_id) IN (SELECT pkg_trkng_nbr, pkg_trkng_unique_id
                                                  FROM stage_item_status);
END;

My third choice would be using pl/sql arrays as you are attmpting here. To do this it would need to look more like:

CREATE 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
   TYPE PKG_RECORD IS RECORD (
      pkg_trkng_nbr VARCHAR2(12),
      pkg_trkng_unique_id VARCHAR2(10));

   TYPE pkg_record_table IS TABLE OF PKG_RECORD index by binary_integer;
   stage_item_status pkg_record_table;
   rec_nbr INTEGER := 0;

   CURSOR del_cur IS
      SELECT 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
        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 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
        AND ...;
   
BEGIN 
   OPEN del_cur;
   LOOP
      FETCH del_cur BULK COLLECT INTO stage_item_status LIMIT 100
      IF stage_item_status.COUNT > 0 THEN
         FORALL i IN stage_item_status.FIRST .. stage_item_status.LAST
            DELETE FROM related1
            WHERE pkg_trkng_nbr = stage_item_status(i).stage_item_status
              AND pkg_trkng_unique_id = stage_item_status(i).pkg_trkng_unique_id;

         FORALL i IN stage_item_status.FIRST .. stage_item_status.LAST
            DELETE FROM related2
            WHERE pkg_trkng_nbr = stage_item_status(i).stage_item_status
              AND pkg_trkng_unique_id = stage_item_status(i).pkg_trkng_unique_id
      END IF;
      EXIT WHEN del_cur%NOT_FOUND;
   END LOOP;
END;

Note that in all cases, I removed the DISTINCt from your select statement. Using UNION implies a sort distinct of the two queries anyway, so distincting each one individually is an overhead you don't need. I would likely use union all in any case to avoid the sort since there is no real harm in deleting a non-existent row.

HTH
John

Forum Stats

Quick Links

Categories

PLS-00103 error after declare in oracle procedure

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

PLS-00103 error after declare in oracle procedure

Answers