| Oracle7 Server Application Developer's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Application Developer's Guide | Library |
Product |
Contents |
Index |
The packages are
Depending upon your security requirements, you may choose to use either a public pipe or a private pipe.
Attention: Pipes are independent of transactions. Be careful using pipes when transaction control can be affected.
| Function/Procedure | Description | Refer to Page |
| CREATE_PIPE | Explicitly create a pipe (necessary for private pipes). | 8 - 4 |
| PACK_MESSAGE | Build message in local buffer. | 8 - 6 |
| SEND_MESSAGE | Send message on named pipe. Implicitly create a public pipe if named pipe does not exist. | 8 - 6 |
| RECEIVE_MESSAGE | Copy message from named pipe into local buffer. | 8 - 8 |
| NEXT_ITEM_TYPE | Return datatype of next item in buffer. | 8 - 9 |
| UNPACK_MESSAGE | Access next item in buffer. | 8 - 10 |
| REMOVE_PIPE | Remove the named pipe. | 8 - 10 |
| PURGE | Purge contents of named pipe. | 8 - 11 |
| RESET_BUFFER | Purge contents of local buffer. | 8 - 12 |
| UNIQUE_ SESSION_NAME | Return unique session name. | 8 - 12 |
You can create an explicit public pipe by calling the CREATE_PIPE function with the PRIVATE flag set to FALSE. You must deallocate explicitly-created pipes by calling the REMOVE_PIPE function.
The domain of a public pipe is the schema in which it was created, either explicitly or implicitly.
Any schema user with the appropriate privileges and knowledge can read information from a public pipe. However, once buffered information is read by one user, it is emptied from the buffer, and is not available for other readers of the same pipe.
The sending session builds a message using one or more calls to the PACK_MESSAGE procedure. This procedure adds the message to the session's local message buffer. The information in this buffer is sent by calling the SEND_MESSAGE procedure, designating the pipe name to be used to send the message. When SEND_MESSAGE is called, all messages that have been stacked in the local buffer are sent.
A process that wants to receive a message calls the RECEIVE_MESSAGE procedure, designating the pipe name from which to receive the message. The process then calls the UNPACK_MESSAGE procedure to access each of the items in the message.
You cannot create a private pipe if an implicit pipe exists in memory and has the same name as the private pipe you are trying to create. In this case CREATE_PIPE returns an error.
Access to a private pipe is restricted to the following:
As with public pipes, you must first build your message using calls to PACK_MESSAGE before calling SEND_MESSAGE. Similarly you must call RECEIVE_MESSAGE to retrieve the message before accessing the items in the message by calling UNPACK_MESSAGE.
ORA-23321: Pipename may not be null
ORA-23322: Insufficient privilege to access pipe
ORA-23321 can be returned by CREATE_PIPE, or any subprogram that takes a pipe name as a parameter. ORA-23322 can be returned by any subprogram that references a private pipe in its parameter list.
Warning: Do not use a pipe name beginning with ORA$; these names are reserved for use by Oracle Corporation.
DBMS_PIPE.CREATE_PIPE(pipename IN VARCHAR2,
maxpipesize IN INTEGER DEFAULT 8192,
private IN BOOLEAN DEFAULT TRUE)
RETURN INTEGER;
The PACK_MESSAGE procedure is overloaded to accept items of type VARCHAR2, NUMBER, or DATE. In addition to the data bytes, each item in the buffer requires one byte to indicate its type, and two bytes to store its length. One additional byte is needed to terminate the message. If the message buffer exceeds 4096 bytes, Oracle raises exception ORA-6558.
When you call SEND_MESSAGE to send this message, you must indicate the name of the pipe on which you want to send the message. If this pipe already exists, you must have sufficient privileges to access this pipe. If the pipe does not already exist, it is created automatically.
Warning: Do not use a pipe name beginning with ORA$; these names are reserved for use by Oracle Corporation.
DBMS_PIPE.PACK_MESSAGE (item IN VARCHAR2);
DBMS_PIPE.PACK_MESSAGE (item IN NUMBER);
DBMS_PIPE.PACK_MESSAGE (item IN DATE);
DBMS_PIPE.PACK_MESSAGE_RAW (item IN RAW);
DBMS_PIPE.PACK_MESSAGE_ROWID (item IN ROWID);
DBMS_PIPE.SEND_MESSAGE(pipename IN VARCHAR2,
timeout IN INTEGER DEFAULT MAXWAIT
maxpipesize IN INTEGER DEFAULT 8192)
RETURN INTEGER;
If the pipe that you specify when you call RECEIVE_MESSAGE does not already exist, Oracle implicitly creates the pipe and then waits to receive the message. If the message does not arrive within a designated timeout interval, the call returns and the pipe is removed.
After receiving the message, you must make one or more calls to UNPACK_MESSAGE to access the individual items in the message. The UNPACK_MESSAGE procedure is overloaded to unpack items of type DATE, NUMBER, VARCHAR2, and there are two additional procedures to unpack RAW and ROWID items. If you do not know the type of data that you are attempting to unpack, you can call NEXT_ITEM_TYPE to determine the type of the next item in the buffer.
DBMS_PIPE.RECEIVE_MESSAGE(pipename IN VARCHAR2,
timeout IN INTEGER
DEFAULT maxwait)
RETURN INTEGER;
DBMS_PIPE.NEXT_ITEM_TYPE RETURN INTEGER;
| Return Value | Description |
| 0 | no more items |
| 6 | NUMBER |
| 9 | VARCHAR2 |
| 12 | DATE |
DBMS_PIPE.UNPACK_MESSAGE (item OUT VARCHAR2);
DBMS_PIPE.UNPACK_MESSAGE (item OUT NUMBER);
DBMS_PIPE.UNPACK_MESSAGE (item OUT DATE);
DBMS_PIPE.UNPACK_MESSAGE_RAW (item IN DATE);
DBMS_PIPE.UNPACK_MESSAGE_ROWID (item IN DATE);
If the message buffer contains no more items, or if the item received is not of the same type as that requested, the ORA-2000 exception is raised.
Pipes created explicitly by CREATE_PIPE are removed only by calling REMOVE_PIPE or when the instance is shut down. All unconsumed records in the pipe are removed before the pipe is deleted. This is similar to calling PURGE on an implicitly created pipe.
DBMS_PIPE.REMOVE_PIPE(pipename IN VARCHAR2) RETURN INTEGER;
Because PURGE calls RECEIVE_MESSAGE, the local buffer might be overwritten with messages as they are purged from the pipe. Also, you can receive an ORA-23322, insufficient privileges, error if you attempt to purge a pipe to which you have insufficient access rights.
DBMS_PIPE.PURGE(pipename IN VARCHAR2);
DBMS_PIPE.RESET_BUFFER;
DBMS_PIPE.UNIQUE_SESSION_NAME RETURN VARCHAR2;
CREATE OR REPLACE PROCEDURE debug (msg VARCHAR2) AS
status NUMBER;
BEGIN
dbms_pipe.pack_message(LENGTH(msg));
dbms_pipe.pack_message(msg);
status := dbms_pipe.send_message('plsql_debug');
IF status != 0 THEN
raise_application_error(-20099, 'Debug error');
END IF;
END debug;This example shows the Pro*C code that receives messages from the PLSQL_DEBUG pipe in the PL/SQL example above, and displays the messages. If the Pro*C session is run in a separate window, it can be used to display any messages that are sent to the debug procedure from a PL/SQL program executing in a separate session.
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
int status;
int msg_length;
char retval[2000];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
void sql_error();
main()
{
/* prepare username */
strcpy(username.arr, "SCOTT/TIGER");
username.len = strlen(username.arr);
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :username;
printf("connected\n");
/* start an endless loop to look for and print
messages on the pipe */
for (;;)
{
EXEC SQL EXECUTE
DECLARE
len INTEGER;
typ INTEGER;
sta INTEGER;
chr VARCHAR2(2000);
BEGIN
chr := '';
sta := dbms_pipe.receive_message('plsql_debug');
IF sta = 0 THEN
dbms_pipe.unpack_message(len);
dbms_pipe.unpack_message(chr);
END IF;
:status := sta;
:retval := chr;
IF len IS NOT NULL THEN
:msg_length := len;
ELSE
:msg_length := 2000;
END IF;
END;
END-EXEC;
if (status == 0)
printf("\n%.*s\n", msg_length, retval);
else
printf("abnormal status, value is %d\n", status);
}
}
void sql_error()
{
char msg[1024];
int rlen, len;
len = sizeof(msg);
sqlglm(msg, &len, &rlen);
printf("ORACLE ERROR\n");
printf("%.*s\n", rlen, msg);
exit(1);
}
The Pro*C program just sleeps, waiting for a message to arrive on the named pipe. When a message arrives, the C program processes it, carrying out the required action, such as executing a UNIX command through the system() call, or executing a SQL command using embedded SQL.
DAEMON.SQL is the source code for the PL/SQL package. This package contains procedures that use the DBMS_PIPE package to send and receive message to and from the Pro*C daemon. Note that full handshaking is used. The daemon will always send a message back to the package (except in the case of the 'STOP' command). This is valuable, since it allows the PL/SQL procedures to be sure that the Pro*C daemon is running.
You can call the DAEMON packaged procedures from an anonymous PL/SQL block using SQL*Plus or Server Manager. For example:
SVRMGR> variable rv number
SVRMGR> execute :rv := DAEMON.EXECUTE_SYSTEM('ls -la');would, on a UNIX system, cause the Pro*C daemon to execute the command system("ls -la").
Remember that the daemon needs to be running first. So you might want to run it in the background, or in another window beside the SQL*Plus or Server Manager session from which you call it.
The DAEMON.SQL also uses the DBMS_OUTPUT package (see page 8 - 21) to display the results. For this example to work, you must have execute privileges on this package.
CREATE OR REPLACE PACKAGE daemon AS
FUNCTION execute_sql(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER;
FUNCTION execute_system(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER;
PROCEDURE stop(timeout NUMBER DEFAULT 10);
END daemon;
/
CREATE OR REPLACE PACKAGE BODY daemon AS
FUNCTION execute_system(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER IS
status NUMBER;
result VARCHAR2(20);
command_code NUMBER;
pipe_name VARCHAR2(30);
BEGIN
pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;
DBMS_PIPE.PACK_MESSAGE('SYSTEM');
DBMS_PIPE.PACK_MESSAGE(pipe_name);
DBMS_PIPE.PACK_MESSAGE(command);
status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20010,
'Execute_system: Error while sending. Status = ' ||
status);
END IF;
status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20011,
'Execute_system: Error while receiving.
Status = ' || status);
END IF;
DBMS_PIPE.UNPACK_MESSAGE(result);
IF result <> 'done' THEN
RAISE_APPLICATION_ERROR(-20012,
'Execute_system: Done not received.');
END IF;
DBMS_PIPE.UNPACK_MESSAGE(command_code);
DBMS_OUTPUT.PUT_LINE('System command executed. result = ' ||
command_code);
RETURN command_code;
END execute_system;
FUNCTION execute_sql(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER IS
status NUMBER;
result VARCHAR2(20);
command_code NUMBER;
pipe_name VARCHAR2(30);
BEGIN
pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;
DBMS_PIPE.PACK_MESSAGE('SQL');
DBMS_PIPE.PACK_MESSAGE(pipe_name);
DBMS_PIPE.PACK_MESSAGE(command);
status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20020,
'Execute_sql: Error while sending. Status = ' || status);
END IF;
status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20021,
'execute_sql: Error while receiving.
Status = ' || status);
END IF;
DBMS_PIPE.UNPACK_MESSAGE(result);
IF result <> 'done' THEN
RAISE_APPLICATION_ERROR(-20022,
'execute_sql: done not received.');
END IF;
DBMS_PIPE.UNPACK_MESSAGE(command_code);
DBMS_OUTPUT.PUT_LINE
('SQL command executed. sqlcode = ' || command_code);
RETURN command_code;
END execute_sql;
PROCEDURE stop(timeout NUMBER DEFAULT 10) IS
status NUMBER;
BEGIN
DBMS_PIPE.PACK_MESSAGE('STOP');
status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20030,
'stop: error while sending. status = ' || status);
END IF;
END stop;
END daemon;
proc iname=daemon userid=scott/tiger sqlcheck=semantics
Then C-compile and link in the normal way.
#include <stdio.h>
#include <string.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char *uid = "scott/tiger";
int status;
VARCHAR command[20];
VARCHAR value[2000];
VARCHAR return_name[30];
EXEC SQL END DECLARE SECTION;
void
connect_error()
{
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL WHENEVER SQLERROR CONTINUE;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while connecting:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon quitting.\n");
exit(1);
}
void
sql_error()
{
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL WHENEVER SQLERROR CONTINUE;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while executing:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon continuing.\n");
}
main()
{
EXEC SQL WHENEVER SQLERROR DO connect_error();
EXEC SQL CONNECT :uid;
printf("Daemon connected.\n");
EXEC SQL WHENEVER SQLERROR DO sql_error();
printf("Daemon waiting...\n");
while (1) {
EXEC SQL EXECUTE
BEGIN
:status := DBMS_PIPE.RECEIVE_MESSAGE('daemon');
IF :status = 0 THEN
DBMS_PIPE.UNPACK_MESSAGE(:command);
END IF;
END;
END-EXEC;
if (status == 0)
{
command.arr[command.len] = '\0';
if (!strcmp((char *) command.arr, "STOP"))
{
printf("Daemon exiting.\n");
break;
}
else if (!strcmp((char *) command.arr, "SYSTEM"))
{
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.UNPACK_MESSAGE(:return_name);
DBMS_PIPE.UNPACK_MESSAGE(:value);
END;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute system command '%s'\n", value.arr);
status = system(value.arr);
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.PACK_MESSAGE('done');
DBMS_PIPE.PACK_MESSAGE(:status);
:status := DBMS_PIPE.SEND_MESSAGE(:return_name);
END;
END-EXEC;
if (status)
{
printf
("Daemon error while responding to system command.");
printf(" status: %d\n", status);
}
}
else if (!strcmp((char *) command.arr, "SQL")) {
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.UNPACK_MESSAGE(:return_name);
DBMS_PIPE.UNPACK_MESSAGE(:value);
END;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute sql command '%s'\n", value.arr);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL EXECUTE IMMEDIATE :value;
status = sqlca.sqlcode;
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.PACK_MESSAGE('done');
DBMS_PIPE.PACK_MESSAGE(:status);
:status := DBMS_PIPE.SEND_MESSAGE(:return_name);
END;
END-EXEC;
if (status)
{
printf("Daemon error while responding to sql command.");
printf(" status: %d\n", status);
}
}
else
{
printf
("Daemon error: invalid command '%s' received.\n",
command.arr);
}
}
else
{
printf("Daemon error while waiting for signal.");
printf(" status = %d\n", status);
}
}
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}Server Manager or SQL*Plus can also display messages buffered by the DBMS_OUTPUT procedures. To do this, you must issue the command SET SERVEROUTPUT ON in Server Manager or SQL*Plus.
In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure. If you do not call GET_LINE, or do not display the messages on your screen in SQL*Plus or Server Manager, the buffered messages are ignored. The DBMS_OUTPUT package is especially useful for displaying PL/SQL debugging information.
Note: Messages sent using the DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.
| Function/Procedure | Description | Refer to Page |
| ENABLE | enable message output | 8 - 22 |
| DISABLE | disable message output | 8 - 23 |
| PUT_LINE | place a line in the buffer | 8 - 23 |
| PUT | place partial line in buffer | 8 - 23 |
| NEW_LINE | terminate a line created with PUT | 8 - 23 |
| GET_LINE | retrieve one line of information from buffer | 8 - 24 |
| GET_LINES | retrieve array of lines from buffer | 8 - 24 |
ORU-10027: buffer overflow
ORU-10028: line length overflow
You must specify the amount of information, in bytes, to buffer. Items are stored in the DBMS_OUTPUT package. If the buffer size is exceeded, you receive the following error message:
ORA-20000, ORU-10027: buffer overflow, limit of <buffer_limit> bytes.
Multiple calls to ENABLE are allowed. If there are multiple calls to ENABLE, BUFFER_SIZE is the largest of the values specified. The maximum size is 1000000 and the minimum is 2000.
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 2000);
DBMS_OUTPUT.DISABLE;
All items are converted to VARCHAR2 as they are retrieved. If you pass an item of type NUMBER or DATE, when that item is retrieved, it is formatted with TO_CHAR using the default format. If you want to use a different format, you should pass in the item as VARCHAR2 and format it explicitly.
When you call PUT_LINE, the item that you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, you must add your own end-of-line marker by calling NEW_LINE. GET_LINE and GET_LINES do not return lines that have not been terminated with a newline character.
If your line exceeds the buffer limit, you receive an error message.
Attention: Output that you create using PUT or PUT_LINE is buffered in the SGA. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller. So, for example, Server Manager or SQL*Plus do not display DBMS_OUTPUT messages until the PL/SQL program completes. In this release, there is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program.
DBMS_OUTPUT.PUT (item IN NUMBER);
DBMS_OUTPUT.PUT (item IN VARCHAR2);
DBMS_OUTPUT.PUT (item IN DATE);
DBMS_OUTPUT.PUT_LINE(item IN NUMBER);
DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);
DBMS_OUTPUT.PUT_LINE(item IN DATE);
DBMS_OUTPUT.NEW_LINE;
After calling GET_LINE or GET_LINES, any lines not retrieved before the next call to PUT, PUT_LINE, or NEW_LINE are discarded to avoid confusing them with the next message.
DBMS_OUTPUT.GET_LINE(line OUT VARCHAR2,
status OUT INTEGER);
DBMS_OUTPUT.GET_LINES(lines OUT CHARARR,
numlines IN OUT INTEGER);where CHARARR is a table of VARCHAR2(255), defined as a type in the DBMS_OUTPUT package specification.
Example 1
An example of a function that queries the employee table and returns the total salary for a specified department follows. The function includes several calls to the PUT_LINE procedure:
CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
CURSOR emp_cursor IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11, 2) := 0;
counter NUMBER(10) := 1;
BEGIN
FOR emp_record IN emp_cursor LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal
+ emp_record.comm;
DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter ||
'; Wages = '|| TO_CHAR(total_wages)); /* Debug line */
counter := counter + 1; /* Increment debug counter */
END LOOP;
/* Debug line */
DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
TO_CHAR(total_wages));
RETURN total_wages;
END dept_salary;Assume the EMP table contains the following rows:
EMPNO SAL COMM DEPT ----- ------- -------- ------- 1002 1500 500 20 1203 1000 30 1289 1000 10 1347 1000 250 20
Assume you execute the following statements in the Server Manager SQL Worksheet input pane:
SET SERVEROUTPUT ON VARIABLE salary NUMBER; EXECUTE :salary := dept_salary(20);
You would then see the following information displayed in the output pane:
Loop number = 1; Wages = 2000 Loop number = 2; Wages = 3250 Total wages = 3250 PL/SQL procedure successfully executed.
Example 2
This example assumes that the user has used the EXPLAIN PLAN command to retrieve information about the execution plan for a statement and store it in PLAN_TABLE, and that the user has assigned a statement ID to this statement. The example EXPLAIN_OUT procedure retrieves the information from this table and formats the output in a nested manner that more closely depicts the order of steps undergone in processing the SQL statement.
/****************************************************************/
/* Create EXPLAIN_OUT procedure. User must pass STATEMENT_ID to */
/* to procedure, to uniquely identify statement. */
/****************************************************************/
CREATE OR REPLACE PROCEDURE explain_out
(statement_id IN VARCHAR2) AS
-- Retrieve information from PLAN_TABLE into cursor
-- EXPLAIN_ROWS.
CURSOR explain_rows IS
SELECT level, id, position, operation, options,
object_name
FROM plan_table
WHERE statement_id = explain_out.statement_id
CONNECT BY PRIOR id = parent_id
AND statement_id = explain_out.statement_id
START WITH id = 0
ORDER BY id;
BEGIN
-- Loop through information retrieved from PLAN_TABLE
FOR line IN explain_rows LOOP
-- At start of output, include heading with estimated cost.
IF line.id = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Plan for statement '
|| statement_id
|| ', estimated cost = ' || line.position);
END IF;
-- Output formatted information. LEVEL is used to
-- determine indention level.
DBMS_OUTPUT.PUT_LINE (lpad(' ',2*(line.level-1)) ||
line.operation || ' ' || line.options || ' ' ||
line.object_name);
END LOOP;
END; The file I/O capabilities are similar to those of the standard operating system stream file I/O (OPEN, GET, PUT, CLOSE), with some limitations. For example, you call the FOPEN function to return a file handle, which you then use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/O on the file, you call FCLOSE to complete any output and to free any resources associated with the file.
| Function/Procedure | Description | Refer to Page |
| FOPEN | Open a file for input or ouput. Create an output file if it does not exist. | 8 - 32 |
| IS_OPEN | Determine if a file handle refers to an open file. | 8 - 33 |
| FCLOSE | Close a file. | 8 - 34 |
| FCLOSE_ALL | Close all open file handles. | 8 - 34 |
| GET_LINE | Read a line of text from an open file. | 8 - 35 |
| PUT | Write a line to a file. Do not append a line terminator. | 8 - 36 |
| PUT_LINE | Write a line to a file. Append an OS-specific line terminator. | 8 - 38 |
| PUTF | A PUT procedure with formatting. | 8 - 38 |
| NEW_LINE | Write one or more OS-specific line terminators to a file. | 8 - 37 |
| FFLUSH | Physically write all pending output to a file. | 8 - 40 |
Note: The UTL_FILE package is similar to the client-side TEXT_IO package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned to the caller using PL/SQL exceptions.
You specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter, as follows:
UTL_FILE_DIR = <directory name>
For example, if the initialization file for the instance contains the line
UTL_FILE_DIR = /usr/jsmith/my_app
then the directory /usr/jsmith/my_app is accessible to the FOPEN function. Note that a directory named /usr/jsmith/My_App would not be accessible on case-sensitive operating systems.
The parameter specification
UTL_FILE_DIR = *
has a special meaning. This entry in effect turns off directory access checking, and makes any directory accessible to the UTL_FILE functions.
Warning: The '*' option should be used with great caution. For obvious security reasons, Oracle does not recommend that you use this option in production systems. Also, do not include '.' (the current directory for UNIX) in the accessible directories list.
Warning: To ensure security on file systems that allow symbolic links, users must not be allowed WRITE permission to directories accessible by PL/SQL file I./O functions. The symbolic links and PL/SQL file I/O could be used to circumvent normal operating system permission checking, and allow users read/write access to directories to which they would not otherwise have access.
UTL_FILE_DIR=/appl/gl/log UTL_FILE_DIR=/appl/gl/out
then the following file locations and filenames are valid:
FILE LOCATION FILENAME /appl/gl/log L10324.log /appl/gl/out O10324.out
but the following file locations and filename are invalid:
FILE LOCATION FILENAME /appl/gl/log/backup L10324.log # subdirectory /APPL/gl/log L10324.log # uppercase /appl/gl/log backup/L10324.log #dir in name /usr/tmp T10324.tmp # not in INIT.ORA
There are no user-level file permissions. All file locations specified by the UTL_FILE_DIR parameters are valid, for both reading and writing, for all users of the file I/O procedures. This can override operating system file permissions.
TYPE file_type IS RECORD (id BINARY_INTEGER);
The contents of FILE_TYPE are private to the UTL_FILE package. Users of the package should not reference or change components of this record.
The parameters for this procedure are described in Table 8 - 15, and the syntax is shown below.
FUNCTION FOPEN(location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;
Attention: Operating system-specific parameters, such as C-shell environment variables under UNIX, cannot be used in the file location or file name parameters.
The parameter for this function is described in Table 8 - 16, and the syntax is shown below.
FUNCTION IS_OPEN(file_handle IN FILE_TYPE)
RETURN BOOLEAN;
| Parameter | Description |
| file_handle | An active file handle returned by an FOPEN call. |
The parameters for this procedure are described in Table 8 - 17, and the syntax is shown below.
PROCEDURE FCLOSE (file_handle IN OUT FILE_TYPE);
| Parameter | Description |
| file_handle | An active file handle returned by an FOPEN call. |
Attention: FCLOSE_ALL does not alter the state of the open file handles held by the user. This means that an IS_OPEN test on a file handle after an FCLOSE_ALL call still returns TRUE, even though the file has been closed. No further read or write operations can be performed on a file that was open before an FCLOSE_ALL.
PROCEDURE FCLOSE_ALL;
If the line does not fit in the buffer, a VALUE_ERROR exception is raised. If no text was read due to "end of file," the NO_DATA_FOUND exception is raised.
Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.
The maximum size of an input record is 1022 bytes.
The parameters for this procedure are described in Table 8 - 18, and the syntax is shown below.
PROCEDURE GET_LINE(file_handle IN FILE_TYPE,
buffer OUT VARCHAR2);
The parameters for this procedure are described in Table 8 - 19, and the syntax is shown below.
PROCEDURE PUT(file_handle IN FILE_TYPE,
buffer IN VARCHAR2);
The parameters for this procedure are described in Table 8 - 20, and the syntax is shown below.
PROCEDURE NEW_LINE (file_handle IN FILE_TYPE,
lines IN NATURAL := 1);
| Parameters | Description |
| file_handle | An active file handle returned by an FOPEN call. |
| lines | The number of line terminators to be written to the file. |
The maximum size for an output record is 1023 bytes.
The parameters for this procedure are described in Table 8 - 21, and the syntax is shown below.
PROCEDURE PUT_LINE(file_handle IN FILE_TYPE,
buffer IN VARCHAR2);
| Parameters | Description |
| file_handle | An active file handle returned by an FOPEN call. |
| buffer | The text buffer that contains the lines to be written to the file. |
%s
Substitute this sequence with the string value of the next argument in the argument list (see the ``Syntax'' section below).
\n
Substitute with the appropriate platform-specific line terminator.
The parameters for this procedure are described in Table 8 - 22, and the syntax is shown below.
PROCEDURE PUTF(file_handle IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2,
...arg5 IN VARCHAR2]);
Hello, world!
I come from Zork with greetings for all earthlings.
my_world varchar2(4) := 'Zork';
...
PUTF(my_handle, 'Hello, world!\nI come from %s with %s.\n',
my_world,
'greetings for all earthlings');
If there are more %s formatters in the format parameter than there are arguments, an empty string is substituted for each %s for which there is no matching argument.
Flushing is useful when the file must be read while still open. For example, debugging messages can be flushed to the file so that they can be read immediately.
The parameter for this procedure is described in Table 8 - 23, and the syntax is shown below.
PROCEDURE FFLUSH (file_handle IN FILE_TYPE);
| Parameters | Description |
| file_handle | An active file handle returned by an FOPEN call. |
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |