DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Using boolean constant in SELECT..INTO.. statement

  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Using boolean constant in SELECT..INTO.. statement

    Hi there,

    Can anyone tell me why the following does work in Oracle8i but not in Oracle9i?

    declare
    myvar BOOLEAN := false;
    c_True CONSTANT BOOLEAN := true;
    begin
    select c_True into myvar from dual;
    end;

    I get "PLS-00382: expression is of wrong type" when running this in Oracle9i, but no error is returned when an Oracle8i database is used.

    Thanks in advance,
    Javi

  2. #2
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    try this

    I don't know why it worked in 8 an not worked in 9...

    you can try this:

    DECLARE
    myvar BOOLEAN := FALSE;
    c_True CONSTANT BOOLEAN := TRUE;
    BEGIN
    myvar := c_True;
    END;
    Last edited by andrejm; 10-13-2003 at 09:07 AM.
    Andrej

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    Andrejm,

    Thanks for your reply.

    I have a stored procedure that uses the "SELECT c_True INTO FROM WHERE ..." statement and has always compiled when using Oracle8i. Now we have migrated to Oracle9i and the compilation of this stored proc has failed with the error I mentioned earlier.

    I do need to do this using a SELECT statement because the WHERE clause determines whether the variable should be set to true.

    Regards,
    Javi

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I suspect that it fails because boolean is not a SQL datatype.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by slimdave
    I suspect that it fails because boolean is not a SQL datatype.
    Exactly. BOOLEAN is a PL/SQL datatype - not a SQL datatype, so you can't do anything with booleans in SQL.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    However the original question is still walid and IMHO quite interested (I wasn't aware of that litle quirk): that odd "SELECT boolean_type_plsql_variable INTO ..." realy works under 8i (and previous releases) and not under 9i anymore.

    So why it worked under 8i and not under 9i anymore? I quess it's because of the common SQL parser in 9i that simply can not cope with non-SQL datatypes (boolean) - it's genuine SQL parser after all. In 8i and before PL/SQL engine had its own sepparate SQL parser, so it was easier for them to built in some exceptional behavior as the one that you obviously have used.

    And BTW, the way you used it is a bad programing practice. As andrejm has allready suggested, you should simply assign a value to the variable - simple PL/SQL assignment. And instead of building the procedural conditions into the WHERE clause you should use PL/SQL conditional logic (IF-ELSIF). Faster, easier to read and maintain, much more natural and efficient code.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Oct 2003
    Posts
    3
    Thanks for all the replies. Really appreciated.

    The select statement was being used to find out whether a particular record existed in a database table. Should the record exist, the boolean variable was being set to true until now (Oracle 8i and previous releases). It is not the way I would do it either (just maintaining existing code), but still I wanted to know what changed in Oracle 9i to invalidate the statement, for the obvious reason that the package will have to be modified in order to be able to compile it.

    Cheers,
    Javi

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