SQL & PL/SQL :: Updating A File With UTL_FILE

May 3, 2010

I have an application that creates files using the utl_file package. It works fine but one of the lines in the file should hold the number of bytes in the file (on a line formatted like 'FileSize: 2104'). Unfortunately this line is not the last line of the file and the lines that follow it are variable in length.

My approach therefore is as follows:
1. Write the 'FileSize: ' line during file creation.
2. Write the remaining lines of data to the file (but don't close it).
3. Use utl_fgetattr to find the file size.
4. Go back and find the 'FileSize' line I need to update, using get_line to read it into the buffer.
5. Append the filesize (plus the number of characters that the variable I use to store the filesize value) to the buffer string.
6. Write the line back to the file using put_line.

However I keep getting invalid file operation errors however I try to re-access the file...

PROCEDURE setUtlFileSize (pFileName IN VARCHAR2,
pFileHandle IN utl_file.file_type) IS
vbFileExists BOOLEAN;
viFileLen NUMBER;
viFileBlockSize NUMBER;
vsFileRecord VARCHAR2(2000);
[code].......

This gives me the following output.... (for two files)... currently I am using some generic exception handling just to show me the error.

File Length: 2106
File Position: 0
-29283,ORA-29283: invalid file operation
File Length: 497
File Position: 0
-29283,ORA-29283: invalid file operation
Process exited.

Two things here concern me: The File Position suggests that the current offset position is set to the start of the file... but since I had not closed or performed any other operation since the last put_line and fflush (which are used to add lines to the file) I had expected that the offset position would be the same as the file length?

Secondly: Even if the position had been reset to the start of the file I don't understand why the get_line gives me the oracle error.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Handle A File With UTL_FILE

Mar 11, 2013

I Got a scnario like a upload file contains

1.Header(Contains the File Name,Branch Name,MIS date)
2.Body(Customer Details)
3.Footer (File Name,Contians Total Number of Records and Number of Customers)

Like

CustomerDet_11-12-2013.csv,Chennai,11-12-2013
101,AAA,Savings,'M',10000
102,BBB,Current,'O',2000
103,CCC,Deposit Acct,'F',3000
CustomerDet_11-12-2013.csv,3,15000

How to handle this scnario through UTL_File

View 31 Replies View Related

SQL & PL/SQL :: File Encoding UTL_FILE

Apr 21, 2011

I'm using

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

I'm creating a file using UTL_FILE.FOPEN and UTL_FILE.PUTF

But I don't know the file I created is in which encoding ASCII, UTF-8, EBCDIC etc.

1.) How can I create files in my desired encoding using UTL_FILE?
2.) Does UTL_FILE use database encoding? If yes then how to find out database encoding?
3.) Which encoding is used by UTL_FILE by default?

View 4 Replies View Related

SQL & PL/SQL :: File Spool Using UTL_FILE

Apr 27, 2012

How to spool the Japanese characters in table using UTL_FILE. I tried with utl_file.fopen it's general,it's spooling. but i am not sure it this right way or not. in this case we need to change any character.

We can't see this characters in TOAD. Only possible in PLSQL developer

create table test1(name varchar2(1000),fname varchar2(1000))

insert into test1 values ('名を入力してください。','姓を入力してください。')

commit;

View 6 Replies View Related

To Use UTL_FILE Package To Create OS File

Apr 26, 2013

I want to use UTL_FILE package to create OS file. How to resolve this error. Oracle11g under XP.

SQL> create directory my_dir as 'c: emp';

Directory created.

1 create or replace procedure test_1(md in varchar2)
2 is
3 file utl_file.file_type;
4 begin
5 file := utl_file.fopen(md,'abc.log','w');
6 utl_file.put_line(file,'EMPLOYE REPORT');
7 utl_file.fclose(file);
8* end;
SQL> /

Procedure created.

SQL> execute test_1('MY_DIR');
BEGIN test_1('MY_DIR'); END;

RROR at line 1:
RA-06510: PL/SQL: unhandled user-defined exception
RA-06512: at "SYS.UTL_FILE", line 98
RA-06512: at "SYS.UTL_FILE", line 157
RA-06512: at "SCOTT.TEST_1", line 5
RA-06512: at line 1

