This discussion is archived
9 Replies Latest reply: Aug 28, 2009 10:27 AM by John Spencer RSS

PLS-00103 error after declare in oracle procedure

719719 Newbie
Currently Being Moderated
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;
/
  • 1. Re: PLS-00103 error after declare in oracle procedure
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi,

    Remove DECLARE.

    Regards
    Peter
  • 2. Re: PLS-00103 error after declare in oracle procedure
    damorgan Oracle ACE Director
    Currently Being Moderated
    DECLARE sections are only valid in two contexts:

    1. Triggers
    2. The local declaration of variables within a nested block
  • 3. Re: PLS-00103 error after declare in oracle procedure
    719719 Newbie
    Currently Being Moderated
    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:
    := . ( @ % ;
  • 4. Re: PLS-00103 error after declare in oracle procedure
    Boneist Guru
    Currently Being Moderated
    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?
  • 5. Re: PLS-00103 error after declare in oracle procedure
    719719 Newbie
    Currently Being Moderated
    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
  • 6. Re: PLS-00103 error after declare in oracle procedure
    Sven W. Guru
    Currently Being Moderated
    You might need direct grants (not via a role) from the schema with your tables (sis or whatever) to the schema with your procedure.
  • 7. Re: PLS-00103 error after declare in oracle procedure
    Boneist Guru
    Currently Being Moderated
    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).
  • 8. Re: PLS-00103 error after declare in oracle procedure
    Peter Gjelstrup Guru
    Currently Being Moderated
    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
  • 9. Re: PLS-00103 error after declare in oracle procedure
    John Spencer Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points