I am trying to write a pl/sql proc. that will compile invalid objs?

[PaullyB]

I am trying to write a procedure that will compile all invalid objects under a particular schema in the database using dynamic sql. Here is an example of the procedure I have come up with that does not compile:

CREATE OR REPLACE PROCEDURE TWPEDB.TEST
AS

CURSOR COMPILE_CURS IS
select 'alter ' || to_char(OBJECT_TYPE) || ' ' || to_char(OWNER) || '.' || to_char(OBJECT_NAME) || ' compile;'
from SYS.DBA_OBJECTS
where OWNER = 'TWPEDB'
AND STATUS = 'INVALID';

THE_STRING VARCHAR2(500);
cid INTEGER;

BEGIN

OPEN COMPILE_CURS;

LOOP

FETCH COMPILE_CURS
INTO THE_STRING;
EXIT WHEN COMPILE_CURS%NOTFOUND;
/* Open new cursor and return cursor ID. */
cid := DBMS_SQL.OPEN_CURSOR;
/* Parse and immediately execute the compile statement. */
DBMS_SQL.PARSE(cid, THE_STRING, dbms_sql.v7);
/* Close cursor. */
DBMS_SQL.CLOSE_CURSOR(cid);
END LOOP;

CLOSE COMPILE_CURS;
EXCEPTION
/* If an exception is raised, close cursors before exiting. */
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
CLOSE COMPILE_CURS;
END;


The problem I have is that this procedure does not compile because of the following error:

PLS-00320: the declaration of the type of this expression is incomplete or malformed

Cause: In a declaration, the name of a variable or cursor is misspelled or the declaration makes a forward reference. Forward references are not allowed in PL/SQL. A variable or cursor must be declared before it is referenced it in other statements, including other declarative statements. For example, the following declaration of dept_rec raises this exception because it refers to a cursor not yet declared:
DECLARE
dept_rec dept_cur%ROWTYPE;
CURSOR dept_cur IS SELECT ...
...

I am assuming my problem is that the cursor query is referencing the DBA_OBJECTS table, of which this procedure has a record.

Do have any other ideas on how to do this? I would love to get this done in a procedure.

Thanks,

PaullyB

 

[carp]

I believe your problem is the extraneous "to_char"s in your cursor.
You will get an error trying to convert a character string to a character string; to_char expects a number or date!

Try replacing your cursor with this:

CURSOR COMPILE_CURS IS
select 'alter ' ||OBJECT_TYPE|| ' ' ||OWNER|| '.' || OBJECT_NAME|| 'compile;'
from SYS.DBA_OBJECTS
where OWNER = 'TWPEDB'
AND STATUS = 'INVALID'
AND OBJECT_TYPE <> 'PACKAGE_BODY';

 

[karluk]

I always thought that this sort of schema level recompile was done by dbms_utility.compile_schema('schema_name'). I'm not sure that I have the details straight. Is the recompile that you're doing different, or is your intention to hit only the invalid objects and leave everything else alone?

 

[PaullyB]

Please disregard the &quot;to_char&quot;'s in my example. The actual procedure I am trying to compile does not have them. My fault there.

I am new with system packages, so I do not know everything that is available. I tried the dbms_utility.compile_schema procedure. Everything worked okay, except it took a long time to compile the whole schema. Any ideas on how to only compile the objects that are invalid? This would definitely save a lot of time. Thanks...

 

[carp]

Karluk -
Yes, DBMS_UTILITY actually does this for you and is the way I would handle the problem.

 

[PaullyB]

How do I deal with invalid views? The DBMS_UTILITY package does not seem to do this.

 

[karluk]

You are right. The documentation says that DBMS_UTILITY omits views. I think the reason is that views are automatically recompiled the first time they are used. However, if you want to explicitly recompile views as well, that would be a good reason to write your own procedure.

That brings us back to your original question. Are you still having problems compiling your procedure? I took the text you posted and, leaving out the extraneous to_char and with appropriate changes to literals, successfully compiled it on my test server. I haven't tested it to see if it's working, but you are probably very close. Unfortunately I can't give you any advice on the PLS-00320 error, as I was unable to reproduce it.

 

[RMT]

I just hate cursors. Here a script to run in Sqlplus to be run as the schema owner:

-- REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-- REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-- REM
-- REM Recompileio.SQL
-- REM
-- REM Make sure to run this only under appropriate user: FOREST
-- REM
-- REM This script creates a second sql script which compiles all
-- REM tables, views, sequences, and stored procedures.
-- REM
-- REM This script is designed to be run in an account with the schema owner
-- REM
-- REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-- REM
SET TERM OFF;
TTITLE OFF;
BTITLE OFF;
SET PAGESIZE 0;
SET VERIFY OFF;
SET HEADING OFF;
SET RECSEP OFF;
--REM
SPOOL C:\tmp_recompile_all.sql
--REM


SELECT 'ALTER '||object_type, object_name ||' COMPILE ;'
FROM user_objects
Where status = 'INVALID';

--REM
SPOOL OFF;
--REM
START C:\tmp_recompile_all.sql
HOST erase C:\tmp_recompile_all.sql
--REM
EXIT