Remote Procedure Calls Will Get You the Cheese

December 10, 2002

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;
/

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;
/

» See All Articles by Columnist James Koopmann