getting oracle error ora-00103 when I try to execute a package in oracle 10g

Question

I have to delete and repopulate tables on a remote database.To access the remote tables I have created synonyms. my package is as follows

create or replace
PACKAGE eco_push IS
PROCEDURE waters_data_push;
END eco_push;

Package body is as follows

create or replace
PACKAGE BODY eco_push is 

procedure waters_data_push
   IS  

 --DELETING FROM WATERS_SITE  

DELETE FROM watersitesyn;  -------------   error line (from)

-- Insert into table WATERS_SITE
     INSERT INTO watersitesyn
            (SITE_ID,SITE_NAME,
            CLI_NAME,ECOTRAC_NO,
            APP_REF,SEQ_NO,
            ACTIV_CODE,LOT_PLAN,
            FACIL_LOC,LAT,
            LON
            )

    SELECT DISTINCT
           st.ID,
           st.NAME,
           cli.NAME,
           cli.ECOTRACKNO,
          /*app.APPROVALREF,*/  CASE WHEN to_date(appst.VALIDTO)IS NOT NULL THEN (select app.APPROVALREF from APPROVALSITE appst, APPROVAL app where  SYSDATE BETWEEN to_date(appst.VALIDFROM) and to_date(appst.VALIDTO)and appst.site_id = st.ID)
                                     WHEN to_date(appst.VALIDTO) IS NULL THEN (select app.APPROVALREF from APPROVALSITE appst, APPROVAL app where  SYSDATE >to_date(appst.VALIDFROM) and appst.site_id = st.ID)
                                END,
           app.APPROVALSEQ,
           luact.CODE,
           CONCAT(lp.LOT,lp.PLAN),
           st.LOCATION,
           geop.LATITUDE,
           geop.LONGITUDE
     FROM  SITE st,
           CLIENT cli,
           APPROVAL app,
           APPROVALSITE appst,
           LU_ACTIVITY luact,
           LOTPLAN lp,
           GEOPOINT geop
     WHERE
          sysdate  between  to_date(appst.VALIDFROM) and to_date(appst.VALIDTO)
          AND  geop.ID = st.GEOPOINT_ID
          AND  lp.SITE_ID = st.ID 
          AND  appst.APPROVAL_ID = app.ID;

......

I am getting the following error

Error: PLS-00103: Encountered the symbol "FROM" when expecting one of the following:     constant exception <an identifier>    <a double-quoted delimited-identifier> table long double ref    char time timestamp interval date binary national character    nchar 

How to solve the error?

Thanks in advance

Answer 1

The DELETE FROM watersitesyn; code is still in the declarative part of your procedure. You need to add a BEGIN to end the declarative part and begin the executable part as described in this section of the Oracle 11.2 Database 2 Day Developer's Guide.