===============================================================================
NB: This e-mail and its contents are subject to the Telkom SA Limited
e-mail legal notice which can be viewed at:
http://www.telkom.co.za/TelkomEMailLegalNotice.PDF
===============================================================================

Hi,

I have some invalid packages, using the following to
recompile:

-- recompile invalid stored program objects
-- CAVEAT: does not take package dependencies
-- into account!
DECLARE
CURSOR invalid_prog_obj IS
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';
rec invalid_prog_obj%ROWTYPE;
status NUMERIC;
BEGIN
DBMS_OUTPUT.enable;
OPEN invalid_prog_obj;
LOOP -- recompile each stored program object
FETCH invalid_prog_obj INTO rec;
EXIT WHEN invalid_prog_obj%NOTFOUND;
DBMS_OUTPUT.put('Recompile ' || rec.object_type ||
' ' || rec.object_name);
DBMS_DDL.alter_compile(rec.object_type, NULL, rec.object_name);
DBMS_OUTPUT.put_line(' SUCCESSFUL'); -- recompile succeeded
END LOOP; -- invalid program objects
CLOSE invalid_prog_obj;
EXCEPTION
WHEN OTHERS THEN
BEGIN
status := SQLCODE;
DBMS_OUTPUT.put_line(' FAILED with ' || SQLERRM(status));
IF (invalid_prog_obj%ISOPEN) THEN
CLOSE invalid_prog_obj;
END IF;

As a rule I don't do PL/SQL, so I don't know why I am getting

SQL> @recompile
25 /
BEGIN
*
ERROR at line 19:
ORA-06550: line 19, column 2:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the
following:
pragma when

Can someone assist me ?

Thanks

GJC

The fifty dwarves were reduced to eight,
before anyone suspected hungry.

______________________________________________________
Gary Colbran

System/Database Administrator
Telkom SA
55 Oak Avenue
Centurian
South Africa
Ph: 012-680 1315.
Ph: 082-786 6592.
[Email Address Removed] <mailto:[Email Address Removed]

**************************Disclaimer**********************************
**

Information contained in this E-MAIL being proprietary to Telkom SA
and is
'privileged' and 'confidential' and intended for use only by the
individual
or entity to which it is addressed. You are notified that any use,
copying
or dissemination of the information contained in the E-MAIL in any
manner
whatsoever is strictly prohibited.

**********************************************************************
*****

Oracle LazyDBA home page