Re: Simple PL/SQL Problem

On Tue, 31 Mar 1998, OraGod wrote:

:)Sean,
:)
:)It seems as if the "AS" is not necessary in this statement.
:)
:)NewDBAMan,
:)Jack
:)
:)Sean Phillips wrote:
:)
:)> Hello All,
:)>
:)> I am as green as they come, so forgive me if this the answer to this is
:)> painfully obvious. I have no one else in the company I can ask, so
:)> please humor me.
:)>
:)> My problem is this: I cannot reference a table in another users schema
:)> while trying to compile a PL/SQL procedure. For example, a table exists
:)> called 'students' created by a user 'bozo'. The table has 3 elements:
:)> first_name, last_name, student_id.
:)>
:)> I am connected as 'krusty' and I am trying to compile a procedure that
:)> references the 'students' table of 'krusty's. For each variable in the
:)> table, I reference them as follows:
:)>
:)> v_FirstName bozo.students.first_name%TYPE
:)> v_LastName bozo.students.last_name%TYPE
:)> v_StudentID bozo.students.student_id%TYPE
:)>
:)> Later, the procedure tries to insert into the table as follows:
:)>
:)> INSERT INTO bozo.students(v_FirstName, v_LastName,v_StudentID) AS
:)> VALUES (n_FirstName,n_LastName,n_StudentID) ;
:)>
:)> I get a PLS-201 error which is complaining about me not declaring the
:)> 'BOZO.STUDENTS' identifier.
:)>
:)> I can successfully compile this procedure all day long as the user bozo,
:)> but as krusty I constantly get the error described above. I have
:)> granted damn near every privilege to krusty known to humankind.
:)>
:)> Any hints, tips, clues would be greatly appreciated.
:)>
:)> Scuffling,
:)> Sean Phillips
:)

So you have granted all the privileges to krusty? Have you granted them to krusty individually, or to a role krusty is part of?
Unfortunately it seems like PL/SQL can't handle role privileges. I got the "Identifier not declared" error when doing a similar thing. Granting the priviliges to the individual user solved the problem.

Just a thought

          Christian Received on Wed Apr 01 1998 - 00:00:00 CST