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.
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.
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)
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?
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
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
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
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)?
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.
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.
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;
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:
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:
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]........
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.
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.
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.
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].........
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?
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.
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',
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)
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;
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.
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
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
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:
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.
