Rollup Function in Procedure

[cse]

I am getting an error in Rollup function in procedure level. I dont get an error if i use Rollup function in View. Anyone knows why this is happening?
Error information is

PLS-00201: identifier 'ROLLUP' must be declared

Any suggesion/help appreciated.

Thanks

[stecal]

If you're accessing tables/views in PL/SQL procedure or package and getting either ORA-1031 or ORA-942 (or PLS-201), but the same select/update/insert/delete works ok on SQL*Plus, then you need to check if the privileges have been granted to the user creating the procedure via a role.

[Shestakov]

In 8i u have to use dynamic sql.
In9i u may use rollup directly in pl/sql code.

[cse]

It works in PL/SQL version 8.1.7 and not in 8.1.5. Using dynamic sql, we can use rollup function in 8.1.5 PL Sql version
Thanks to Shestakov & stecal

[jmodic]

Originally posted by stecal
If you're accessing tables/views in PL/SQL procedure or package and getting either ORA-1031 or ORA-942 (or PLS-201), but the same select/update/insert/delete works ok on SQL*Plus, then you need to check if the privileges have been granted to the user creating the procedure via a role.

Not in this case. Here it is clearly evident the error is not caused by privileges not being granted directly - here the error is cause by the SQL parser in PL/SQL engine, which doesn't know about ROLLUP expression (to the contrary of his SQL parser brother in SQL engine, which is perfectly aware about ROLLUP).

Up until 9i Oracle uses two separate SQL parsers, and that one of PL/SQL engine is usually lagging behind for at least one release when it comes to new SQL expressions/features.

To overcome this problem inside PL/SQL, you must use dynamic SQL, either in its "natural" form (EXECUTE IMMEDIATE...) or in its "clasical" form (DBMS_SQL).