iSelfSchooling - How to use DBMS

iSelfSchooling.com Since 1999 References | Search more | Oracle Syntax | Free Online Oracle Training

Home .Services Login Start Learning Certification . .Share your BELIEF(s)...

. Online Accounting     .Copyright & User Agreement   |

    .Vision      .Biography     .Acknowledgement
.Contact Us      .Comments/Suggestions       Email2aFriend    |

Examples of how to use DBMS_DDL package...

	More Resources by Google:

By: John Kazerooni

Examples of how to use DBMS_DDL package:
        DBMS_OUTPUT

SQL> SET SERVEROUTPUT ON SIZE 400000

Create a table.
SQL> CREATE TABLE test01_4ddl_pkg
  1   (col1 NUMBER)
  2  /

Create a procedure.
SQL> CREATE OR REPLACE PROCEDURE test02_4ddl_pkg
  1  AS
  2  BEGIN
  3    /* We created this procedure to show 
  4    how you can compile a procedure by
  5     using the DBMS_DDL package.          */
  6   NULL;
  7  END;
  8  /

Change the object_name column size.
SQL> COL object_name FORMAT a20

Query the last analyzed time from USER_TABLES for the created table.
SQL> SELECT

  1   TO_CHAR (LAST_ANALYZED,'mm-dd-yy hh24:mi:ss') last_analyzed_time
  2    FROM USER_TABLES 
  3    WHERE TABLE_NAME = 'TEST01_4DDL_PKG'
  4  /

Query the last ddl time from the USER_OBJECTS table for the

created procedure.
SQL> SELECT object_name,

  1   to_char(last_ddl_time,'mm-dd-yy hh24:mi:ss') ddl_time
  2    FROM user_objects
  3    WHERE object_name = 'TEST02_4DDL_PKG'
  4  /


Use the DBMS_DDL.ANALYZE_OBJECT procedure to analyze the

created table.
SQL> BEGIN
  1    DBMS_DDL.ANALYZE_OBJECT

  2     ('TABLE','ISELF','TEST01_4DDL_PKG','COMPUTE');
  3  END;
  4  /
SQL>

Query the last analyzed time from USER_TABLES for the created table.
Check the difference from pervious query.
SQL> SELECT

     TO_CHAR (LAST_ANALYZED,'mm-dd-yy hh24:mi:ss') last_analyzed_time
  1    FROM USER_TABLES 
  2    WHERE TABLE_NAME = 'TEST01_4DDL_PKG'
  3  /

Use the DBMS_DDL.ALTER_COMPILE procedure to compile the

created procedure.
SQL> BEGIN
  1    DBMS_DDL.ALTER_COMPILE

       ('PROCEDURE','ISELF','TEST02_4DDL_PKG');
  2  END;
  3  /

Query the last ddl time from the USER_OBJECTS table for the

created procedure.
Check the difference from pervious query.
SQL> SELECT object_name,

     to_char(last_ddl_time,'mm-dd-yy hh24:mi:ss') ddl_time
  1    FROM user_objects
  2    WHERE object_name = 'TEST02_4DDL_PKG'
  3  /

Good Luck!