Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: Encountered the symbol "QRY_STMT" when expecting one
PLS-00103: Encountered the symbol "QRY_STMT" when expecting one [message #347514] Fri, 12 September 2008 00:09 Go to next message
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'')';
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 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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


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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: explicit/implicit inner join
Next Topic: varchar to number
Goto Forum:
  


Current Time: Sun Dec 04 18:55:00 CST 2016

Total time taken to generate the page: 0.10090 seconds