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

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 17

“The happiness of a man in this life does not consist in the absence but in the mastery of his passions.” Alfred Lord Tennyson (1809 - 1892)

 

Using the DBMS_DDL package

 

Introduction:

In the PL/SQL block, you are not able to perform any DDL statement. In order to use the DDL statements such as CREATE TABLE, DROP TABLE, and ALTER TABLE, you should use the DBMS_DDL package.

 

This package was provided by Oracle to enable an Oracle developer to perform its DDL statement. Now, check the following hands-on, how you can ANALYZE or COMPILE an Oracle object.

 

Set serverout on with a large buffer size.

SQL> SET SERVEROUTPUT ON SIZE 400000

 

Create a table.

SQL> CREATE TABLE test01_4ddl_pkg

                (col1 NUMBER)

/

 

Create a PL/SQL procedure

Create a procedure. This procedure does nothing..

SQL>

 

CREATE OR REPLACE PROCEDURE test02_4ddl_pkg

AS

 

BEGIN

 

    /* We created this procedure to show

    how you can compile a procedure by

    using the DBMS_DDL package. */

 

    NULL;

 

END;

/

 

Change the object_name column size.

SQL> COL object_name FORMAT a20

 

USER_TABLES table

Query the last analyzed time from USER_TABLES for the created table.

SQL> SELECT

            TO_CHAR (LAST_ANALYZED,'mm-dd-yy hh24:mi:ss') last_analyzed_time

            FROM USER_TABLES

            WHERE TABLE_NAME = 'TEST01_4DDL_PKG'

/

The output shows you a date and time of the last time you perform the ANALYZE statement.

 

USER_OBJECTS table

Query the last ddl time from the USER_OBJECTS table for the created procedure.

SQL> SELECT object_name,

            to_char(last_ddl_time,'mm-dd-yy hh24:mi:ss') ddl_time

            FROM user_objects

            WHERE object_name = 'TEST02_4DDL_PKG'

/

The output shows that the last time a DDL statement such as ALTER TABLE, etc was performed on the object.

 

DBMS_DDL.ANALYZE_OBJECT procedure

Use the DBMS_DDL.ANALYZE_OBJECT procedure to analyze the created table.

SQL>

BEGIN

    DBMS_DDL.ANALYZE_OBJECT

        ('TABLE','ISELF','TEST01_4DDL_PKG','COMPUTE');

END;

/

 

Query analyzed time

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

            FROM USER_TABLES

            WHERE TABLE_NAME = 'TEST01_4DDL_PKG'

/

 

DBMS_DDL.ALTER_COMPILE procedure

Use the DBMS_DDL.ALTER_COMPILE procedure to compile the

created procedure.

SQL>

BEGIN

    DBMS_DDL.ALTER_COMPILE

        ('PROCEDURE','ISELF','TEST02_4DDL_PKG');

END;

/

 

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

            FROM user_objects

            WHERE object_name = 'TEST02_4DDL_PKG'

/

 

“Education is what survives when what has been learned has been forgotten.” B. F. Skinner (1904 - 1990), New Scientist, May 21, 1964

 

Questions:

Q: How do you increase the size of SERVEROUTPUT buffer?

Q: Can you perform a DDL statement in the PL/SQL block?

Q: How can you compile an object in a PL/SQL block?

Q: What does the DBMS_DDL package?

Q: What does the ANALZE_OBJECT procedure in the DBMS_DDL package and how can you verify that the object was ANALYZED?

Q: What does the ALTER_COMPILE procedure in the DBMS_DDL package and how can you verify that the object was compiled?