|
“Those who dream by
day are cognizant of many things which escape those who dream
only by night.” Edgar Allan Poe (1809 - 1849), "Eleonora" |
Read
first then play the video:
PLS016(VIDEO)-Managing
Objects Dependency
Managing Objects Dependency
Hands-On introduction
In this Hands-On, you will
view some of the Oracle directory views and experience the Oracle
object dependencies (timestamp).
Go to “MS-DOS.”
Login to “sqlplus” as
"iself/schooling.”
Use the "column"
command to change the output format length of the Object name to 30
characters.
SQL> COL object_name
FORMAT a30
Object dependency and use
of USER_OBJECTS table
An object may be created
based on the existence of another object or objects. The purity of the
created object depends on the status of the other objects that have
already been created. If any of those objects changed or deleted, the
new object can not perform its task completely. Therefore, Oracle will
change its status to an INVALID mode.
Query all the object names,
their type and status from user_objects where their object names start
with ‘D-E-P-T.’
SQL> SELECT object_name,
object_type, status
FROM user_objects
WHERE object_name LIKE ‘DEPT%’;
Describe the dept table.
SQL> DESC dept
Then change the “l-o-c”
column size from 13 to 14 characters. Notice that when you change a
column attribute in a table, its modify date will be recorded. It is
called a timestamp. Now any objects that were using this table are
going to have an invalid status. Since the timestamp shows a date that
is after creation of those objects.
SQL> ALTER TABLE dept
MODIFY (loc VARCHAR2(14));
Describe the dept table
again.
SQL> DESC dept
Notice that the location
column size was changed.
Query all the object names,
their type and status from user_objects where their object names start
with ‘D-E-P-T.’
SQL> SELECT object_name,
object_type, status
FROM user_objects
WHERE object_name LIKE ‘DEPT%’;
Notice that the dept_name
function became invalid. The reason was that the dept_name function
was dependent on the dept table.
Compile a function
You compile the function to
change its modification timestamp to current one.
Compile the dept_name
function.
SQL> ALTER FUNCATION
dept_name COMPILE;
Then query the object
names, their type and status from user_objects where their object
names start with ‘D-E-P-T.’
SQL> SELECT object_name,
object_type, status
FROM user_objects
WHERE object_name LIKE ‘DEPT%’;
Now, both are valid.
That was an example of
object dependencies.
|
“Complaining is
good for you as long as you're not complaining to the person
you're complaining about.” Lynn Johnston (1947) |
Questions:
Q: What is an object
dependency in the Oracle database?
Q: What is a timestamp?
Q: How do you query all the
objects that was create by you (your schema)?
Q: How do you change a
datatype of a column in a table?
Q: How do you compile a
PL/SQL function?
|