the proper code :
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
--- no begin needed
status := SQLCODE;
DBMS_OUTPUT.put_line(' FAILED with ' || SQLERRM(status));
IF (invalid_prog_obj%ISOPEN) THEN
CLOSE invalid_prog_obj;
END IF;
end; -- u should end the block as u have began it by bigin command

Deepa Kale -DBA
Jyoti Structures - Nasik(IT)



"Gary Colbran
(GJ)" To: "LazyDBA.com Discussion" <[Email Address Removed]
<[Email Address Removed] cc:
co.za> Subject: Recompiling packages.

01/30/2004 01:14
PM






===============================================================================

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
status := SQLCODE;
DBMS_OUTPUT.put_line(' FAILED with ' || SQLERRM(status));
IF (invalid_prog_obj%ISOPEN) THEN
CLOSE invalid_prog_obj;
END IF;
end;
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] 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.

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


--------
Get today's cartoon: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] using this list you agree to these terms:
http://www.lazydba.com/legal.html






Oracle LazyDBA home page