Amit,

Why reinvent the wheel?

You should use the Oracle supplied script for this purpose:

Sqlplus / as sysdba
> @$ORACLE_HOME/rdbms/admin/utlrp

It does packages, procedures, views, functions, the lot. A script will
need to be run several times maybe to pick up dependancies and recompile
those. The Oracle script does it in one hit.

HTH
John.



-----Original Message-----
From: Amit
[mailto:oracledba-ezmlmshield-x50703602.[Email address protected]
Sent: 28 February 2008 05:37
To: LazyDBA Discussion
Subject: Re-compile invalid objects in oracle9i


Dear All,



I have to alter a table in production database. Then associated objects

become invalid. So I want to rebuild by using below script:



spool on



Spool D:\amit\CalendarRegistration\ForRelease\SpoolText.txt



select



'ALTER ' || OBJECT_TYPE || ' ' ||



OWNER || '.' || OBJECT_NAME || ' COMPILE;'



from



all_objects



where



status = 'INVALID'



and



object_type in ('PACKAGE','FUNCTION','PROCEDURE')



;





but it is giving error like

SP2-0734: unknown command beginning "'ALTER ' |..." - rest of line

ignored.

SP2-0734: unknown command beginning "OWNER || '..." - rest of line

ignored.

SP2-0042: unknown command "from" - rest of line ignored.

SP2-0734: unknown command beginning "all_object..." - rest of line

ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0042: unknown command "where" - rest of line ignored.

SP2-0734: unknown command beginning "status = '..." - rest of line

ignored.

SP2-0042: unknown command "and" - rest of line ignored.

SP2-0734: unknown command beginning "object_typ..." - rest of line

ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.







Please help me on this issue.



Thanks,

Amit







This e-mail is confidential and may be legally privileged. If you are
not the intended recipient, please notify the sender immediately and
delete this e-mail.



????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.

Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.

SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.


Oracle LazyDBA home page