Check invalid packages

1	SELECT * FROM ALL_OBJECTS WHERE STATUS!='VALID' and owner not in ('SYS', 'PUBLIC', 'SYSTEM', 'WMSYS', 'SYSMAN', 'APEX_030200', 'VAS_P2P_DB', 'VAS_INDICATOR_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'VAS_ACK_DB', 'VAS_LOG_DB', 'VAS_TOEMAIL_DB');

Then to recompile invalid packages:

1 2 3 4 5 6

EXEC UTL_RECOMP.recomp_serial('CRM_DB'); EXEC UTL_RECOMP.recomp_serial('PROV_DB'); EXEC UTL_RECOMP.recomp_serial('RAF_CONS_DB'); EXEC UTL_RECOMP.recomp_serial('RATING_DB'); EXEC UTL_RECOMP.recomp_serial('RESOURCE_DB'); EXEC UTL_RECOMP.recomp_serial('SHARED_DB');

Or to perform the same task in parallel using the specified number of threads

1	EXEC UTL_RECOMP.recomp_parallel(4, 'PROV_DB');

or at database level:

1	EXEC UTL_RECOMP.recomp_parallel(4);

See http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php for more details

Toad: Find Backward does not work, the Direction Forward and Backward are greyed out

Toad For Oracle 11

• Description

  In Find dialog, the Direction Forward and Backward are greyed out.
  Only the Forward option is selected.
  Thus, only F3 works but not Shift+F3.
  
  

• Cause

  User Setting file may have been corrupted.

• Resolution

  As a test to localize and confirm the issue, doing the following will give you a clean User Files.
  
  Here are steps to get a clean User Files folder…
  1) Select Utilities | Copy User Settings.
  2) Select “Create a clean set of User Files from the base installation” and click OK.
  3) Toad will automatically restart. Once restarted, test issue.
  You will temporarily lose your connections settings.
  (If error no longer persist, with Toad for Oracle closed: The user can copy their connections.ini, connectionpwds.ini and savedsql.dat files from the 11.5_bak folder into their newly created User Files Folder.)
  Here are steps to get old settings back…
  1) Close Toad.
  2) Delete the 11.5 folder in the C:\Users\<User>\AppData\Roaming\Quest Software\Toad for Oracle directory.
  3) Rename the 11.5_bak folder, in the same directory, to 11.5.
  4) Launch Toad.

Toad: Objects within Package Body or Spec are not sorted alphabetically in Schema Browser

Toad For Oracle 11

1. Double click a package in the Schema Browser
2. Once the package has been opened, right click then Desktop | Navigator
   
3. Right click in navigator and select "Sort and Group option
   
4. Come back to the Schema Browser and refresh

Check last PL/SQL package compile time

1	SELECT OWNER, OBJECT_NAME, LAST_DDL_TIME FROM ALL_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND OWNER LIKE '%_DB';

PL/SQL function: raise exceptions and fill in output variables

If we use "x_out_param OUT VARCHAR2", and an exception is raised in the function, the parameter won't be filled in.
The trick consists in using the NOCOPY keyword to pass variables by reference and not by copy.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

PROCEDURE PROC(x_out_param OUT NOCOPY VARCHAR2) IS BEGIN     ...     x_out_param := 'some value';     RAISE MY_ERROR_EXCEPTION; END PROC;   PROCEDURE MAIN(x_out_param OUT VARCHAR2) IS BEGIN     ...     PROC(x_out_param); EXCEPTION     WHEN MY_ERROR_EXCEPTION THEN RETURN 1;     WHEN OTHERS THEN RETURN 2; END MAIN;