This is an example of a dynamic SQL which recompiles all the invalid
Packages.
PROCEDURE compile_invalid_objects(obj_owner in varchar2)
iS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
for invalids in (select object_name,object_type from all_objects
where UPPER(owner)='LMT0'
AND object_TYPE = 'PACKAGE BODY'
AND STATUS='INVALID')
LOOP
cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name,
'ALTER PACKAGE '||INVALIDS.OBJECT_NAME||' COMPILE',
dbms_sql.NATIVE);
rows_processed := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
END;
^¥x <atsai_at_xentury.com.tw> wrote in message
news:83gdhb$a7a_at_netnews.hinet.net...
> Hi, all:
>
> Can anyone has the experience about Writing Dynamic StoreProcedure, or
> any other suggestion to dynamic generate StoreProcedure ?
>
> Thanks your Suggestion!
>
> Best Regard,
> Allen
>
>
Received on Sun Dec 19 1999 - 19:44:22 CST
