Zara,

It looks like the procedures can't recompile because the GLOGOWNER user
doesn't have rights to access the v$ views such as v$parameter (10/32),
v$session (100,21), as well as some of the DBA tables DBA_OBJECTS,
DBA_CONSTRAINTS, etc. I suspect that the role APP_OWNER does not have the
"SELECT ANY TABLE" privilege. You can check to see what pivileges APP_OWNER
has with the following SQL (run as SYSTEM)

SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='APP_OWNER';

And this should return the following

PRIVILEGE
----------------------------------------------------------------------------
CREATE PUBLIC SYNONYM
COMMENT ANY TABLE
ALTER SESSION
CREATE SESSION
CREATE DATABASE LINK
CREATE VIEW
DROP PUBLIC SYNONYM
CREATE TABLE
CREATE TYPE
CREATE ANY CONTEXT
CREATE SEQUENCE
CREATE TRIGGER
CREATE PROCEDURE
CREATE ANY SYNONYM
SELECT ANY TABLE

15 rows selected.

If as I suspect you don't have "SELECT ANY TABLE" you need to revisit
"create_glog_roles.sql", did you run this script & did you run it as SYSTEM
(not SYS)? You can rerun this script as system (which will drop and recreate
the roles) then manually grant the roles & any missing privileges back to
the users by following along with the grant statements in the
create_glog_users.sql script. This should help you avoid having to drop the
users & data and recreate everything. For example the glogowner grants in
create_glog_users.sql are

grant app_owner to glogowner;
grant app_user to glogowner;
grant app_user_select to glogowner with admin option;
grant create any table, alter any trigger, alter any table, select any
table, create any sequence, create any trigger, analyze any,
create user, drop user, create procedure,
create public synonym, drop public synonym, CREATE MATERIALIZED VIEW to
glogowner;
grant execute on dbms_rls to glogowner;
GRANT aq_administrator_role TO glogowner;
GRANT EXECUTE ON DBMS_AQ TO glogowner;
GRANT EXECUTE ON DBMS_AQADM TO glogowner;
GRANT EXECUTE ON DBMS_AQIN TO glogowner;
GRANT execute on dbms_pipe TO glogowner;

And so on for each user...

Then go back and try running recompile_invalid_objects.sql

-Alan

On 12/14/06 1:00 AM, "idqpbi" <idqpbi (AT) gmail (DOT) com> wrote:

