Rollup Function in Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rollup Function in Procedure

  1. #1
    Join Date
    Nov 2002
    Posts
    6

    Rollup Function in Procedure

    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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    In 8i u have to use dynamic sql.
    In9i u may use rollup directly in pl/sql code.

  4. #4
    Join Date
    Nov 2002
    Posts
    6
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width