Re: Newbie PL/SQL Question

On a general tip regarding this,

do a SET ROLE NONE; <this disables all role privileges>

if you can then select from the object you will be OK ; but if you get a 942 then it's a privileges thing.

HTH check out the PL/SQL manual - there is a section "Greg H" <greg_horie_at_telus.net> wrote in message news:V12ba.8940$Ty5.834435_at_news0.telusplanet.net...
> Thanks!
>
> "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message
> news:W%Zaa.1503$pK2.2176_at_news.indigo.ie...
> > 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 - 09:42:35 CST