> Hi,
>
> Anybody experienced this before? This is the last SQL script to run
> before I could actually start to verify my imported data. All of the
> previous scripts were OK, even the import after I took Stephen's advice.
> Sigh...... :-(
> Maybe Alan or Stephen could help again. :-)
>
> Here is what I have in the terminal window: (sorry it's kind of long
> since I ran the same scripts twice hoping to fix it)
> ------------------------------------------------------------------------------
> ---------------------------------------------------
>
> _*1st Run*_
>
> SQL> @recompile_invalid_objects.sql
>
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
>
>
> List of first 50 Invalid objects after recompile.
> ================================================
> DBHEALTH PACKAGE BODY
> CREATE_TABLE PACKAGE BODY
> PKG_PURGE PACKAGE BODY
>
> Errors for PACKAGE BODY CREATE_TABLE:
> 55/23 PL/SQL: ORA-00942: table or view does not exist
>
> 55/7 PL/SQL: SQL Statement ignored
>
>
> Errors for PACKAGE BODY DBHEALTH:
> 10/14 PL/SQL: SQL Statement ignored
>
> 10/32 PL/SQL: ORA-00942: table or view does not exist
>
> 100/21 PL/SQL: ORA-00942: table or view does not exist
>
> 106/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 120/20 PL/SQL: SQL Statement ignored
>
> 120/77 PL/SQL: ORA-00942: table or view does not exist
>
> 53/14 PL/SQL: SQL Statement ignored
>
> 53/84 PL/SQL: ORA-00942: table or view does not exist
>
> 57/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 64/14 PL/SQL: SQL Statement ignored
>
> 64/56 PL/SQL: ORA-00942: table or view does not exist
>
> 68/40 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 75/14 PL/SQL: SQL Statement ignored
>
> 75/48 PL/SQL: ORA-00942: table or view does not exist
>
> 79/44 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 98/14 PL/SQL: SQL Statement ignored
>
>
> Errors for PACKAGE BODY PKG_PURGE:
> 1339/4 PL/SQL: SQL Statement ignored
>
> 1339/43 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/29 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/5 PL/SQL: SQL Statement ignored
>
> 1913/5 PL/SQL: SQL Statement ignored
>
> 1913/61 PL/SQL: ORA-00942: table or view does not exist
>
> 1921/8 PLS-00364: loop index variable 'JOB_PARAM_REC' use is
> invalid
>
> 1938/7 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
> 1950/15 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
>
>
> Invalid objects before Recompile....
>
> 45
>
> *Invalid objects after Recompile....
>
> 3 *
>
>
>
> Some objects have compiled successfully.
>
>
> Please re-run recompile_invalid_objects to compile the rest of invalid
> objects.
>
> _*2nd Run*_
>
> SQL> @recompile_invalid_objects.sql
>
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
>
>
> List of first 50 Invalid objects after recompile.
> ================================================
> DBHEALTH PACKAGE BODY
> CREATE_TABLE PACKAGE BODY
> PKG_PURGE PACKAGE BODY
>
> *Errors for PACKAGE BODY CREATE_TABLE: *
> 55/23 PL/SQL: ORA-00942: table or view does not exist
>
> 55/7 PL/SQL: SQL Statement ignored
>
>
> *Errors for PACKAGE BODY DBHEALTH: *
> 10/14 PL/SQL: SQL Statement ignored
>
> 10/32 PL/SQL: ORA-00942: table or view does not exist
>
> 100/21 PL/SQL: ORA-00942: table or view does not exist
>
> 106/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 120/20 PL/SQL: SQL Statement ignored
>
> 120/77 PL/SQL: ORA-00942: table or view does not exist
>
> 53/14 PL/SQL: SQL Statement ignored
>
> 53/84 PL/SQL: ORA-00942: table or view does not exist
>
> 57/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 64/14 PL/SQL: SQL Statement ignored
>
> 64/56 PL/SQL: ORA-00942: table or view does not exist
>
> 68/40 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 75/14 PL/SQL: SQL Statement ignored
>
> 75/48 PL/SQL: ORA-00942: table or view does not exist
>
> 79/44 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 98/14 PL/SQL: SQL Statement ignored
>
>
> *Errors for PACKAGE BODY PKG_PURGE: *
> 1339/4 PL/SQL: SQL Statement ignored
>
> 1339/43 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/29 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/5 PL/SQL: SQL Statement ignored
>
> 1913/5 PL/SQL: SQL Statement ignored
>
> 1913/61 PL/SQL: ORA-00942: table or view does not exist
>
> 1921/8 PLS-00364: loop index variable 'JOB_PARAM_REC' use is
> invalid
>
> 1938/7 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
> 1950/15 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
>
>
> Invalid objects before Recompile....
>
> 3
>
> *Invalid objects after Recompile....
>
> 3 *
>
>
>
> The number of invalid objects is still the same after recompile.
>
>
> Please analyze the existing invalid objects.
>
> SQL>
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ---
>
> The only thing I could think of was I failed to grant execute permission
> on "dbms_rls", "dbms_aqin" and "dbms_pipe" when running the
> "create_glog_users.sql" script. So I ended up connecting as SYS user
> instead and manually ran those four statements. However it does not look
> like that would cause these invalid objects...........My head is going
> to explode, I think.
>
> Thanks in advance for any help!!
>
> Zara
>
> You are receiving this mail as a subscriber of the Oracle Transportation
> Management SIG List Server. This list is provided by Solution Beacon,
> LLC for the Oracle Transportation Management SIG to facilitate distribution
> of issues and topics related to Oracle Transportation Management. If you
> do not want to receive future mail from the list, send email to
> majordomo (AT) solutionbeacon (DOT) net with the words "unsubscribe otmsig" (without
> quotes) in the body of the email.
> If you need help, please contact mweiss (AT) solutionbeacon (DOT) com.
> Thank you!



You are receiving this mail as a subscriber of the Oracle Transportation
Management SIG List Server. This list is provided by Solution Beacon,
LLC for the Oracle Transportation Management SIG to facilitate distribution
of issues and topics related to Oracle Transportation Management. If you
do not want to receive future mail from the list, send email to
majordomo (AT) solutionbeacon (DOT) net with the words "unsubscribe otmsig" (without
quotes) in the body of the email.
If you need help, please contact mweiss (AT) solutionbeacon (DOT) com.
Thank you!