Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00905 -- Why am I getting this
PLS-00905 -- Why am I getting this [message #224815] Thu, 15 March 2007 10:38 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I don't understand why I'm getting a PLS-00905 error. I'm doing the same thing as I do in another schema.

When I try to compile the globalVariables within the BLACKBOARD schema, I get PLS-00905: object BLACKBOARD.WEB is invalid. It works fine with the other schemas as you can see my other grants. This is with Oracle 10.2.0.1.0

BLACKBOARD schema

CREATE OR REPLACE PACKAGE globalVariables IS

  status                  web.user_level.user_level_descr%type; 
  userLevel               web.user_level.user_level%type;
  userLevelDescr          web.user_level.user_level_descr%type;

  ssoId                   web.users.sso_id%type;
  alternateSSOId          web.users.sso_id%type;
  employeeEmplid          web.users.emplid%type;
  alternateEmplid         web.users.emplid%type;

END globalVariables;

WEB schema

CREATE TABLE USERS
(
  EMPLID          VARCHAR2(11 BYTE),
  SSO_ID          VARCHAR2(20 BYTE),
  APPLICATION_ID  NUMBER,
  STATUS          VARCHAR2(1 BYTE),
  EFFSEQ          NUMBER,
  USER_LEVEL      NUMBER,
  CREATE_SSO_ID   VARCHAR2(20 BYTE),
  EFFDT           DATE
)

[B]GRANT SELECT ON  USERS TO BLACKBOARD;[/B]

GRANT INSERT, SELECT ON  USERS TO HSCP;

GRANT INSERT, SELECT ON  USERS TO IS_LEAVE_REPORTS;

GRANT SELECT ON  USERS TO ITUNESU;

CREATE TABLE USER_LEVEL
(
  APPLICATION_ID    NUMBER,
  USER_LEVEL        NUMBER,
  STATUS            VARCHAR2(1 BYTE),
  EFFSEQ            NUMBER,
  USER_LEVEL_DESCR  VARCHAR2(70 BYTE),
  CREATE_SSO_ID     VARCHAR2(20 BYTE),
  EFFDT             DATE
)

[B]GRANT SELECT ON  USER_LEVEL TO BLACKBOARD;[/B]

GRANT SELECT ON  USER_LEVEL TO HSCP;

GRANT SELECT ON  USER_LEVEL TO IS_LEAVE_REPORTS;

Re: PLS-00905 -- Why am I getting this [message #224838 is a reply to message #224815] Thu, 15 March 2007 12:16 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What the message is telling you is that it is looking for an object called WEB in the BLACKBOARD schema, not that it can't see the object USER_LEVEL in the WEB Schema.

I suspect you did not create or grant as you have said so. This is not a cut and paste of your session as I see no feedback from your commands, and there is no semi-colon or slash ending your CREATE TABLE statements.

[Updated on: Thu, 15 March 2007 12:23]

Report message to a moderator

Re: PLS-00905 -- Why am I getting this [message #224841 is a reply to message #224838] Thu, 15 March 2007 12:35 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I cut and pasted from Toad. The table is already defined within the Web schema.

You can see that I have Granted BLACKBOARD, HSCP and IS_LEAVE_REPORTS select access.

CREATE TABLE USER_LEVEL
(
  APPLICATION_ID    NUMBER,
  USER_LEVEL        NUMBER,
  STATUS            VARCHAR2(1 BYTE),
  EFFSEQ            NUMBER,
  USER_LEVEL_DESCR  VARCHAR2(70 BYTE),
  CREATE_SSO_ID     VARCHAR2(20 BYTE),
  EFFDT             DATE
)
TABLESPACE WEB
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PK_USER_LEVEL ON USER_LEVEL
(APPLICATION_ID, USER_LEVEL, STATUS, EFFSEQ)
LOGGING
TABLESPACE WEB
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE USER_LEVEL ADD (
  CONSTRAINT PK_USER_LEVEL
 PRIMARY KEY
 (APPLICATION_ID, USER_LEVEL, STATUS, EFFSEQ)
    USING INDEX 
    TABLESPACE WEB
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));


GRANT SELECT ON  USER_LEVEL TO BLACKBOARD;

GRANT SELECT ON  USER_LEVEL TO HSCP;

GRANT SELECT ON  USER_LEVEL TO IS_LEAVE_REPORTS;


Here's the globalVariables package on the HSCP schema. It has no problem reading the table.

CREATE OR REPLACE PACKAGE globalVariables IS

  administrator                web.user_level.user_level_descr%type default 'Administrator';
  assistance                   web.user_level.user_level_descr%type default 'Assistance';
  assignIdNumber               web.user_level.user_level_descr%type default 'Assign Id Number';
  classList                    web.user_level.user_level_descr%type default 'Class List';
  classListFinalized           web.user_level.user_level_descr%type default 'Class List Finalized';
  classWithdraw                web.user_level.user_level_descr%type default 'Class Withdraw';
  disabled                     web.user_level.user_level_descr%type default 'Disabled';
  displayCourses               web.user_level.user_level_descr%type default 'Display Courses';
  displayStudent               web.user_level.user_level_descr%type default 'Display Student';
  displayStudents              web.user_level.user_level_descr%type default 'Display Students';
  downloadStudents             web.user_level.user_level_descr%type default 'Download Students';
  downloadCourses              web.user_level.user_level_descr%type default 'Download Courses';
  downloadInformation          web.user_level.user_level_descr%type default 'Download Information';
  downloadReport               web.user_level.user_level_descr%type default 'Download Report';
  duplicateMatches             web.user_level.user_level_descr%type default 'Duplicate Matches';
  edit                         web.user_level.user_level_descr%type default 'Edit';
  enrollStudent                web.user_level.user_level_descr%type default 'Enroll';
  enrollStudents               web.user_level.user_level_descr%type default 'Enroll Students';
  emailHSCP                    web.user_level.user_level_descr%type default 'Email HSCP';
  find                         web.user_level.user_level_descr%type default 'Find';
  financialAssistance          web.user_level.user_level_descr%type default 'Financial Assistance';
  inactive                     web.user_level.user_level_descr%type default 'I';
  instructor                   web.user_level.user_level_descr%type default 'Instructor';
  instructorRegistration       web.user_level.user_level_descr%type default 'Instructor Registration';
  generatingReport             web.user_level.user_level_descr%type default 'Generating Report';
  highSchool                   web.user_level.user_level_descr%type default 'High Schools';
  highSchoolHistory            web.user_level.user_level_descr%type default 'High School History';
  home                         web.user_level.user_level_descr%type default 'H';
  logout                       web.user_level.user_level_descr%type default 'logout';
  login                        web.user_level.user_level_descr%type default 'login';
  loginError                   web.user_level.user_level_descr%type default 'login-error';
  mainMenu                     web.user_level.user_level_descr%type default 'Main Menu';
  newAdministrator             web.user_level.user_level_descr%type default 'New Administrator';
  newInstructor                web.user_level.user_level_descr%type default 'New Instructor';
  newHighSchool                web.user_level.user_level_descr%type default 'New High School';
  newSemester                  web.user_level.user_level_descr%type default 'New Semester';
  no                           web.user_level.user_level_descr%type default 'N';
  noneSelected                 web.user_level.user_level_descr%type default 'None Selected';
  pageLoading                  web.user_level.user_level_descr%type default 'Page Loading';
  reportEnrollmentError        web.user_level.user_level_descr%type default 'Report Enrollment Error';
  reportCourseClassList        web.user_level.user_level_descr%type default 'Report Course Class List';
  reportUmkcOutstandingBalance web.user_level.user_level_descr%type default 'Report Umkc Outstanding Balance';
  school                       web.user_level.user_level_descr%type default 'S';
  search                       web.user_level.user_level_descr%type default 'Search';
  searchName                   web.user_level.user_level_descr%type default 'Search Name';
  searchSSN                    web.user_level.user_level_descr%type default 'Search SSN';
  ssnConfirmation              web.user_level.user_level_descr%type default 'SSN Confirmation';
  status                       web.user_level.user_level_descr%type; 
  studentCorrection            web.user_level.user_level_descr%type default 'Student Correction';
  studentEnroll                web.user_level.user_level_descr%type default 'Student Enroll';
  studentNotFound              web.user_level.user_level_descr%type default 'Student Not Found';
  submit                       web.user_level.user_level_descr%type default 'Submit';
  superAdministrator           web.user_level.user_level_descr%type default 'Super Administrator';
  server                       web.user_level.user_level_descr%type default 'Server';
  timeout                      web.user_level.user_level_descr%type default 'Timeout';
  userLogin                    web.user_level.user_level_descr%type default 'User Login';
  updateStudent                web.user_level.user_level_descr%type default 'Update Student';
  viewUpdateAdministrator      web.user_level.user_level_descr%type default 'Administrator';
  viewUpdateHighSchool         web.user_level.user_level_descr%type default 'High School';
  viewUpdateSemester           web.user_level.user_level_descr%type default 'Semester';
  viewUpdateInstructor         web.user_level.user_level_descr%type default 'Instructor';
  withdraw                     web.user_level.user_level_descr%type default 'Withdraw';
  yes                          web.user_level.user_level_descr%type default 'Y';
  
END globalVariables;

[Updated on: Thu, 15 March 2007 12:36]

Report message to a moderator

Re: PLS-00905 -- Why am I getting this [message #224851 is a reply to message #224841] Thu, 15 March 2007 13:50 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what Oracle says about the error:
Oracle
PLS-00905: object string is invalid

Cause: An invalid package specification or stored subprogram was referenced. A package specification or stored subprogram is invalid if its source code or any database object it references has been DROPped, REPLACEd, or ALTERed since it was last compiled.

Action: Find out what invalidated the package specification or stored subprogram, then make sure that Oracle can recompile it without errors.

If it doesn't help, try this: forget about TOAD. Open SQL*Plus session, copy and paste CREATE PACKAGE script and execute it again. Type SHOW ERRORS and see what it says.
Previous Topic: Create sequence.
Next Topic: stored procedure with curson quering data dictonary view
Goto Forum:
  


Current Time: Tue Dec 06 08:18:39 CST 2016

Total time taken to generate the page: 0.10336 seconds