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


SQL & PL/SQL :: Clob To Flat File?

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);
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;

View 14 Replies View Related

SQL & PL/SQL :: Export GB Data In Flat File

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

SQL & PL/SQL :: Producing Pipe Delimited Flat File

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

Building Data Warehouse From Single Flat File

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

SQLLDR With CLOB Data In Single Flat File?

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

SQL & PL/SQL :: Unable To Load A Flat File Through Oracle Loader?

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


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

PL/SQL :: Create Multiple External Tables From Same Flat File?

Nov 28, 2012

using oracle 10g currently create many external tables like so..


"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)

is there any way I can use 1 flat file (csv) to populate many external tables ?

View 4 Replies View Related

PL/SQL :: NLS Parameters - Exporting Couple Of Views Into Flat File

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 AM|07-JAN-11 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:

v_sql         VARCHAR2 (32000);
v_sql_count   NUMBER             := 0;
v_error       VARCHAR2 (4000);
v_new_file    UTL_FILE.file_type;
I also tried to do the following on production in order to get it equal to the test environment:


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





View 2 Replies View Related

Client Tools :: How To Spool Clob Data Into Flat File

Oct 11, 2011

I want to spool CLOB data into flat file ?

View 6 Replies View Related

SQL & PL/SQL :: Oracle Stored Procedure To Load Flat File Data

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

SQL & PL/SQL :: How To Load Flat File Into Database At Regular Interval Time

Apr 5, 2011

how can we load a Flat file into a Database At Regular Interval Time.

View 2 Replies View Related

SQL & PL/SQL :: Creating Store Procedure That Will Accept A Username From A Flat File?

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)
end addUsers;


View 21 Replies View Related

Server Utilities :: Import A Flat File Into Oracle And Update Another Table

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:



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:

INFILE 'C:OracleTestReturnedFile.txt'


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

Server Utilities :: Flat File Tab Delimiter Import Messed Up With SQL Loader

Jan 4, 2012

I am trying to import data from below content.

Flat File

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.!!!!

INFILE 'FlatFile.txt'
BADFILE ''FlatFile.bad'
DISCARDFILE ''FlatFile.dsc'

INTO TABLE "table"

View 5 Replies View Related

PL/SQL :: Load Data From Flat File To Target Table With Scheduling In Loader

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

Server Utilities :: Load Selected Records From Flat File Using SQL Loader

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

SQL & PL/SQL :: Reading From A Excel File

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

SQL & PL/SQL :: Reading File With UL_FILE?

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
arquivo_ler UTL_File.File_Type;
Linha Varchar2 (1000);
arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
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.');

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;



WHERE table_name = 'INTRANET_LOAD';

The data are returned.



View 10 Replies View Related

SQL & PL/SQL :: Reading Doc File Stored In Database

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;
SELECT file_data INTO b FROM fnd_lobs WHERE file_id = 322420;
if (b is null) then

With the data displaying some boxes before and after data

View 11 Replies View Related

Reading Specific Line In Util File

Jul 1, 2013

I would like to read specific line in the file in the util file.

View 1 Replies View Related

Forms :: Reading And Writing Data In A Text File

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

Forms :: Import CSV Directly Into D2kwutil / Reading From File?

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

SQL & PL/SQL :: Ftp File To Remote Server By Reading Data From Table?

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

* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)


View 3 Replies View Related

Forms :: Slow Performance Using OLE2 Reading Xls File

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;


View 2 Replies View Related

SQL & PL/SQL :: Export Data File Using Dynamic SQL And Header?

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

Forms :: Reading Data From Excel File And Load Into Form

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

Backup & Recovery :: ORA-01115 - IO Error Reading Block From File 11?

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


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?

Recovery Manager: Release - 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)


how to resolve this problem?

View 6 Replies View Related

ORA-27047 / Unable To Read Header Block Of File

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-01565: error in identifying file
ORA-27047: unable to read the header block of file

View 1 Replies View Related

JDeveloper, Java & XML :: Section Break Conflicts With Dynamic Header In RTF File

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

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