Re: PL/SQL Problem

If you're in 8i, use the predefine package :

DBMS_UTILITY.COMPILE_SCHEMA Procedure
This procedure compiles all procedures, functions, packages, and triggers in the specified schema. After calling this procedure, you should select from view ALL_OBJECTS for items with status of INVALID to see if all objects were successfully compiled.

To see the errors associated with INVALID objects, you may use the Enterprise Manager command:

SHOW ERRORS <type> <schema>.<name>

Syntax
DBMS_UTILITY.COMPILE_SCHEMA (
   schema VARCHAR2);

In article <91d9fc$stj$1_at_nnrp1.deja.com>,   steveee_ca_at_my-deja.com wrote:
> Hi AJ,
>
> I think your problem is in your loop syntax..I got it to work by
> declaring a cursor first instead of using the select in the loop..
> Like this,
>
> set serveroutput on
>
> declare
> sql_statement varchar2(200);
> cursor_id number;
> ret_val number;
>
> cursor invcursor is select object_type, owner, object_name
> from sys.dba_objects o,
> sys.order_object_by_dependency d
> where o.object_id = d.object_id(+)
> and o.status = 'INVALID'
> and o.object_type in ('PACKAGE','PACKAGE BODY',
> 'FUNCTION',
> 'PROCEDURE', 'TRIGGER',
> 'VIEW')
> order by d.dlevel desc, o.object_type;
>
> BEGIN
> cursor_id := dbms_sql.open_cursor;
> for invalid in invcursor loop
>
> if invalid.object_type = 'PACKAGE BODY' then
> sql_statement := 'alter package ' || invalid.owner || '.' ||
> invalid.object_name || ' compile body';
> else
> sql_statement := 'alter '|| invalid.object_type || ' ' ||
> invalid.owner
> || '.' || invalid.object_name || ' compile';
> end if;
>
> dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
>
> ret_val := dbms_sql.execute(cursor_id);
>
> dbms_sql.close_cursor(cursor_id);
>
> dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
> invalid.object_name, 32)||' :
> compiled');
> end loop;
>
> end;
> /
> I should say I didn't try to validate the code itself and I have no
> invalid objects in my database..I've merely tried to correct what I
> thought was a syntax issue. This completes successfully on my machine.
> Hope this helps,
>
> Steve
> In article <91cvhl$le3$1_at_nnrp1.deja.com>,
> alarkin77_at_my-deja.com wrote:
> > I have executed the following PL/SQL to compile invalid packages,
 etc.
> >
> > It gives the me the errors displayed at the bottom which i do not
> > understand. Can anyone help???
> >
> > declare
> > sql_statement varchar2(200);
> > cursor_id number;
> > ret_val number;
> > begin
> >
> > for invalid in (select object_type, owner, object_name
> > from sys.dba_objects o,
> > sys.order_object_by_dependency d
> > where o.object_id = d.object_id(+)
> > and o.status = 'INVALID'
> > and o.object_type in ('PACKAGE', 'PACKAGE
> > BODY', 'FUNCTION',
> > 'PROCEDURE', 'TRIGGER',
> > 'VIEW')
> > order by d.dlevel desc, o.object_type) loop
> >
> > if invalid.object_type = 'PACKAGE BODY' then
> > sql_statement := 'alter package ' || invalid.owner || '.' ||
> > invalid.object_name || ' compile body';
> > else
> > sql_statement := 'alter '|| invalid.object_type || ' ' ||
> > invalid.owner
> > || '.' || invalid.object_name || ' compile';
> > end if;
> >
> > cursor_id := dbms_sql.open_cursor;
> >
> > dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
> >
> > ret_val := dbms_sql.execute(cursor_id);
> >
> > dbms_sql.close_cursor(cursor_id);
> >
> > dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
> > invalid.object_name, 32)||' :
> > compiled');
> > end loop;
> >
> > end;
> >
> > -- Error as follows
> > declare
> > *
> > ERROR at line 1:
> > ORA-01436: CONNECT BY loop in user data
> > ORA-06512: at line 7
> >
> > Doe anyone know how to solve this or a better way to recompile all
> > invalid packages,procedures, etc.
> >
> > Thanks in advance,
> > AJ
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 15 2000 - 12:57:24 CST