Re: retrieve 0 records from SESSION_ROLES in package function - Help

You have the underlying symptom here
of the old problem:

    'I can do XXX from SQL*Plus, but when I write the     code into a package I get Oracle error 'insufficient privilege'"

Roles are disabled during package execution, and the package executes with the directly granted privileges of the package creator.

    all roles disable = no rows in SESSION_ROLES.

(this isn't true for "AUTHID CURRENT USER" packages in 8.1 of course, which execute with all the current roles of the user)

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Peter Yu wrote in message <3b6e0809$1_at_sspwb100.sp.edu.sg>...


>I wrote a packaged function that retrieve and count the number of roles of

 a


>session:

>

>In the package function the cursor

>    cursor c_role is

>       select role from SESSION_ROLES;

>

>was used to for the purpose.

>

>To my surprise, the function always return zero count.

>

>If the cursor was changed to retrieve from any other table it works, I

 tried


>it ok for USER_ROLE_PRIVS.  Now why does it not works for SESSION_ROLES ?

>

>I have tried using the same function as named function in a PL block and it

>works !

>

>Now how can i get it to work in packaged procedure.

>

>Any help is appreciated.

>

>

>Peter Yu

>

>