Stack Overflow
Stack Overflow

Questions tagged [utl-file]

An Oracle package to enable reading and writing of text files on the operating system.

utl_file is an Oracle package that offers a limited version of standard operating system file I/O to read and write text files outside of the database.

Access to files is possible through a directory object but is also restricted by any operating system privileges.

Questions tagged should also be tagged .

Further reading:

164 questions
2
votes
2 answers

Oracle / Plsql do I need to check that the file is saved?

ORA-29280: ORA-06512: "SYS.UTL_FILE", line 270 ORA-06512: "SYS.UTL_FILE", line 1243 ORA-06512: line 9 declare fhandle UTL_FILE.FILE_TYPE; begin /*********************************************/ -- if statement ( file1.txt…
berkay91
  • 31
  • 4
2
votes
1 answer

Where can I access a file from my oracle db with UTL_FILE?

I'm trying to read from a file with oracle db using UTL_FILE. I can't find a file location I have access to. Whenever I use this code: DECLARE F1 UTL_FILE.FILE_TYPE; BEGIN F1 :=UTL_FILE.FOPEN('C:\TEMP','test_file.txt','R'); END; I get:…
Kresten
  • 810
  • 13
  • 36
2
votes
1 answer

PL/SQL file writing with generic input

I recently created a PL/SQL program that creates five different pipe delimited files from related data in a database. I could not find a way to dynamically pull different tabular data in this case cursors, into a generic procedure that would create…
Clint L
  • 1,093
  • 5
  • 12
  • 29
2
votes
4 answers

Delete file from server using UTL_FILE.FRemove without knowing the file name

I am a reporting analyst, who was asked to learn some PL/SQL to automate some processes. So I am almost finished, with one step standing in the way. I have a package that Loads a table with a query I wrote. Exports the results from that table to…
Ben O'Neill
  • 55
  • 1
  • 1
  • 7
2
votes
1 answer

Generating Excel by PL/SQL UTL_FILE, Style error

I'm using ExcelDocTypeUtils pkg for exporting Query data to EXCEL FILE. When I run the procedure I'm getting a excel file, but when I try to open it this error pops out: Program came up in the following areas during load: Style The original line…
Dexter
  • 31
  • 3
2
votes
1 answer

What are common Causes for WRITE_ERROR,Oracle PL/SQL?

Recently, I encountered many UTL.FILE.WRITE_ERROR , but the doc does not explain all the causes. For example: Oracle PL/SQL UTL_FILE.PUT buffering describes one situation that causes this issue that when we called put(even we fflush after every…
JaskeyLam
  • 15,405
  • 21
  • 114
  • 149
2
votes
2 answers

BLOB exports are slow for files larger than 32kb

I have the need the export BLOBs from our Oracle 11g (11.2.0.3) system. The process works great for the BLOBs (JPG photos) that are < 32,767 bytes. I'm able to export ~4000 photos in under 5 seconds to a local directory on the database server…
Doug Purnell
  • 23
  • 1
  • 3
2
votes
4 answers

ORACLE 11G UTL_FILE + UTL_FILE_DIR - Invalid Directory

Hi I am running Oracle 11 and I am trying to write to a directory on the server box using UTL_FILE.FOPEN. To test this I am using the following script (output included): SQL> @D:\test.sql declare l_file_handle …
Robert Gallow
  • 23
  • 1
  • 1
  • 5
2
votes
1 answer

Bulk extraction of Oracle BLOBS into files - advice/tuning help needed

I am working on a project that needs migration of existing Oracle blobs into files. The environment to read from is a shared Oracle 10gR2 server. Currently I have a script using UTL_FILE. However, the process is pretty slow. It takes around 3 hours…
sammy
  • 524
  • 4
  • 11
1
vote
1 answer

How do I convert the contents of multiple tables to XML in SQL*Plus?

I have an assignment where I need to write the XML for multiple tables(17). The professor told us we could use DBMS_XMLGEN and linked us to an example PL/SQL script to do it. The problem is that I don't want to rewrite or copy-paste the script 17…
Michael Rivera
  • 11
  • 1
1
vote
2 answers

UTL_FILE, UTL_FILE_DIR and DIRECTORY, how do they affect UTL_FILE.FREMOVE?

FREMOVE is failing with an error "ORA-29280: invalid directory path" It works fine when I add the path to the UTL_FILE_DIR and restart the database. This is regardless of having the directory as an Oracle directory with both READ and WRITE granted.
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
1
vote
2 answers

Print values of TABLE OF RECORDS TYPE without knowing the COLUMN NAMES of record

I have created a Procedure which uses a View to PRINT (into CSV ) , all the records of that view. Any user can drop and recreate that view , so its column structure can change Is there any way to FETCH all records of VIEW into a collection and PRINT…
Swechchha
  • 38
  • 7
1
vote
1 answer

Oracle 18c - PL/SQL limiting records written to file

I have a procedure which creates a file with as many records returned from a cursor as possible. Due to downstream file size issues, I want to limit each file to ~500k(n) records (value to derive from a parameter table). If there are more than…
user3224907
  • 758
  • 2
  • 15
  • 40
1
vote
0 answers

How to solve "file or LOB operation FILEOPEN failed" while inserting xml data into oracle table?

I followed these steps to insert XML data into an Oracle table: Created one directory and created one table. What are the columns having that XML file I created the table also. Now I'm inserting the data into that table I'm getting an error which…
Ramesh
  • 11
  • 1
1
vote
2 answers

UTL_FILE using append mode creates repeating headers

How to use "A"ppend mode in UTL_FILE package, but only create one header (not repeating)? Is it possible? I'm appending data, but everytime it appends, it will create repeating headers. My code: CREATE OR REPLACE PROCEDURE p_test AS CURSOR c_test…
BFF
  • 378
  • 6
  • 15
1
2
3
…
10 11