PL/SQL Problem

From: <alarkin77_at_my-deja.com>
Date: Fri, 15 Dec 2000 11:33:08 GMT
Message-ID: <91cvhl$le3$1@nnrp1.deja.com>

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/ Received on Fri Dec 15 2000 - 05:33:08 CST