Using boolean constant in SELECT..INTO.. statement

[javierdiaz]

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

[andrejm]

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;

[javierdiaz]

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

[slimdave]

I suspect that it fails because boolean is not a SQL datatype.

[chrisrlong]

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

[jmodic]

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.

[javierdiaz]

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