Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from serverfault.com Sep 6 '12 at 22:49

This question came from our site for system and network administrators.

1 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.

share|improve this answer
    
Thanks for reply. But when I add BEGIN then I get following error "synonym translation no longer valid". Also, if I run individual queries using synonyms seperately they seem to be working fine. –  sam May 16 '12 at 5:30
1  
Are you running the individual queries (outside the package) as the same user/schema who owns the eco_push package? Are you sure the object the synonym refers to exists and is granted DIRECTLY to the owner of the package (not granted through a role) because ORA-00980: synonym translation is no longer valid indicates the object under the synonym does not exist, e.g., database link cut out, object was dropped, etc. –  George3 May 16 '12 at 22:21
    
Thanks George3 for your reply. Apparently I had to add "Execute Immediate" to my queries and it worked. –  sam May 18 '12 at 0:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.