-
CASE statement in procedure
Hi,
I am unable to use CASE statement within a stored proc. Here is my test code:
PROCEDURE open_cv (generic_cv IN OUT PACK.CURSOR_TYPE) IS
BEGIN
OPEN generic_cv
FOR SELECT CASE WHEN corr_acc_no < 1000000 THEN 'LOW'
ELSE 'HIGH' END
FROM acgr;
END;
When I compile the SP, I get the follwoing error message:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( * - + all mod null
table avg count cu
Can anyone please help me!!!
-
Oracle, in its infinite wisdom, had 2 SQL engines - one for SQL and one for PL/SQL. And using even more intelligence, they put new features in the SQL-SQL engine before the PL/SQL-SQL engine. Therefore, in order to use analytical functions and case statements in 8i, you need to do dynamic SQL so that the SQL-SQL engine is used insted of the PL/SQL-SQL engine. Brilliant, eh? Mind you, it finally dawned on them that this was not such a good idea and they now have only one engine in 9i.
- Chris
-
Hi Chris,
Thanks for your prompt reply. I am new to ORACLE. Would you be kind enough to convert my code in dynamic SQL or otherwise let me know the syntax.
Cheers
-
PROCEDURE open_cv (generic_cv IN OUT PACK.CURSOR_TYPE) IS
BEGIN
OPEN generic_cv
FOR 'SELECT CASE WHEN corr_acc_no < 1000000 THEN ''LOW''
ELSE ''HIGH'' END
FROM acgr';
END;
- Chris
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|