|
"So
many gods, so many creeds; So many paths that wind and wind,
while just the art of being kind is all the sad world
needs."
-Ella
Wheeler Wilcox (1850-1919)
|
Examples
of how to use DBMS_DDL package...
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
/
|