Error Executing Script: PLS-00103: Encountered the symbol "DECLARE"

Discussion of open issues, suggestions and bugs regarding database management and development tools for Oracle
Post Reply
brandscill
Posts: 2
Joined: Thu 01 Nov 2018 18:32

Error Executing Script: PLS-00103: Encountered the symbol "DECLARE"

Post by brandscill » Thu 01 Nov 2018 18:37

Hi,

I'm trying to run a script that has been generated using DbForge. The script was not generated in DbForge but in TOAD. This script runs without issue in TOAD and also SQL Developer.

A snippet of the script is below but essentially DbForge throws an error when it hits the second declare statement within the script despite the previous declare being terminated with a semi colon.

Any ideas why DbForge does not like this type of script?
grant create sequence to lhapp_owner;



--##REMARKS make sure we have permission to do things
DECLARE
sddl settingsdatatype.datatypevarchar2000%TYPE;
BEGIN
sddl := 'PURGE RECYCLEBIN';

--DBMS_OUTPUT.Put_Line(sDDL);
EXECUTE IMMEDIATE sddl;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

--##REMARKS First check there is at least some content in the LHAPPT
DECLARE
ncount settingsdatatype.datatypeint%TYPE;
nodata EXCEPTION;
BEGIN
SELECT COUNT(rpttaskid) INTO ncount FROM lhappt_owner.rpttask;

IF ncount = 0 THEN
RAISE nodata;
END IF;
END;

--##REMARKS DISABLE ALL TRIGGERS IN LHAPP_OWNER
DECLARE
sddl settingsdatatype.datatypevarchar2000%TYPE;

CURSOR c1 IS
SELECT *
FROM sys.all_triggers
WHERE owner = 'LHAPP_OWNER' AND trigger_name <> 'TRIG_LHSESSION' AND trigger_name <> 'TRIG_COMPUTEREXTEVENTHISTORY'
ORDER BY table_name;
BEGIN
FOR c1rec IN c1 LOOP
sddl := 'ALTER TRIGGER LHAPP_OWNER.' || c1rec.trigger_name || ' DISABLE';

--DBMS_OUTPUT.Put_Line(sDDL);
EXECUTE IMMEDIATE sddl;
END LOOP;
END;

alexa
Devart Team
Posts: 2558
Joined: Fri 24 Jun 2011 14:17

Re: Error Executing Script: PLS-00103: Encountered the symbol "DECLARE"

Post by alexa » Fri 02 Nov 2018 10:08

We will investigate this issue and will answer you as soon as possible.

brandscill
Posts: 2
Joined: Thu 01 Nov 2018 18:32

Re: Error Executing Script: PLS-00103: Encountered the symbol "DECLARE"

Post by brandscill » Fri 02 Nov 2018 11:31

Thanks.

In SQL Developer I need to terminate each end on a new line with a / but even that doesn't work in DbForge.

Need to execute these types of scripts regularly so if it can't be done then I'll need to stick with TOAD.

alexa
Devart Team
Posts: 2558
Joined: Fri 24 Jun 2011 14:17

Re: Error Executing Script: PLS-00103: Encountered the symbol "DECLARE"

Post by alexa » Tue 06 Nov 2018 10:35

The following script causes no errors on our side:

Code: Select all

--##REMARKS make sure we have permission to do things
DECLARE
SDDL settingsdatatype.datatypevarchar2000 % TYPE;
BEGIN
SDDL := 'PURGE RECYCLEBIN';

--DBMS_OUTPUT.Put_Line(sDDL);
EXECUTE IMMEDIATE SDDL;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/

--##REMARKS First check there is at least some content in the LHAPPT
DECLARE
NCOUNT settingsdatatype.datatypeint % TYPE;
NODATA EXCEPTION;
BEGIN
SELECT COUNT(rpttaskid)
INTO NCOUNT
FROM lhappt_owner.rpttask;

IF NCOUNT = 0
THEN
RAISE NODATA;
END IF;
END;
/

--##REMARKS DISABLE ALL TRIGGERS IN LHAPP_OWNER
DECLARE
SDDL settingsdatatype.datatypevarchar2000 % TYPE;

CURSOR C1 IS
SELECT *
FROM SYS.ALL_TRIGGERS
WHERE OWNER = 'LHAPP_OWNER'
AND TRIGGER_NAME <> 'TRIG_LHSESSION'
AND TRIGGER_NAME <> 'TRIG_COMPUTEREXTEVENTHISTORY'
ORDER BY TABLE_NAME;
BEGIN
FOR C1REC IN C1
LOOP
SDDL := 'ALTER TRIGGER LHAPP_OWNER.' || C1REC.TRIGGER_NAME || ' DISABLE';

--DBMS_OUTPUT.Put_Line(sDDL);
EXECUTE IMMEDIATE SDDL;
END LOOP;
END;

Post Reply