How can I tell if an Oracle Package is natively compiled?

Question

I have some packages installed in Oracle, and I would like to see if they are natively compiled. Is there a way to check this in the data dictionary?

Answer 1

You can check if a package has been natively compiled using the dictionary views:

Oracle 9i:

USER_STORED_SETTINGS, DBA_STORED_SETTINGS and ALL_STORED_SETTINGS.

For example:

SELECT param_value 
  FROM user_stored_settings 
 WHERE param_name = 'PLSQL_COMPILER_FLAGS'
   AND object_name = 'MY_PACKAGE';

The PARAM_VALUE column has a value of NATIVE for procedures that are compiled for native execution, and INTERPRETED otherwise.

Oracle 10g and 11g:

USER_PLSQL_OBJECT_SETTINGS, DBA_PLSQL_OBJECT_SETTINGS and ALL_PLSQL_OBJECT_SETTINGS see the PLSQL_CODE_TYPE column.

See: http://www.dba-oracle.com/t_compiled_pl_sql.htm and http://www.pastusiak.info/oracle/native_compilation for more information.

Hope it helps...