SQL & PL/SQL :: Reading Flat File With Header?
Jul 3, 2012
working on loading the data from flat file into table and below given is the validation condition given.I checked the UTL_FILE build in package but not able to figure out, how to identify the column header in flat file.
1. Skip the header, if any. The header is the first record, and starts with '000'
2. Skip the trailer, if any. The Trailer is the last record, and starts with '999'
3. Log an error, but continue if a line exceeds 512 characters
4. Log an error, but continue if a line is blank
View 5 Replies
ADVERTISEMENT
Nov 20, 2012
Just wanted to export a clob field to .txt file, the maximum length of the clob field exceeds the limit 32767. So only partial data is exported to flat file. is there any way to export the entire data available in clob field irrespective of the size or lenght.
Length of the clob is 301829
l_file := UTL_FILE.fopen('LOCALDIR', '3.txt', 'w', 32767);
LOOP
DBMS_LOB.read (v_text_exp, l_amount, l_pos, l_buffer);
UTL_FILE.put(l_file, l_buffer);
l_pos := l_pos + l_amount;
END LOOP;
View 14 Replies
View Related
Nov 25, 2011
Database: Oracle 8i
My query is:
I have large data in the one table(approx. 2GB and above). I want to load the data in one flat file.
When i use spool - it is loading half data and remaining it is corrupting. In UNIX, i kept .sql file and with that i am exporting in .dat file. How to export the large data into flat file(.dat). is there any way to load command which wil be used in UNIX.
View 8 Replies
View Related
Oct 20, 2011
I have a task to code a procedure and function in sql developer that will extract data within a date range (Jan 1 to April 3) from a source (source_name: expenses)and produce a text-file in pipe-delimited format.
View 2 Replies
View Related
May 2, 2012
I am trying to build a data warehouse for Consumer Price Index and so I have downloaded data from the Bureau of Statistics.It is in excel format and since I am working with Oracle Warehouse Builder I have converted it to .csv file so that I can use it as a data source.
Question1: Is it practical to use single .csv file as a source of data for a data warehouse?
Question2: I have 3 dimensions tables and a fact table.The dimensions are one for the Region(as the date is organized in region,states etc),two is the consumer goods and services (as the data is organized in groups of goods and services, services/goods types) and finally time(year and month),
Now how am I going to do the mapping here?Is it possible to do a one to one mapping here as all data required by the dimensions is located in the .csv file.
View 4 Replies
View Related
Nov 24, 2012
I am migrating data from DB2 to Oracle. I used DB2 export to extract the data specifying lobsinfile clause. This created all the CLOB data in one file. So a typical record has a column with a reference to the CLOB data. "OUTFILE.001.lob.0.2880/". where OUTFILE.001.lob is the name specified in the export command and 0 is the starting position in the file and 2880 is the length of the first CLOB.
When I try to load this data using sqlldr I'm getting a file not found.Attached is a copy of the control file and output from testing
PS. I cant use the DB2 option LOBSINSEPFILES which creates a separate file for each CLOB column because the table has over 14 million rows....and creating 14 mil files causes OS inode problems...
Attached File(s)
sqlldr.txt ( 2.05K )
Number of downloads: 3
View 5 Replies
View Related
Sep 28, 2011
Issue: Unable to load a flat file through Oracle Loader
Below is the script that is being used:
drop table dl_fact_fac_data_xtern;
create table dl_fact_fac_data_xtern
(
[Code].....
After rnning this script, it prompts that table has been created; but once I fire the select command on the table I receive the following errors :
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "data": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 10 column 11
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
View 2 Replies
View Related
Nov 28, 2012
using oracle 10g currently create many external tables like so..
CREATE TABLE "XT_UNITS"
(
"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....
is there any way I can use 1 flat file (csv) to populate many external tables ?
View 4 Replies
View Related
Jan 11, 2013
In one of my projects I am exporting a couple of views into a flat file. The export utility is generic and uses dynamic sql to generate a flat file. We have a test environment and a production environment. On both the code is the same. We noticed that the output is different between the environments although it is supposed to be the same. If I export a view in the production I will get a record like this:
0020110107O0000000001|OTHER|07.01.11 08:06:00,296000|07.01.11 08:04:41,008000||0|0|EUR||NOT_FROZEN|MVOIP||IS_NORMAL_VERSION|MODIFIED|6863475590797607166|8648564326455689103|8011808169304472215|||CCP||||10000580||||DEKA|PS
In the test environment it will be like this:
0020110107O0000000001|OTHER|07-JAN-11 08.06.00.296000 AM|07-JAN-11 08.04.41.008000 AM||0|0|EUR||NOT_FROZEN|MVOIP||IS_NORMAL_VERSION|MODIFIED|6863475590797607166|8648564326455689103|8011808169304472215|||CCP||||10000580||||DEKA|PS
The code I am running is not changing any settings explicitly. It looks like this and it will be run as EXECUTE IMMEDIATE:
DECLARE
v_sql VARCHAR2 (32000);
v_sql_count NUMBER := 0;
v_error VARCHAR2 (4000);
v_new_file UTL_FILE.file_type;
BEGIN
[code]........
I also tried to do the following on production in order to get it equal to the test environment:
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = AMERICAN '
|| 'NLS_NUMERIC_CHARACTERS = ''.,'''
|| 'NLS_TIMESTAMP_FORMAT = ''DD-MON-RR HH.MI.SSXFF AM''';
END;
This would change the formatting for the timestamp columns for almost all files. Almost. Two of those files remain unchanged and still show the decimal separator from the old setting:
0020110107O0000000001|OTHER|07-JAN-11 08.06.00,296000 AM|07-JAN-11 08.04.41,008000 AM||0|0|EUR||NOT_FROZEN|MVOIP||IS_NORMAL_VERSION|MODIFIED|6863475590797607166|8648564326455689103|8011808169304472215|||CCP||||10000580||||DEKA|PSAny
[URL]......
[URL]......
[URL].....
[URL]......
View 2 Replies
View Related
Oct 11, 2011
I want to spool CLOB data into flat file ?
View 6 Replies
View Related
Oct 24, 2013
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies
View Related
Apr 5, 2011
how can we load a Flat file into a Database At Regular Interval Time.
View 2 Replies
View Related
Apr 8, 2012
I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.
Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.
create or replace procedure addUsers(userNam in varchar2)
is
begin
EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'||
'PASSWORD EXPIRE';
end addUsers;
/
[code].....
View 21 Replies
View Related
Jul 5, 2013
I have a text file called ReturnedFile.txt. This is a comma separated text file that contains records for two fields.... Envelope and Date Returned.
At the same time, I have a table in Oracle called Manifest. This table contains the following fields:
Envelope
DateSentOut
DateReturned
I need to write something that imports the ReturnedFile.txt into a temporary Oracle table named UploadTemp, and then compares the data in the Envelope field from UploadTemp with the Envelope field in Manifest. If it's a match, then the DateReturned field in Manifest needs updated with the DateReturned field in UploadTemp.
I've done this with SQL Server no problem, but I've been trying for two days to make this work with Oracle and I can't figure it out. I've been trying to use SQL*Loader, but I can't even get it to run properly on my machine.
I did create a Control file, saved as RetFile.ctl. Below is the contents of the CTL file:
LOAD DATA
INFILE 'C:OracleTestReturnedFile.txt'
APPEND
INTO TABLE UploadTemp
FIELDS TERMINATED BY "'"
(
ENVELOPE,
DATERETURNED
)
If I could get SQL*Loader running, below is the code I came up with to import the text file and then to do the compare to the Manifest table and update as appropriate:
sqlldr UserJoe/Password123 CONTROL=C:OracleTestRetFile.ctl LOG=RetFile.log BAD=RetFile.bad
update Manifest m set m.DateReturned =
(select t.DateReturned
from UploadTemp t
where m.Envelope = t.Envelope
*)
That's all I got. As I said, I can't find a way to test it and I have no idea if it's even close.
View 2 Replies
View Related
Jan 4, 2012
I am trying to import data from below content.
Flat File
PARENTCHILDALIAS
PLAN_PCOTDefaultPlanning Customer
1001_BTPCOTDefaultGeneral Planning Customer
2000_BTPCOTDefaultNational Account Planning Customer
3000_BTPCOTDefaultDistributor Planning Customer
3010_BTPCOTDefaultEducation Planning Customer
3020_BTPCOTDefaultResearch Planning Customer
OPT1_PCOTDefaultOption 1 Planning customer
OPT2_PCOTDefaultOption 2 Planning customer
OPT3_PCOTDefaultOption 3 Planning customer
The problem here is , When you try to import to a table which has same columns . I skipped the first line when loading . The issue here is the second field is getting split in to the two columns . for e.g. :- Default goes to Child and Remaining goes to the Alias.
infarct there is a tab at the end of the each line. How to set the Sql loader settings correctly so that I can populated the end column in CHILD column only.!!!!
OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'FlatFile.txt'
BADFILE ''FlatFile.bad'
DISCARDFILE ''FlatFile.dsc'
INTO TABLE "table"
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY "''" TRAILING NULLCOLS
(PARENT,
CHILD,
ALIAS CONSTANT '')
View 5 Replies
View Related
Sep 7, 2013
I have requirement as follows. I need to load the data to the target table on every Saturday. My source file consists of data of several sates. For every week i have to load one particular state data to target table. If first week I loaded AP data, then second week on Saturday karnatak, etc.
Provide code also how can i schedule the data load with every Saturday with different state column values automatically.
View 2 Replies
View Related
Nov 24, 2011
Load the selected records from the flat file using SQL*Loader.
I have a flat file it's having 100 records, I want to load first 10 records from the file using SQL*LOADER.
View 8 Replies
View Related
Dec 1, 2011
I am looking for a code/script to read values from excel file and perform PLSQL script.
Now i have the PLSQL script to generate report which takes two value which i have to change all the time to generate new report .All i wanna do create a script to read from a excel file and perform the other script.
I have been searching from a long time and only found UTL_file Package which use CREATE OR REPLACE FUNCTION and create some virtual table.
The problem is i don't have create authorization in database so i m not able to use UTL_File command . Is there any simple way to read value from excel file?
View 16 Replies
View Related
Jul 8, 2013
I'm trying to utilize the utl file to read a txt file and import the data into a table in Oracle. I've read in various forums and have researched a lot on oracle documentation site and on the internet but can not find the answer to the problem.
The source follows:
Set serveroutput on
DECLARE
arquivo_ler UTL_File.File_Type;
Linha Varchar2 (1000);
BEGIN
arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
Loop
UTL_File.Get_Line (arquivo_ler, Linha);
dbms_output.put_line (Linha);
End Loop;
UTL_File.Fclose (arquivo_ler);
DBMS_OUTPUT.PUT_LINE ('File processed with sucesso.');
END;
/
The errors:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 633
ORA-29283: invalid file operation
ORA-06512: at line 5
What has been done:
Created DIRECTORY (INTRANET_LOAD) and given the GRANT read, write to the user
On Linux where Oracle is installed, was given full access to the Oracle user folder: / u01/app/oracle/product/11.2.0/db_1/adp
When writing the query;
SELECT *
FROM ALL_TAB_PRIVS
WHERE table_name = 'INTRANET_LOAD';
The data are returned.
1 FOLLOW INTRANET INTRANET_LOAD SYS READ NO NO
2 FOLLOW INTRANET IN NO WRITE SYS INTRANET_LOAD
View 10 Replies
View Related
Jun 8, 2011
A doc file stored in a database having data type blob. To read this file I have written Following procedure
create or replace procedure XX_read_blobfile1 as
b blob;
c clob;
n number;
begin
SELECT file_data INTO b FROM fnd_lobs WHERE file_id = 322420;
if (b is null) then
[code]......
With the data displaying some boxes before and after data
View 11 Replies
View Related
Jul 1, 2013
I would like to read specific line in the file in the util file.
View 1 Replies
View Related
Mar 8, 2011
I need to read data from text file(located on application or db server or on some other server, however path is known to me.) and then append some data in it.
Data will be read and written on daily basis so i want to clear all data on date change.
View 3 Replies
View Related
Jan 29, 2008
I know that this topic in not new, but maybe my case is a bit different than common.My form has multi-record block (4 columns) in which i want to upload CSV file.
The form will have "Browse..." button to piont CSV file (on local users disc, not where Forms server resides) after pointing the CSV file, user will press UPLOAD button which will import CSV data into block. After that data is visible in block. But whole form must not be refreshed.
I'm studying about UTL_FILE package, but it seems it allows manipulating files on server not local user's machines. Do Forms have such "Browse... -> UPLOAD" possibility or I need to learn Java and create Java funcionality inside Forms?
Or it is impossible to load data directly into form? (I have to use SQLoader and load data into temp table then into block on form????)
View 7 Replies
View Related
May 1, 2012
I need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..
Here is the code
CREATE OR REPLACE PACKAGE UTL_FTP
AUTHID CURRENT_USER
AS
/**
* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)
[code].....
View 3 Replies
View Related
Oct 10, 2011
I just trying to import some informations from excel to Oracle using OLE2 over Oracle Forms 6i, but It´s very slow when I have import under then 10k lines. anything to optimize that ? Follow the code used...
application OLE2.Obj_Type;
workbooks OLE2.Obj_Type;
workbook OLE2.Obj_Type;
[Code]....
View 2 Replies
View Related
May 23, 2010
I have a set of store procedures that will construct the header and data dynamically. This procedure will return a CURSOR.Now, I will write a new procedure to export data file by calling the above store procedure.
a) is it possible for me to retain the dynamic header when I export the data out ?
b) use 1 export data file procedure to handle it without coding for each data file I want to export.
I have been testing manually creating the header. I am assigning the header string myself.
UTL_FILE.PUTF(fHandler, header_string);
and then use a cursor to loop through the data for each store procedure.
UTL_FILE.PUTF(fHandler, record_string);
View 4 Replies
View Related
Oct 25, 2013
I have a Excel File which contains some columns and rows, i need to load that excel into a form and import that form data to Database Table, using DDE Method.
In simple i say; Just to read the excel and load into a form, which can be imported into a table later.
How to achieve this? only dde method.
View 10 Replies
View Related
May 17, 2013
During the scheduled backup we observed that, backup got failed. Error is "ORA-01115: IO error reading block from file 11 (block # 118736)
".
unable to rectify it. Below are the part of log file -
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MAIN [TABLE_DATA:"DW_REP"."DW_MOTOR_CLAIM_INFO_DTLS"]
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 8976
ORA-24795: Illegal ROLLBACK attempt made
[code]...
This is development server. Dont have RMAN configured.But when we connected to RMAN and issued block recovery, it started process and shows media recovery done. Since no RMAN backup was taken before, what it does here?
C:UsersAdministrator>RMAN
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 17 10:48:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> CONECT target /
connected to target database: MAGMADEV (DBID=2469200049)
[code]...
how to resolve this problem?
View 6 Replies
View Related
Apr 26, 2011
I am trying to create standby with oracle 10g R2 from Solaris 5.10 (sun ultrasparc X86-64 bit) to Solaris 5.10(Intel Xeon 64 bit). All the mount points are the same as primary server. All the data files are at location as specified in control file. I created standby control file from primary. All the control files are at location specified in pfile.
When I am trying to mount database I am getting following error.
ORA-00202: control file: '/oracle/BP1/ctl1.dbf'
ORA-27047: unable to read the header block of file
ORA-205 signalled during: alter database mount...
I try to open database with trace file also but got following error..
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/oracle/BP1/sapdata1/system_1/system.data1'
ORA-27047: unable to read the header block of file
View 1 Replies
View Related
Jun 26, 2012
I have dynamic header in my rtf file. I have section break on start group of body. but it does not display dynamic header value. If i remove section break then it display dynamic header.
I have to display dynamic header and section break is also required there.
View 4 Replies
View Related
