|
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
1 (col1
NUMBER)
2 /
Create a
PL/SQL procedure
Create a
procedure. This procedure does nothing..
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
USER_TABLES
table
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 /
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,
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 /
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
1
DBMS_DDL.ANALYZE_OBJECT
2 ('TABLE','ORACLE','TEST01_4DDL_PKG','COMPUTE');
3 END;
4 /
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
1 FROM
USER_TABLES
2 WHERE
TABLE_NAME = 'TEST01_4DDL_PKG'
3 /
DBMS_DDL.ALTER_COMPILE procedure
Use the
DBMS_DDL.ALTER_COMPILE procedure to compile
the
created
procedure.
SQL> BEGIN
1
DBMS_DDL.ALTER_COMPILE
('PROCEDURE','ORACLE','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 /
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? |
