Archive for the ‘Procedures’ Category

Custom Script to Identify Invalid Packages

Identifying Invalid Objects

SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Manual Approach

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

DBMS_DDL

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’,’MY_SCHEMA’,’MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PROCEDURE’,’MY_SCHEMA’,’MY_PROCEDURE’);

EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);

EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

DBMS_UTILITY

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.

 

  • Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
  • The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
  • The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and  DBMS_RANDOM to be present and valid.
  • — Schema level.

    EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);

    EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

    — Database level.

    EXEC UTL_RECOMP.recomp_parallel(4);

    — Using job_queue_processes value.

    EXEC UTL_RECOMP.recomp_parallel();

    EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

     

    Custom Script for Compiling Invalid Packages in File

    CREATE OR REPLACE PROCEDURE COMPILE_PACKAGES

    IS

    outfile   UTL_FILE.file_type;

    stmt      VARCHAR2 (150);

    BEGIN

      outfile := UTL_FILE.fopen (‘PLSQL_FILE’, ‘INVALID_OBJECTS.txt’, ‘w’);

      FOR cur_rec IN (SELECT owner,

                             object_name,

                             object_type,

                             DECODE(object_type, ‘PACKAGE’, 1,

                                                 ‘PACKAGE BODY’, 2, 2) AS                    recompile_order

                      FROM   dba_objects

                      WHERE  object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)

                      AND    status != ‘VALID’

                      ORDER BY 4)

      LOOP

        BEGIN

          IF cur_rec.object_type = ‘PACKAGE’ THEN

            stmt := ‘ALTER ‘ || cur_rec.object_type || ‘ "’ || cur_rec.owner || ‘"."’ || cur_rec.object_name || ‘" COMPILE’;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

            –EXECUTE IMMEDIATE stmt;

          ElSE

            stmt:= ‘ALTER PACKAGE "’ || cur_rec.owner ||

                ‘"."’ || cur_rec.object_name || ‘" COMPILE BODY’;

            –EXECUTE IMMEDIATE stmt;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

          END IF;

          UTL_FILE.PUT_LINE(outfile, stmt);

        EXCEPTION

          WHEN OTHERS THEN

            DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||

                                 ‘ : ‘ || cur_rec.object_name);

        END;

      END LOOP;

      UTL_FILE.FCLOSE(outfile);

    END COMPILE_PACKAGES;

    Main Source of this article: Oracle Base link on Recompiling Objects

    Advertisements

    Oracle Supplied Packages

    DBMS_SQL

    Using DBMS_SQL, one can write stored procedures that use dynamic SQL. It can issue data definition language statements in PLSQL

     

    CREATE OR REPLACE PROCEDURE delete_all_rows
      (tab_name IN VARCHAR2, rows_del OUT NUMBER)
      IS
      cursor_name : = DBMS_SQL.OPEN_CURSOR;

      DBMS_SQL.PARSE ( cursor_name, ‘DELETE FROM ‘ ||tab_name,    DBMS_SQL.NATIVE);
      rows_del := DBMS_SQL.EXECUTE(cursor_name);
      DBMS_SQL.CLOSE_CURSOR(cursor_name);
    END;

     

    DBMS_DDL

    DBMS_DDL provides access to some SQL DDL statements from stored procedures. It includes ALTER_COMPILE and ANALYZE_OBJECT procedures. 

    DBMS_DDL.ALERT_COMPILE  (‘PROCEDURE’ ,’USER’, ‘OBJECT_NAME’ );   #DDL

    DBMS_DDL.ANALYZE_OBJECT(‘ OBJECT_TYPE’, ‘USER’, ‘NAME’ , ‘COMPUTE’);    

    DBMS_JOBS and DBMS_SCHEDULER

    It enables scheduling and execution of PL/SQL programs:

    Submitting jobs
    Executing jobs
    Removing and Suspending jobs

    begin
    --job:=101;   --job created to execute for every minute.
    sys.dbms_job.submit(job => job,
    what => 'del_test_info_p;',
    next_date => TRUNC(SYSDATE, 'HH')+ 1/24,
    interval => 'TRUNC(SYSDATE,''MI'')+1/(24/60));
    
    commit;
    end;

    BEGIN

      DBMS_SCHEDULER.create_job (

        job_name        => ‘dummy_job’,

        job_type        => ‘PLSQL_BLOCK’,

        job_action      => ‘BEGIN NULL; /* Do Nothing */ END;’,

        start_date      => SYSTIMESTAMP,

        repeat_interval => ‘SYSTIMESTAMP + 1 /* 1 Day */’);

    END;

    DBMS_OUTPUTLINE

    DBMS_OUTPUT.PUT_LINE(‘I got here:’||:new.col||’ is the new value’);

    UTL_FILE

    The UTL_FILE package provides text file I/O  from within PL/SQL. In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIRCREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

        

    declare      
           outfile         UTL_FILE.file_type;

          locdataline   VARCHAR2 (150);

    BEGIN

         outfile := UTL_FILE.fopen (‘/u99/sas_data’, ‘VehData.dat’, ‘w’);

          FOR v1 IN veh_data

          LOOP

             BEGIN

                locdataline :=    v1.a  || ‘|’   || v1.b || ‘|’   || v1.c || ‘|’   || v1.d   || ‘|’;

                UTL_FILE.put_line (outfile, locdataline);

             END;

          END LOOP;

          UTL_FILE.fclose (outfile);

    END

    UTL_HTTP

    The UTL_HTTP package makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. You can use it to access data on the Internet over HTTP.

    UTL_TCP

    With the UTL_TCP package and its procedures and functions, PL/SQL applications can communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols and e-mail.

    DBMS_STATS

    Below is a sample execution of dbms_stats with the options clause.

    exec dbms_stats.gather_schema_stats( –

    ownname          => ‘SCOTT’, –

    options          => ‘GATHER AUTO’, –

    estimate_percent => dbms_stats.auto_sample_size, –

    method_opt       => ‘for all columns size repeat’, –

    cascade          => true, –

    degree           => 15 –

    )

    Here is a representative example of invoking dbms_stats in 10g:

    DBMS_STATS.gather_schema_stats(

    ownname=>’<schema>’,

    estimate_percent=>dbms_stats.auto_sample_size

    cascade=>TRUE,

    method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

     

    OTHER USEFUL PACKAGES

    DBMS_ALERT

    DBMS_DESCRIBE

    DBMS_LOCK

    DBMS_SESSION

    DBMS_APPLICATION_INFO

    DBMS_TRANSACTION

    DBMS_SHARED_POOL

    DBMS_UTILITY

    DBMS_LOGMNR

    DBMS_PROFILER

    DBMS_REPAIR

    DBMS_SPACE

    DBMS_STATS