View 2 Replies View Related

SQL & PL/SQL :: Read Excel File Using UTL_FILE Package

Jul 23, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Is there a way to read Excel file using UTL_FILE package ?

Like our usual method:
l_utlfile := utl_file.fopen(p_dir, p_filename, 'R',2000);
p_filename is event.xls

Or do we need to convert that file to .csv or .txt ?

View 5 Replies View Related

SQL & PL/SQL :: UTL_FILE.PUT_LINE Is Not Writing Data To A File?

Jan 18, 2011

I want to extract data from a table and write into a txt file using UTL_FILE utility.I have written the below query. I am able to see query is returning data.But it is not creating file and writing data into it.

CREATE OR REPLACE DIRECTORY DOC_PATH AS '/apps/orarpt/SJDEV/utl'
CREATE OR REPLACE PROCEDURE xxcfi_outbound_test (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
AS
CURSOR emp_cur

[code].....

View 1 Replies View Related

Create File With (other) User Having Read Permission When Using Utl_file?

Dec 23, 2012

We have a requirement to create a file using UTL_FILE package. so we tried to generate the file in directory at UNIX level,for example "/tmp" which means when we executing the stored procedure which in turn calls UTL_FILE package and create a file say "a.txt". On checking the permission for "a.txt", it shows as follows

-rw-r----- oracle dba a.txt

this means read and write for oracle user, read for dba group and no permission for other user.

our requirement is to have the following privileges at UNIX level.

-rw-r--r-- oracle dba a.txt

Is it possible to do at oracle level since the file is owned by oracle user or at UNIX level (with out logging to oracle user)?

Environment: UNIX AIX 6.1 and Oracle 11.2.0.3

View 3 Replies View Related

SQL & PL/SQL :: Loading Unique Text File Names With Utl_file

Oct 9, 2012

I am using an Oracle 8i database.We intend creating a dbms_job that would run every 5 minutes. The job executes a script that would use utl_file to read two text files (a header file and detail file) from a particular directory and load the data into temporary tables. The naming convention of the files are Parts_Master_DD/MM/YY HH:MI:SS and Parts_Stats_DD/MM/YY HH:MI:SS. Both files to be stored in the same directory.

If the file name was constant ie. Parts_Master, Parts_Stats I would not have a problem. However with each file name now going to be unique, how do I handle this using utl_file. To add to my dilemma, within an hour more than one set of files could be piped to the directory to be read into the database.

View 3 Replies View Related

SQL & PL/SQL :: Updating List In A File?

Mar 28, 2011

I have a file list containing some accounts and respective value which I need to deduct from their promo account and update thier new promo account with the new value greater than zero and update to zero if the value is less than zero.

fileA
0112345 11636
01233224350
0122331 43885
0155582 13825
01334423339

View 4 Replies View Related

Forms :: OLE2 Updating Excel File From 6i

Dec 12, 2011

I am using OLE2 to update an excel spreadsheet from Oracle Forms 6i. If the excel spreadsheet is already open, the code runs through fine, and even asks if you want to replace the file when running the lines: OLE2.INVOKE(worksheet, 'Save');

OLE2.INVOKE(workbook, 'Save');

However, the file never gets updated. There is no error that appears. I would like to have the program check if the file is open before running the program;

View 1 Replies View Related

Server Utilities :: Sqlload - 2nd File With Updating Information?

Nov 7, 2011

I'm an absolut Oracle beginner and have problems with loading csv files into an Oracle DB. I get 2 files in the morning, the first one has provisionally information like that:

03.11.2011;BFS;03:00:00;00:29:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:30:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:28:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:59:33;0,00093421;0,04295300;59,57108000;

I load them with sqlload into my table. Table keys are the frist three fields. Now in the second file there are updates in the numbers, f.e. like that:

03.11.2011;BFS;03:00:00;00:27:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:31:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:29:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:60:33;0,00093421;0,04295300;59,57108000;

I can't load the with sqlload, because the program says, the key already exists. My control file looks like that:

load data
infile 'example.csv'
badfile 'example.bad'
append
into table mf_sendung

[code]...

View 6 Replies View Related

SQL & PL/SQL :: Loop In UTL_FILE Is Not Getting Closed

Nov 1, 2013

I am trying read a File to find out the error message through UTL_FILE.m. But the loop used inside is not getting closed.

CREATE OR REPLACE procedure alert_mail_A
as
v_flag varchar2(10);
mesg varchar2(100);
c1 utl_smtp.connection; -- passing the datatype to a variable.
c varchar2 (1000);
B utl_file.file_type;
[code]........

View 1 Replies View Related

SQL & PL/SQL :: Utl_file Error In Oracle 9i?

Oct 5, 2010

I have make utl_file. Its run in oracle 10 g. But when i run oracle 9i it give some error.

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at "HR.EXP_DATA", line 9
ORA-06512: at line 1.

[code]......

View 3 Replies View Related

SQL & PL/SQL :: Oracle 9i - UTL_FILE Package?

Oct 4, 2010

oracle 9i, I am reading about UTL_FILE package.make this in Hr Schema.Where hire date between date 1 and date 2.

View 8 Replies View Related

SQL & PL/SQL :: Excel Created Using Utl_file Is Larger

Jul 1, 2010

when I am running a cursor and printing its data into an excel file using utl_file, the file size is nearly 50mb. But if I run the cursor and copy its data manually into an excel sheet the file size is only 22mb. I am unable to undersatnd why there is difference in file size.

View 6 Replies View Related

SQL & PL/SQL :: Moving Files After Using UTL_FILE Package?

Feb 21, 2013

We have a p/slq procedure that reads a *.txt file using the UTL_FILE package. The contents of the file are then inserted into a database table.

At the end of the procedure we close the open file using UTL_FILE.FCLOSE.

There is a program (non-oracle)that attempts to move the file to a new location after being read into Oracle. The problem is that the application cannot move the file as the file is locked. ie message displays that the file is open and cannot be moved to a new location.

Is there anything else that we are missing besides the UTL_FILE.FCLOSE.

View 5 Replies View Related

SQL & PL/SQL :: Writing Multiple Files Using UTL_FILE

Aug 16, 2012

I'm trying to read the data from flat file and write data into multiple files based on the condition. The value of each line is checked with the Flag Table (Id NUMBER, FlagType VARCHAR(25), Flag CHAR(1)), If the Flag is True then new file has to be created and corresponding line has to be moved into new file otherwise it has to continue with the same file.

CREATE OR REPLACE PROCEDURE rw_demo (File_In VARCHAR2, File_out VARCHAR2) IS
InFile utl_file.file_type;
OutFile utl_file.file_type;
vNewLine VARCHAR2(4000);
i PLS_INTEGER;
j PLS_INTEGER := 0;
SeekFlag BOOLEAN := TRUE;
[code].........

View 24 Replies View Related

SQL & PL/SQL :: Extra Characters While Utl_file.get_line()

Jul 21, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

i am trying to read the file, for which i m using the following:

l_utlfile_hdr := utl_file.fopen(p_dir,'header_evnt.txt', 'R');

utl_file.get_line(l_utlfile_hdr, l_hdr_evnt);

it gets these three characters at the start of the line
l_hdr_evnt = 

So i saved header_evnt.txt as UTF-8, and used utl_file.fopen_nchar, utl_file.get_line_nchar, which got rid of first two characters, but still has ¿. How to get rid of that?

View 18 Replies View Related

SQL & PL/SQL :: Read International Characters Through Utl_file Function

Jul 19, 2010

i am using oracle 10g enterprise edition.whether its possible to read international characters from the text file using the utl_file function..?

View 1 Replies View Related

SQL & PL/SQL :: UTL_FILE.FGETATTR Can't Access Files On Network Disk After Database Server Was Restarted

Jun 20, 2012

I managed to upload images to a database server, resize them, copy to the application server and everything worked just fine - the Apex page successfully displayed images. Since last week, things have broken. This is how: there's a directory object which points to application server's directory:

SQL> select * from all_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ------------------------------ -----------------------------------
SYS SLIKE_4005_UPLOAD d:gisslike_4005_upload --> on a database server
SYS SLIKE_4005 \my-iasd$homegisslike_4005 --> on an application server

SQL>

I can use a directory located on a database server:

D:GISSlike_4005_upload>dir photo_resize.*
Volume in drive D is RAID
Volume Serial Number is 88F2-69D2
Directory of D:GISSlike_4005_upload
[code]....

How come it doesn't work? I was absent last week, database server was restarted for some reason (there were Windows' updates which required restarting). After that, all applications (lucky us, just two of them, but in multiple procedures/functions) return FALSE for UTL_FILE.FGETATTR.

We recreated directory objects, but that didn't work (UNC or not, no difference). I Googled quite a lot, read Metalink notes - nothing I did solved the problem.

what these OS updates were about; maybe they are not to be blamed at all. Both servers (database & application) run MS Windows Server 2003 Standard Edition Service Pack 2. In the meantime, a colleague developed a workaround (it uses UTL_HTTP) which works, but it is MUCH slower than the previous UTL_FILE.FGETATTR option.

Why don't we keep these images on the database server (instead of the application server)?I was told that Apache is incapable of accessing mapped network directories so we used what we could.

View 13 Replies View Related

Forms :: Use Features Of Utl_file In Oracle Forms

Apr 3, 2011

can we use features of utl_file in oracle forms.

View 2 Replies View Related

Updating Table With Same Key?

Nov 19, 2008

I am using oracle 9i, and having trouble with updating a table.

I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.

why this error occurs or why this error did not occur on my PC,

[Update sql](key is CD and SDATE)
Update TBL1
set CD = 'A',

[Code]....

View 4 Replies View Related

Updating Using Subqueries

Oct 26, 2010

i was just working on one of my SQL assignments from my database management course, and thus far, this is the first that I just can't figure out. The question is:

Quote: Increase the credit limit of any customer who has any order that exceeds their credit limit. The new credit limit should be set to their maximim order amount plus $1,000. This must be done in 1 SQL statement

The bolded part is what I'm having trouble with.

What I have thus far:

UPDATE Customers
SET    CreditLimit = 1000 + (SELECT MAX(Amount) FROM Orders,  Customers WHERE Cust = CustNum)
WHERE CustNum IN (   
                   SELECT Cust
                   FROM Orders
                   WHERE Cust = CustNum
                   AND CreditLimit < Amount); 

So there's two tables that I'll be working with, Customers (the table I'm updating), and Orders (the table where the order amount is found). With the code I have so far, it does seem to be updating the correct tables at the very least, but not with the correct values. It's essentially updating the CreditLimit column with the new value of 1000 + the maximum amount in the order table, which is very close to what I want it to do, but I want it to be 1000 + the maximum amount for that specific customer.

CustNum is the primary key for the Customers table, and Cust is the foreign key that links each together.

(about the formatting, it looked much prettier in SQL Worksheet Plus)

View 8 Replies View Related

SQL & PL/SQL :: Updating CLOB Value?

Nov 9, 2011

create table top_uid(oldUID number,newUID number);

select * from top_uid;

OLDUID NEWUID
---------------------- ----------------------
1 1001
2 1002
3 1003
4 1004

create table topdUIDXML (uidinfo clob);

insert into topdUIDXML select '<filter name="test" topologyUID="1">' from dual;
insert into topdUIDXML select '<filter name="test2" topologyUID="2">' from dual;
insert into topdUIDXML select '<filter name="ftest" topologyUID="3">' from dual;
insert into topdUIDXML select '<filter name="qtest" topologyUID="4">' from dual;

select * from topdUIDXML

UIDINFO
---------------------------------------
<filter name="test" topologyUID="1">
<filter name="test" topologyUID="2">
<filter name="test" topologyUID="3">
<filter name="test" topologyUID="4">

the topdUIDXML table will contain the oldUID's in the clob XML. need to update the topologyUID in that topdUIDXML with the newUID from the top_uid.

View 5 Replies View Related

SQL & PL/SQL :: Updating Column In Every Row Except For The First

Jan 16, 2012

I am trying to find a way to update all of the rows in a table for a column EXCEPT for the very first row. I am not sure if this can be done while I enter my SET parameter or not. I have also thought about using an EXCEPTION in a stored procedure. For example, say I have the table listed below:

SQL> select * from example1;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ---------------
A 100 A 200 John 20111225
A 100 A 300 John 20111225
A 100 A 500 John 20111225
A 100 A 400 John 20111225
A 100 A 250 John 20111225
A 100 A 700 John 20111225
A 100 A 800 John 20111225
A 100 A 900 John 20111225
A 100 A 1000 John 20111225
A 100 A 1150 John 20111225
A 100 A 1275 John 20111225
A 100 A 3000 John 20111225

12 rows selected

I am wanting to update the table so that if there were more than 3 J_id's on the table for the same I_id then it will set all of the code1's and code2's to a C except for the very first one. Such as:

SQL> select * from example2;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ----------------
A 100 A 200 John 20111225
C 100 C 300 John 20111225
C 100 C 500 John 20111225
C 100 C 400 John 20111225
C 100 C 250 John 20111225
C 100 C 700 John 20111225
C 100 C 800 John 20111225
C 100 C 900 John 20111225
C 100 C 1000 John 20111225
C 100 C 1150 John 20111225
C 100 C 1275 John 20111225
C 100 C 3000 John 20111225

12 rows selected

I have done some searches and haven't seen any results.

View 12 Replies View Related

SQL & PL/SQL :: Updating CLOB

Apr 21, 2011

Ok, CLOB columns are such a hassle.

I have a variable in my script: v_field1 VARCHAR2(32000);

This is part of a cursor record:

v_mf_table IS TABLE OF mf_detail%ROWTYPE INDEX BY BINARY_INTEGER;
v_mf_record v_mf_table;

I use a FORALL to insert the data into a table:

FORALL x IN v_mf_record.FIRST .. v_mf_record.COUNT
INSERT INTO monthly_mf_snapshot VALUES v_mf_record(x);

BUT! v_field1 is > 4000 characters. Does this trash my changes of using FORALL? Do I need to deal with 4k chunks in an UPDATE instead?

View 30 Replies View Related

SQL & PL/SQL :: Updating On Select?

Jun 15, 2010

I have two tables,
CREATE TABLE repos
(
rep_key VARCHAR(10) NOT NULL,
base_term VARCHAR(100) NOT NULL,
blt_key INTEGER NOT NULL

[code]...

gloss table has the unique set of base_term as in repos. BLT_KEY will be primary_key in gloss and foreign key in repos.

Data in gloss table
BLT_KEY BASE_TERM

1 base1
2 base2
3 base3

Now, I need to update the BLT_KEY in gloss to matching entries in repos. Can I do that in a update on select statement? like,
UPDATE repos
SET blt_key = (SELECT gloss.blt_key
FROM repos,
gloss
WHERE repos.base_term = gloss.abase_term) This throws subquery returns more than one row.

And the end of update the repos table should look like,
REP_KEY BASE_TERM BLT_KEY

M001 base1 1
M002 base1 1
M003 base2 2
M004 base1 1

[code]....

Also I need a single query which can update on select as the no of records to be updated are more than 90000 in repos. So two step process would slow down the process

View 13 Replies View Related

PL/SQL :: Updating Records With Different IDs

Jun 6, 2012

Im looking for the posibility to update some records using new id with the column values with another id

example

the table contains these records:

id    gross      net

========================
7     0,1     0,0507749
8     0,2     0,1015499
9     0,5     0,2538748
10     0,83     0,4214
11     0,85     0,4315873

[Code]....

and I would like insert the same gross and net column values of ids 7 to 16 into columns with the ids 40 to 49 in the same order. therefore I would like to obtain the result that I describe below:

id    gross      net

========================
7     0,1     0,0507749
8     0,2     0,1015499
9     0,5     0,2538748
10     0,83     0,4214
11     0,85     0,4315873

[Code]....

View 4 Replies View Related

Updating Two Table Via SQL Loader

Jun 27, 2011

I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:

LOAD DATA
INFILE 'table1.dat'
INFILE 'table2.dat'
APPEND
INTO TABLE table1
WHEN tid='1'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(pid "pid.nextval", p_fname, p_lname, tid)
[code]....

The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:

table1.dat
,John,Doe,1
,Joe,Smith,1

table2.dat
10,,abc,1,
11,,xyz,1,

The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved