| 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'')';
|
|
|
|
|
|
|
|
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
