Your choices in 8i are:

o create a view that "hides" the case from PLSQL
o use dynamic sql

the PLSQL sql parser quite simply does not understand certain constructs like

o order by in a subquery
o CASE
o group by rollup | cube
o select ( select ... ) from t;
o TO_LOB()

In 9i, the PLSQL and database engine share a common parser meaning that as feaures are added to SQL, they'll instantly be avaialble in PLSQL.


You can use dynamic sql and use a %ROWTYPE using a "template" cursor. Consider:

ops$tkyte@ORA817.US.ORACLE.COM> declare
2 type rc is ref cursor;
3
4 cursor TEMPLATE is select ename, empno, rpad( '*', 30, '*' ) case_exp from emp;
5
6 l_rec TEMPLATE%rowtype;
7
8 l_cursor rc;
9
10 l_job varchar2(25) default 'CLERK';
11 begin
12 open l_cursor for
13 'select ename, empno, case when sal between 0 and 500 then ''LOW''
14 when sal between 501 and 1000 then ''MED''
15 else ''HIGH''
16 end
17 from emp
18 where job = :x'
19 using l_job;
20
21 loop
22 fetch l_cursor into l_rec;
23 exit when l_cursor%notfound;
24 dbms_output.put_line( l_rec.case_exp );
25 end loop;
26
27 close l_cursor;
28 end;
29 /
MED
HIGH
MED
HIGH

PL/SQL procedure successfully completed.


you just need to make a static cursor (no WHERE clause is ever needed) that selects representative columns.


followup to comment one

No -- the reason is simple.

In PLSQL, using the following block of code:

declare
l_ename varchar2(5) default '%A%';
begin
for x in ( select * from emp where ename like l_ename and sal > 55 )
loop
.....


the SQL submited will be:

SELECT * FROM EMP WHERE ENAME LIKE :b0 AND SAL > 55


PLSQL leaves constants along and just finds its variables and binds them. In SQLPLUS you would query:


select * from emp where ename like '%A%' and sal > 55;


Now, in sqlplus, if you want that to be "bound" for you -- in 816 and up you can:


SQL> alter session set cursor_sharing=force;
SQL> select * from emp where ename like '%A%' and sal > 55;

Now what gets executed is

select * from emp where ename like :sys_bv_0 and sal > :sys_bv_1

using this "autobind" feature, you have have applications that do not bind (bad application) and environments like SQLPlus use bind variables for you.