If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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 ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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.