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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: