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
0
votes
0 answers

Oracle sql : read/write to CSV files using sql

By using SQL, I need to read data from a CSV file that contains rows of 3 values and then use it in a WHERE clause in a SELECT statement, after that dump the results of the SELECT (7 fields in total) in a new CSV file. The query looks like : SELECT …
SWEEPY
  • 589
  • 1
  • 6
  • 20
0
votes
1 answer

Oracle utl_file throwing error when the input buffer size exceeds more than 1000 character

I'm tyring to write files for every cursor execution as shown in below sql. The data_payload column will have more than 1000 characters. There is an exception at utl_file.put line. If I use SUBRTR of 1000 characters, then the file writes…
BK Barathi
  • 91
  • 2
  • 9
0
votes
1 answer

Utl_File not generating file in the server path

I have executed a block of code which generates a simple text file using utl_file package with a word 'test' and outputs the file to the location in server. When i run the procedure it compiles successfully but the file is not generated in the…
Gautam S
  • 41
  • 1
  • 1
  • 7
0
votes
1 answer

HPL/SQL UTL_FILE.PUT_LINE Puts NULL Character Between Each Character

I'm writing text to a file directly to HDFS using HPL/SQL's UTL_FILE function PUT_LINE(). Each line in the file consists of several text fields delimited by a semicolon. Note: When I "hadoop copyToLocal" and open that file in vi, I see one NULL…
sheepsqueezers
  • 89
  • 7
0
votes
0 answers

PLSQL procedure to write to File XML Data - some data sometimes gets missed

I have a PLSQL procedure that is reading data from an underlying table, and writing the same to a file, which it then pushes to oracle object storage. This procedure is called multiple times in loop via an Integration in Oracle Integration Cloud.…
Mohak
  • 1
  • 1
0
votes
1 answer

Number of rows inserted/updated in utl_file

How do i print number of rows in utl_file. If i am using dbms_output.put_line('Total record'||'|'||SQL%ROWCOUNT); and If i am ​using dbms_output.put_line('Total record'||'|'||To_char(SQL%ROWCOUNT)); Compiler saying wrong argument is passed.…
shoaib mansori
  • 1
  • 2
0
votes
0 answers

HOW TO ADD HEADER AND FOOTER AT THE OF THE CODE TO GET THE REQUIRED O/P

Write a procedure to genearte a report of all employee with managers name details with | (pipe delimiter) in unix development server /tmp path. Example:** Emp ID|Emp Name|Manager Name|Sal|Dept No|Location …
shoaib mansori
  • 1
  • 2
0
votes
1 answer

PLS-00404: list of the cursor in order to avoid duplicate column requested Alias names

I have a query, this query is joining between two tables and those tables have a lot of columns and some columns have the same name, when I run the query alone in SQL windows work fine without any error, but when I put it in the cursor I get the…
M.Youssef
  • 146
  • 9
0
votes
1 answer

Can we find out directory name , path and other details related to files in oracle? If yes, what is the query for that?

SELECT * FROM DBA_DIRECTORIES; --gives all list of directory in database. select * from DBMS_CLOUD.LIST_FILES('tmp'); --gives detail about files in tmp directory. Can we find out directory name, path and other details related to files in…
PRIYA GOUR
  • 11
  • 1
0
votes
0 answers

UTL_FILE. FOPEN and ora-06502

I have to read a big file line by line, usually more than 400 kb, change some values and write the file changed in another file. My problema now is that I'm having a ora-06502 in the fopen .... the code is like this CREATE OR REPLACE PROCEDURE…
Dembora
  • 3
  • 2
0
votes
1 answer

How to handle Chineese/Japenese characters while writing data to a .csv file using utl.file wirte plsql

I was trying to use the below to handle the Japenese characters in my data set but it dosent seem to work. UTL_FILE.PUT_LINE(output_file1,chr(9)||CHR(15711167)); Can anyone suggest any alternatives for the same. Below id the PL-SQL code. …
Gaurav Thuckral
  • 13
  • 3
0
votes
1 answer

PL/SQL - UTL_FILE physical location of the file

I want to use UTL_FILE pkg to read data from a csv file, I already have the code for it, but where exactly I should place the file? I know that I need to give the path with the UTL_FILE.FOPEN, and I can also create a directory, but I can't find it…
quinnyke
  • 21
  • 3
0
votes
1 answer

PL/SQL UTL_FILE package read from csv and load values into a table

If I have a CSV file like this: How can I read this with UTL_FILE package and load the values into one table which have columns: ItemIdentifier, SoldOnWeb, SoldInTheShop?
quinnyke
  • 21
  • 3
0
votes
0 answers

Possibility of writing stale data to a file using utl_file.put_raw in pl/sql

I am writing a blob into a file system file using the following code: create or replace PROCEDURE SAVE_FILE_TO_DISK (image_id NUMBER, dir IN VARCHAR2 , file_name IN VARCHAR2) IS image_blob BLOB; img_blob_size INTEGER; destination_file …
Rustam Issabekov
  • 3,279
  • 6
  • 24
  • 31
0
votes
1 answer

PL/SQL UTL_FILE: How the loop automatically returns the next line?

This is PL/SQL code I am using for working with UTL_FILE package: DECLARE vfile utl_file.file_type; vfloc VARCHAR2(100) := 'UTL_TST_DIRC'; vline VARCHAR(32676); BEGIN vfile := utl_file.fopen(vfloc, 'test.txt', 'r', 32767); …
Dev Anand
  • 314
  • 1
  • 14
1 2 3
…
10 11