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 14

“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:

   PLS-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?