Page 284
What's the buffer size for? After all, a PL/SQL table can have over four billion elements! Imagine you're writing a C program to receive buffered strings via get_line. It would really be a waste (and not a good idea) to allocate a chunk of memory 4.29 billion ¥ 255 bytes. You would need a terabyte of memory! Memory's cheap nowadays, but not that cheap. Instead, you ask the user to specify the buffer size, between, say 2KB and 1MB, and that's what you allocate. The package tracks the buffer space usage, since your C program expects no more data than what the user specified. Then, you pack the lines returned into this memory area, wasting little or no space. Finally, all the lines are now available to display to the user. This may not seem like a particularly sophisticated scheme, but it's simple and it works.
If you exceed the buffer space size you specified, you'll get the exception:
ORA-20000: ORU-10027: buffer overflow, limit of <buf_limit> bytes.
This package enables you to transmit data to another Oracle session via a "pipe," a shared memory object stored in the SGA that acts like a FIFO queue. The pipe defaults to 8KB in size, but can be explicitly created up to any size that can reasonably fit in the SGA. The format of the message is determined by the sender. There is a small overhead cost associated with each additional datum. The message is packed into an internal buffer according to a "transport" layer protocol (and I use that loosely), as follows:
There are a few interesting quirks to the behavior of a pipe:
Page 285
I recommend establishing a standard protocol that all programs must follow. Such a protocol might have:
The message signature is needed so that the reader process can assure itself it has a proper message to unpack, so it can unpack the proper data types in the proper order. The user ID and application name are for tracking purposes. The sequence number can help uncover sending problems, like messages being skipped (such as when the pipe is full and the next message fails to send). The date stamp also helps in troubleshooting, or it may be simply desirous for the receiving process to know when the message was sent. The return pipe name is used in those cases where data is expected to be returned. This is especially useful in external applications, such as C programs, that receive a command and return a response. The variable data is specific to each application.
My TRACE package can be used for sending and receiving trace messages, such as for debugging. The sending clients can specify their own separate pipes to write trace data to, or use a common pipe if volume is anticipated to be low. The receiving server program could be an external program written in Delphi, for example, that might display each trace in separate windows. The receiving session does have to know the pipe on which to listen.
Page 286
Open up two SQL*Plus sessions and connect as the same user you compiled the TRACE package under. In one session, run the following Producer test program (see Listing 12.4):
Listing 12.4 traceprd.sqlProducer Script to Test the Trace Package
-- PRODUCER SCRIPT: put_trace user DECLARE -- sending process status NUMERIC; li VARCHAR2(4); BEGIN DBMS_OUTPUT.enable; TRACE.trace_rec.name := `TEST'; -- pipe name TRACE.trace_rec.mod := `PIPE_TEST'; -- module name TRACE.trace_on; -- enable tracing FOR i in 1..5 LOOP li := TO_CHAR(i); TRACE.trace_rec.var := `name' || li; -- name of variable TRACE.trace_rec.val := `val' || li; -- value of variable TRACE.trace_rec.com := `this is a test'; -- comment TRACE.put_trace(TRACE.trace_rec, status); DBMS_OUTPUT.put_line(`result of put_trace: ` || SQLERRM(status)); END LOOP; EXCEPTION WHEN OTHERS THEN status := SQLCODE; DBMS_OUTPUT.put_line(`producer: ` || SQLERRM(status)); END; /
You should get the following response:
result of put_trace: ORA-0000: normal, successful completion result of put_trace: ORA-0000: normal, successful completion result of put_trace: ORA-0000: normal, successful completion result of put_trace: ORA-0000: normal, successful completion result of put_trace: ORA-0000: normal, successful completion PL/SQL procedure successfully completed.
In the other session, run the following Consumer script (see Listing 12.5):
Listing 12.5 tracecns.sqlConsumer Script to Test the Trace Package
-- CONSUMER SCRIPT: get_trace user DECLARE -- receiving process status NUMERIC := 0; BEGIN DBMS_OUTPUT.enable; TRACE.trace_rec.name := `TEST'; -- pipe name TRACE.trace_rec.sleep := 1; -- 1 sec between reads WHILE (status = 0) LOOP -- will exit when no more messages found