Printable View
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
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.
In 8i u have to use dynamic sql.
In9i u may use rollup directly in pl/sql code.
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
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).Quote:
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.
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).