Previous | Table of Contents | Next

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.

Sharing Data with DBMS_PIPE

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.sql—Producer 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.sql—Consumer 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

Previous | Table of Contents | Next