|
“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
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','ISELF','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','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 /
|
“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?
|
