CASE statement in procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: CASE statement in procedure

  1. #1
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    13

    Exclamation 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!!!

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    13
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    13
    Cool stuff......thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width