Remote Procedure Calls Will Get You the Cheese


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.

Bon Appitete

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles