One of our rats, Priscilla, had a terrible problem. As best she could determine, the little green light that would come on telling her it was time to go eat was not quite in sync with all the other rat’s lights. For this reason, she would continually be late out of her cage when lunch time came around. To make matters worse, she never got to finish eating her cheese before the little green light would come back on, signaling to her that it was time to get back into her cage and work again before the large mean-o-putty-tat would fly out of his cage and try to eat her.
You see, the door to her cage was activated by a client application that would check Priscilla’s computer system time and at 12:00 noon would flash a green light and open the door. What Priscilla didn’t know was that the official lunch clock for the rats and putty-tat was maintained within another computer system, and because Simon the system admin never installed any time syncing software on Priscilla’s system, her system was always 15 minutes behind.
Well, since last week when Simon got a bit too greedy around lunch time and putty-tat finally got to feed, Priscilla realized her problem of synchronization wasn’t going to get solved any time soon. Priscilla decided to fix the problem herself. She would somehow have to change the application that controlled her green light so that it would obtain the system time from the main computer instead of using her own internal system clock.
In a desperate attempt, Priscilla first tried to just select the sysdate by using a database link:
SELECT to_char(sysdate,'MMDDYY:HH24:MI:SS') FROM dual@LUNCH;
All this did was once again give Priscilla the sysdate from her internal system.
After experimenting for a little while, she realized that in order to get the proper date and time to the application that flashed the green light and opened her cage door, it would have to issue a connect to the LUNCH database. This could only be accomplished through a remote procedure call that would return the date and time. Fortunately, the code was in PL/SQL and Priscilla was an expert at PL/SQL. Priscilla started coding so she could get to lunch sooner.
The first procedure to write was the remote procedure that would reside in the LUNCH database:
REMOTE PROCEDURE: GET_DATE CREATE PROCEDURE GET_DATE (SYSTEM_DATE OUT DATE) IS SQL_CMD INTEGER; IGNORE INTEGER; W_DATE DATE; BEGIN SQL_CMD := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(SQL_CMD,'SELECT SYSDATE FROM DUAL',DBMS_SQL.V7); DBMS_SQL.DEFINE_COLUMN(SQL_CMD, 1, W_DATE); IGNORE := DBMS_SQL.EXECUTE(SQL_CMD); IF DBMS_SQL.FETCH_ROWS(SQL_CMD) > 0 THEN DBMS_SQL.COLUMN_VALUE(SQL_CMD, 1, W_DATE); END IF; DBMS_SQL.CLOSE_CURSOR(SQL_CMD); SYSTEM_DATE := W_DATE; EXCEPTION WHEN OTHERS THEN SYSTEM_DATE := NULL; END GET_SYSDATE; /
This was an easy procedure for Priscilla to write. Nothing she hadn’t done many times before. Even though Priscilla is a wise rat and keeps her database upgraded, she knew that there are earlier versions of Oracle around and one of those other rats just might need her code, so she decided to be “version friendly” and compatible for earlier versions of PL*SQL.
The next step was to alter the client application. There was a procedure that pulled the system date and time called CALL_GET_DATE. After a few attempts this is what Priscilla came up with for her local procedure. There’s not a great deal different here except she changed the simple SELECT sysdate from dual’ with a dynamic procedure build which uses a database link.
LOCAL PROCEDURE: CALL_GET_DATE CREATE PROCEDURE CALL_GET_DATE IS IGNORE INTEGER; SQL_CMD INTEGER; SYSTEM_DATE DATE; W_DATE DATE; BEGIN SQL_CMD := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(SQL_CMD,'DECLARE BEGIN GET_DATE@LUNCH (:W_DATE); END;',DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(SQL_CMD, ':W_DATE', W_DATE); IGNORE := DBMS_SQL.EXECUTE(SQL_CMD); DBMS_SQL.VARIABLE_VALUE(SQL_CMD, ':W_DATE', W_DATE); DBMS_SQL.CLOSE_CURSOR(SQL_CMD); SYSTEM_DATE := W_DATE; EXCEPTION WHEN OTHERS THEN SYSTEM_DATE := NULL; END GET_SYSDATE_CALL; /
As you can see Pricilla was very rushed to get this done, and as a result, she hard coded the dblink name. The twelve o’clock hour was coming up soon, and she didn’t want to be late for anymore lunches. When she gets back from lunch with a full belly of cheese and crumbs, she intends to pass this procedure the database link name and also add some error routines.
You can use this method for a variety of things. I personally have used it to read and write O/S system files and evaluate the results within a database. You may ask, why not just get on the other system for this? I personally like being able to store the information I gather into a table for a full analysis later. Also, it would be very hard to relate database I/O statistics from V$FILESTAT against the monitoring gathered by the operating system through iostat or sar. There has to be a common ground with the power of database analytics behind it. Also, as in the case with Priscilla, you may not have full access to the system in question and the only way to get to the data is through a bit of indirection.