OraFAQ Forum: SQL & PL/SQL » PLS-00103: Encountered the symbol "QRY

Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: Encountered the symbol "QRY_STMT" when expecting one

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLS-00103: Encountered the symbol "QRY_STMT" when expecting one [message #347514]

Fri, 12 September 2008 00:09

kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai

Junior Member

Hi All, I am geeting below error while executing below procedure.

Rgrds,
Ram Kishan

CREATE OR REPLACE PROCEDURE CleanUp
AS
CURSOR c_Pug IS
SELECT *
FROM PurgeConFig
WHERE Purge_Flag = 'Y'
AND Purge_Update_Flag = 'N';
qry_stmt VARCHAR2(200);
BEGIN
UPDATE PurgeConFig
SET Purge_Update_Flag = 'N'
WHERE Purge_Flag = 'Y';

COMMIT;

FOR i IN c_Pug LOOP
IF i.Retention_Type = 'D' THEN
qry_stmt := 'delete from '
||i.TableName
||' where '
||i.Key_Column
||' <= sysdate - '
||i.Retention_Period;

ElseIf
qry_stmt := 'delete from '
||i.TableName
|| ' where '
|| i.Key_Column
|| ' <= sysdate - NUMTOYMINTERVAL('
|| i.Retention_Period
|| ',''MONTH'')';

dbms_Output.Put_Line(qry_stmt);
ELSE
qry_stmt := 'delete from '
||i.TableName
||' where '
||i.Key_Column
||' <= sysdate - NUMTOYMINTERVAL('
||i.Retention_Period
||',''YEAR'')';

dbms_Output.Put_Line(qry_stmt);
END IF;

EXECUTE IMMEDIATE qry_stmt;
END LOOP;
END;
/

Error Message:

SQL> @purge_new.sql

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE CLEANUP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
27/1 PLS-00103: Encountered the symbol "QRY_STMT" when expecting one
of the following:
:= . ( @ % ;
The symbol "." was substituted for "QRY_STMT" to continue.

SQL> 27
27* qry_stmt := 'delete from '
SQL> 28
28* ||i.TableName
SQL> 29
29* || ' where '
SQL> 30
30* || i.Key_Column
SQL> 31
31* || ' <= sysdate - NUMTOYMINTERVAL('
SQL> 32
32* || i.Retention_Period
SQL> 33
33* || ',''MONTH'')';

Report message to a moderator

Re: PLS-00103: Encountered the symbol "QRY_STMT" when expecting one [message #347517 is a reply to message #347514]

Fri, 12 September 2008 00:14

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Code: [Select all] [Show/ hide]

ElseIf
qry_stmt := 'delete from '

This is not the correct syntax of IF-THEN statement.
You may find the correct one in the documentation, found e.g. online on http://tahiti.oracle.com/.
Focus on PL/SQL User's Guide and Reference.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "QRY_STMT" when expecting one [message #347519 is a reply to message #347514]

Fri, 12 September 2008 00:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Report message to a moderator

Re: PLS-00103: Encountered the symbol "QRY_STMT" when expecting one [message #347545 is a reply to message #347517]

Fri, 12 September 2008 01:18

kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai

Junior Member

Thanks for the your help.
I have changed code to
ElseIf i.retention_type = 'M' then
qry_stmt :=

still I am getting below error.

LINE/COL ERROR
-------- -----------------------------------------------------------------
26/8 PLS-00103: Encountered the symbol "I" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "I" to continue.

26/31 PLS-00103: Encountered the symbol "THEN" when expecting one of
the following:
* & - + ; / at mod remainder rem <an exponent (**)> and or ||
multiset

34/17 PLS-00103: Encountered the symbol "MONTH" when expecting one of

Report message to a moderator

Re: PLS-00103: Encountered the symbol "QRY_STMT" when expecting one [message #347547 is a reply to message #347545]

Fri, 12 September 2008 01:21

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel

Report message to a moderator

Re: PLS-00103: Encountered the symbol "QRY_STMT" when expecting one [message #347549 is a reply to message #347514]

Fri, 12 September 2008 01:30

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Unfortunately for you, Oracle PL/SQL (as most of programming languages) is too accurate on correct syntax and spelling. It is a good thing as it reduces possible misinterpretations.
There is no ELSEIF keyword in IF statement construction. But, if you would consult documentation, you would surely find it yourself.

Report message to a moderator

Previous Topic:	explicit/implicit inner join
Next Topic:	varchar to number

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 07 14:42:55 CDT 2025