Re: PL/SQL Problem

From: <steveee_ca_at_my-deja.com>
Date: Fri, 15 Dec 2000 14:22:41 GMT
Message-ID: <91d9fc$stj$1@nnrp1.deja.com>

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