The reason for the needed GRANT is because PL/SQL will not use privileges
granted by a ROLE ; you need direct SELECT access to the objects in
question.
"DA Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3E6C45A8.478E3A16_at_exesolutions.com...
> Greg H wrote:
>
> > Hey Folks,
> >
> > I'm just starting to learn a little PL/SQL (enough to be dangerous). I
was
> > trying to create a simple package that referenced the
hr.emp_details_view
> > example view. Unfortunately, I get the following error in reference to
this
> > view:
> >
> > PL/SQL: ORA-00942: table or view does not exist
> >
> > I'm thinking this is a scope or permissions issue since I seem to be
able to
> > access this table using a straight SQL query, but I'm not sure what I
need
> > to do to correct it. Any suggestions?
> >
> > Thanks,
> >
> > - Greg.
> >
> > -------------------
> > CREATE OR REPLACE PACKAGE test_package
> > AS
> > PROCEDURE pl(message VARCHAR2);
> >
> > PROCEDURE emp_details;
> > END test_package;
> > /
> >
> > -------------------
> > CREATE OR REPLACE PACKAGE BODY test_package
> > AS
> >
> > PROCEDURE pl(message VARCHAR2)
> > IS
> > BEGIN
> > DBMS_OUTPUT.PUT_LINE(message);
> > END pl;
> >
> > PROCEDURE emp_details
> > IS
> > BEGIN
> > pl('emp_details stub()');
> > FOR emps IN
> > (SELECT first_name, last_name, city, country_name, salary
> > FROM hr.emp_details_view)
> > LOOP
> > <<list_details>>
> > pl(emps%ROWCOUNT || ') ' ||
> > emps.first_name || ', ' ||
> > emps.last_name || ', ' ||
> > emps.city || ', ' ||
> > emps.country_name || ', ' ||
> > emps.salary );
> > END LOOP list_details;
> > END emp_details;
> >
> > END test_package;
> > /
>
> You need to log on as the owner of the table or view and:
>
> GRANT SELECT ON <object_name> TO <schema_name>
>
> that owns the package. Then either identify the object as
> schema_name_dot_object_name
>
> or create a synonym for it.
>
> Daniel Morgan
>
Received on Mon Mar 10 2003 - 04:44:06 CST
