Recompile invalid packages

Hi,

Do you know how can i recompile invalid package on SQL+ ?

So, i know to do this on Toad but i need a command for SQL+

Thanks,
Julien.

rem change the spool path to whatever you want


spool /opt/oracle/admin/adhoc/runthem.sql
SELECT 'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
/

spool off;

@/opt/oracle/admin/adhoc/runthem.sql



P.S. Why am I unemployed ?



"Julien" wrote in message
news:bob61u$2cta$1-at-news6.isdnet.net...
> Hi,
>
> Do you know how can i recompile invalid package on SQL+ ?
>
> So, i know to do this on Toad but i need a command for SQL+
>
> Thanks,
> Julien.
>
>

rem change the spool path to whatever you want


spool /opt/oracle/admin/adhoc/runthem.sql
SELECT 'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
/

spool off;

@/opt/oracle/admin/adhoc/runthem.sql



P.S. Why am I unemployed ?



"Julien" wrote in message
news:bob61u$2cta$1-at-news6.isdnet.net...
> Hi,
>
> Do you know how can i recompile invalid package on SQL+ ?
>
> So, i know to do this on Toad but i need a command for SQL+
>
> Thanks,
> Julien.
>
>

check out the sql reference at tahiti.oracle.com - especially alter package.

--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Julien" wrote in message
news:bob61u$2cta$1-at-news6.isdnet.net...
> Hi,
>
> Do you know how can i recompile invalid package on SQL+ ?
>
> So, i know to do this on Toad but i need a command for SQL+
>
> Thanks,
> Julien.
>
>

check out the sql reference at tahiti.oracle.com - especially alter package.

--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Julien" wrote in message
news:bob61u$2cta$1-at-news6.isdnet.net...
> Hi,
>
> Do you know how can i recompile invalid package on SQL+ ?
>
> So, i know to do this on Toad but i need a command for SQL+
>
> Thanks,
> Julien.
>
>

In addition to the other fine answers, also look at
ORACLE_HOME/rdbms/admin/utlirp.sql to recompile all invalid objects.

HTH,
Brian

Julien wrote:
>
> Hi,
>
> Do you know how can i recompile invalid package on SQL+ ?
>
> So, i know to do this on Toad but i need a command for SQL+
>
> Thanks,
> Julien.

--
================================================== =================

Brian Peasland
dba-at-remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

In addition to the other fine answers, also look at
ORACLE_HOME/rdbms/admin/utlirp.sql to recompile all invalid objects.

HTH,
Brian

Julien wrote:
>
> Hi,
>
> Do you know how can i recompile invalid package on SQL+ ?
>
> So, i know to do this on Toad but i need a command for SQL+
>
> Thanks,
> Julien.

--
================================================== =================

Brian Peasland
dba-at-remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

nobody wrote:
> rem change the spool path to whatever you want
>
>
> spool /opt/oracle/admin/adhoc/runthem.sql
> SELECT 'ALTER ' || OBJECT_TYPE || ' ' ||
> OWNER || '.' || OBJECT_NAME || ' COMPILE;'
> FROM DBA_OBJECTS
> WHERE STATUS = 'INVALID'
> /
>
> spool off;
>
> @/opt/oracle/admin/adhoc/runthem.sql

If you have an invalid package body, this
will fail, since the correct syntax for
the recompilation of a package body is

alter package compile body;

Oops, now I gave the OP the answer instead
of correctly advising to read the sql reference
at tahiti.oracle.com.

Sorry ;-)

>
>
>
> P.S. Why am I unemployed ?
>


I can't think why

Regards

Holger

nobody wrote:
> rem change the spool path to whatever you want
>
>
> spool /opt/oracle/admin/adhoc/runthem.sql
> SELECT 'ALTER ' || OBJECT_TYPE || ' ' ||
> OWNER || '.' || OBJECT_NAME || ' COMPILE;'
> FROM DBA_OBJECTS
> WHERE STATUS = 'INVALID'
> /
>
> spool off;
>
> @/opt/oracle/admin/adhoc/runthem.sql

If you have an invalid package body, this
will fail, since the correct syntax for
the recompilation of a package body is

alter package compile body;

Oops, now I gave the OP the answer instead
of correctly advising to read the sql reference
at tahiti.oracle.com.

Sorry ;-)

>
>
>
> P.S. Why am I unemployed ?
>


I can't think why

Regards

Holger

"nobody" wrote in message news:...
> rem change the spool path to whatever you want
>
>
> spool /opt/oracle/admin/adhoc/runthem.sql
> SELECT 'ALTER ' || OBJECT_TYPE || ' ' ||
> OWNER || '.' || OBJECT_NAME || ' COMPILE;'
> FROM DBA_OBJECTS
> WHERE STATUS = 'INVALID'
> /
>
> spool off;
>
> @/opt/oracle/admin/adhoc/runthem.sql
>
>
>
> P.S. Why am I unemployed ?

Because this doesn't cover all possible object compilations (think
about one thing referencing another...)?

Because a modern dba would use a built-in sp?

Bonus consulting job: Demonstrate situation where built-in sp doesn't
work.

Kyte-level: Demonstrate unpublished parameters to make it work.

Double-secret-probation-job: Prove Kyte wrong.

jg
--
@home.com is bogus.
http://images.amazon.com/images/P/B0...1.LZZZZZZZ.jpg