Search Results putf_nchar




Overview
Explore in AI Chat

Overview

SYS.UTL_FILE is the Oracle-supplied PL/SQL package that provides operating-system file input and output capability directly from PL/SQL. Within Oracle EBS 12.1.1 and 12.2.2, it serves as the underlying file-access primitive used by concurrent programs, requests, and custom extensions to read and write flat files on the database server. The package is declared AUTHID CURRENT_USER, meaning it executes with the privileges of the invoking schema and its file operations are constrained by the directory objects that schema has been granted access to.

In the EBS context, the package enables outbound interfaces (writing extract files for external systems), inbound interfaces (reading bank, payroll, or EDI files), log and exception-file generation, and data migration utilities. Because EBS runs on the database tier, all file paths resolve to directories accessible to the database server process, not to the client workstation.

Key Procedures and Functions

The package exposes 23 documented subprograms covering the full file lifecycle. The core set includes:

  • FOPEN — Opens a file for read or write based on a supplied mode, returning a file handle; the handle supplied to all subsequent operations.
  • FOPEN_NCHAR — Opens a file for national character set (NCHAR) operations.
  • IS_OPEN — Tests whether a given file handle refers to an open file.
  • FCLOSE — Closes an individual open file handle.
  • FCLOSE_ALL — Closes all open file handles for the session.
  • GET_LINE and GET_LINE_NCHAR — Read a single line from an open file.
  • PUT and PUT_NCHAR — Write a text string to the current line without a line terminator.
  • NEW_LINE — Terminates the current output line; the customary companion to PUT.
  • PUT_LINE and PUT_LINE_NCHAR — Write a line of text terminated by a newline. PUT_LINE_NCHAR writes data in the database national character set, which is the subprogram associated with the user's search term "put_line_nchar".
  • PUTF and PUTF_NCHAR — Formatted write supporting substitution of escape sequences such as newline and tab.
  • FFLUSH — Forces buffered output to be physically written to the operating-system file.
  • PUT_RAW and GET_RAW — Binary read and write for raw data.
  • FSEEK — Repositions the file pointer to a specified byte offset.
  • FREMOVE — Deletes a file on the server.
  • FCOPY — Copies content between an open file and a destination file, optionally with line ranges.

The package defines a rich exception set, including file_open, invalid_path, invalid_mode, invalid_filehandle, invalid_operation, read_error, write_error, internal_error, invalid_maxlinesize, invalid_filename, access_denied, invalid_offset, delete_failed, rename_failed, and charsetmismatch. Each carries a fixed error code in the range -29280 to -29298, bound via PRAGMA EXCEPTION_INIT. Note that access-path validation historically relies on UTL_FILE_DIR or, in modern releases, directory objects.

Tables Accessed

UTL_FILE does not read or write application tables, nor does it reference any APPS synonym-backed tables. Its operations are directed exclusively at operating-system files referenced through database directory objects. Any table interaction occurs in the calling code — for example, a concurrent program that queries EBS interface tables and writes the result set out with PUT_LINE.

Usage Notes

UTL_FILE is normally wrapped in custom PL/SQL rather than called directly. Typical invocation patterns include Oracle Forms client-side handlers, concurrent program executables, database triggers, and standalone scripts. The canonical pattern is FOPEN, repeated PUT_LINE or PUT_LINE_NCHAR calls, FFLUSH, and FCLOSE with appropriate exception handling.

For NCHAR workloads, matching FOPEN_NCHAR with GET_LINE_NCHAR, PUT_NCHAR, PUT_LINE_NCHAR, and PUTF_NCHAR prevents charsetmismatch errors. Because the package is AUTHID CURRENT_USER, the invoking schema must be granted the relevant directory object, and on multi-node EBS environments the directory must be visible to the database instances on which the concurrent manager runs. UTL_FILE is also referenced by 29 other packages in the EBS data model, confirming its role as a foundational utility rather than an end-user interface.