Server Utilities :: SQL Loader With WHEN And DISCARD?

Sep 25, 2012

My data file contains records with RECORD_TYPE '01', '02', '03' and '04' in position (30:31) I use sql loader to load this data into a table. I need to load ONLY rows with RECORD_TYPE ='04'. I have to output all the other rows into a file, so I decided to use DISCARD file. Now, those with RECORD_TYPE ='4' have to be loaded into different columns depending on the value in position (267:268).

So, my ctl file should look something like:

WHEN (30:31) = '04'
into table MYDATA
WHEN (267:268) != 'O '
into table MYDATA
WHEN (267:268) = 'O '

and whatever is not '04' goes to discard file.

I tried to use

into table MYDATA
WHEN (30:31) = '04' and (267:268) != 'O '
into table MYDATA
WHEN (30:31) = '04' and (267:268) = 'O '

but I don't get the right result in terms of the discard file.

Is there any way to put together all these conditions?

View 3 Replies


ADVERTISEMENT

Server Utilities :: SQL Loader Discard Non-numeric Chars

Sep 30, 2011

Creating a table

CREATE TABLE NEW_DATA
(INK_DATE DATE,
INV_ID NUMBER,
CUST_ID NUMBER,
AMOUNT NUMBER)

Loading data into the table with

LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'input/NEW_DATA.dat' "str '
'"
BADFILE 'log/NEW_DATA.bad'
DISCARDFILE 'log/NEW_DATA.dsc'

TRUNCATE INTO TABLE NEW_DATA
FIELDS TERMINATED BY '|'
(INK_DATE date "YYYY-MM-DD",
INV_ID,
CUST_ID,
AMOUNT)

My problem is that there are some rows (about 1%) where the columns INV_ID, CUST_ID, AMOUNT are containing non numeric characters and they end up in the BAD-file as errors.

Is there a way to make them end up in the discard file instead so I don't end up with errors but discards?Or even better load then into another table looking like this

(INK_DATE DATE,
INV_ID varchar2,
CUST_ID varchar2,
AMOUNT varchar2)

Do I need to have a WHEN-clause?

View 8 Replies View Related

Difference Between BAD And DISCARD File In SQL Loader

Sep 18, 2012

What is the difference between BAD File and DISCARD File in SQL Loader?

View 4 Replies View Related

Server Utilities :: SQL Loader Is Missing

Feb 4, 2011

I installed oracle 10G complete so I can have everything. But now I cannot run sql loader. I check my oracle devsuitehome directory and I cannot find sqlldr.exe

I need to install sql loader separately? I can't find sql loader installer on web.

View 3 Replies View Related

Server Utilities :: SQL Loader - How To Implement

Jul 7, 2010

SQL Loader - How to implement/Best solution.

I have 3 tables with their columns:
- MASTER_TABLE - MASTER_ID, DATA;
- PARENT_TABLE_A - MASTER_ID, DATA;
- PARENT_TABLE_B - MASTER_ID, DATA.

And the file I need to import has lines like the ones below:

MMMASTER_TABLE1
PAPARENT_TABLE_A1
PBPARENT_TABLE_B1
MMMASTER_TABLE2
PAPARENT_TABLE_A2
PBPARENT_TABLE_B2
MMMASTER_TABLE3
PAPARENT_TABLE_A3
PBPARENT_TABLE_B3

The line means:

- 1 - M or P: indicates which table to insert: MASTER or PARENT;
- 2 - M or A or B: indicates MASTER, PARENT_A, PARENT_B;
- 3:18 - DATA.

Based on the values above, what I need to do is:

1. Load a line to MASTER_TABLE;
2. Load a line to PARENT_TABLE_A pointing to its relative line in MASTER_TABLE;
3. Load a line to PARENT_TABLE_B pointing to its relative line in MASTER_TABLE;
4. In the original file line, there is nothing I can use to join a MASTER line with a PARENT line.

The result would be:
MASTER_ID PARENT_DATA
1 PARENT_TABLE_A1
1 PARENT_TABLE_B1
2 PARENT_TABLE_A2
2 PARENT_TABLE_B2

I tried to use both: SEQUENCE and Sequence.NextVall (CurrVal) but they only work when using ROWS=1 and the file I need to load has millions of rows, so I need direct path loading.Also, I read about External Table, but it does not suit my needs because the Application server is not the same as Database server, which is needed by external tables.

in this case is better load the data to a temporary table and then insert to the other tables, I found almost the same question in the topic pointed by the link below: URL....

View 7 Replies View Related

Server Utilities :: Load WKT Using Sql*loader

Mar 10, 2010

I want to load geometry into a table using sql*loader. My datafile contains geometry defined as WKT URL...., a standard for geometry and also Oracle has a function called sdo_util.from_wktgeometry.If I'm using a separate 'insert into' statement using this function in sql*plus, there's no problem. But if I'm using the same function in my control-file for sql*loader import, I get a sql*loader-418 error: "bad datafile for column geometrie".

Why and how I can import WKT using sql*loader?

-- data file
id;geometrie
1;POINT(120123.123 485345.789)
2;LINESTRING(123456.01 482543.21, 125764.76 483444.11)
3;POLYGON((121121.22 484394.22, 122887.444 484721.48, 122911.098 486382.45, 121005.21 486592.01, 121121.22 484394.22),(121922.56 485333.23, 122010.22 485854.83, 121922.56 485333.23))
4;MULTIPOINT((120586 483958.33),(120635 483726.11))
5;MULTILINESTRING((117948 480284, 118215 481236),(118475 481604, 120462 482822))
6;MULTIPOLYGON(((123678 481948, 124654 485215, 123678 481948),(127321 488321, 124907 483921, 127321 488321)))
[code]....

View 15 Replies View Related

Server Utilities :: To Use SQL Loader In A Procedure

Nov 10, 2011

I want to use SQL loader in a procedure..will it be possible to use it in procedure and if yes then how..?

View 4 Replies View Related

Server Utilities :: SQL Loader Loads Only 1 Record

Sep 21, 2011

The SQL loader somehow is loading only first record. The data file is a csv and the end of line character is a new line. Some text fields have multiple new lines.

Here is my control file

load data
infile '/home/devo/c0397105/RuleImport/testLoad/dummyLoad.csv'
Truncate
into table DUMMY_LOAD_TABLE
fields terminated by "," optionally enclosed by '"'
( ID "to_number(:ID)",REQUESTED_GROUP,PURPOSE,COMMENTS)
[code]........

We don't have Retail resource type as Dependent System now; the rule will be changed later when Dependent Systems can accept resource types other than application"

View 1 Replies View Related

Server Utilities :: SQL Loader Comma Delimiter

Dec 22, 2011

I am using sqlloader for loading the data into database by using csv file.My csv file is delimited by comma in that i am having a column which is having the , and line feeds targeted to load into a long data type.for example as below

descri,dfdfdfd,dfdfdf,
sdfsdf,
dfsdfd,

i want to move this column data into a single table column.But due to because of delimited "," it is splitting into number of columns

View 6 Replies View Related

Server Utilities :: DECODE Not Working While Using With SQL Loader?

Sep 18, 2010

I had a requirement of loading flatfile into staging table using SQL Loader, One of the columns in the the Flat file is having values FALSE or TRUE and my requirement is that I load 0 for FALSE and 1 for TRUE which can be achieved by simple DECODE function...I did use decode and tried to load several times but did not work.

INFILE 'sql_4ODS.txt'
BADFILE 'SQL_4ODS.badtxt'
APPEND
INTO TABLE members
FIELDS TERMINATED BY "|"

[code]...

I did try putting a trim as well as SUBSTR but did not work....the cloumn just doent get any values in the output (just null or say free space)

View 5 Replies View Related

Server Utilities :: CSV File As Datafile In SQL Loader

Sep 5, 2010

1) can we use a CSV file as a Data file in any format (fixed, delimited...) of Sql loader. I tried, but not succeeded.

2) if not then tell me the reason for it....

3) Also tell me is there any restriction on using the file format for a datafile?

View 18 Replies View Related

Server Utilities :: SQL Loader Number Format

Aug 25, 2011

I have a small problem when I am trying to load data into a table using SQL Loader. The data I am trying to load should be a number, but it is in the format '999,999,999 USD'. When I try to load the data, I am getting an invalid number error, due to the USD (I have already accounted for the thousands seperators). My question is, how can I load the data as a number with USD in the format?

Sample Data(testfile.dat):

sample1, "342,2343,543 USD"
sample2, "564,324,465 USD"
sample3, "534,753,213 USD"
[code]....

View 4 Replies View Related

Server Utilities :: SQL Loader TRIM In WHEN Clause?

Apr 9, 2012

My control file is :

LOAD DATA
APPEND
INTO TABLE IPGITLREDATA WHEN ITL_REC_TYPE = 'D'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(

[code].....

The data file might have a value of " D " instead of "D" for ITL_REC_TYPE and ITL_REC_TYPE is in the WHEN clause. How can I check for the trimmed value of ITL_REC_TYPE in the WHEN clause ?

View 5 Replies View Related

Server Utilities :: Count Of Data In Sql Loader?

Aug 3, 2012

I want to populate totale number of record in the file. Usually i get 10000 records per file and i load them using sql loader.I want to also insert the number of records in file while loading the data in table.

How can i achive it.

structure of control file is

load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table ERS_RIO_SRC
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR

[code]...

data

V5_RIO_5K7C|78967|172.16.43.153|RioLoginSrc.asp|0.375|01/08/2012 07:44:44.623|01/08/2012 07:44:45.000|V5_RIO_5K7C||||||||||
V5_RIO_5K7C|78968|172.16.43.150|RioLoginSrc.asp|0.187|01/08/2012 08:22:32.813|01/08/2012 08:22:33.000|V5_RIO_5K7C||||||||||

[code]...

table structure

CREATE TABLE ERS_RIO_SRC
(
INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,
CLINICAL_TRANSACTION_ID VARCHAR2(255 BYTE) NULL,

[code]...

View 4 Replies View Related

Server Utilities :: Tuning SQL Loader Process

Jun 17, 2010

I have loaded 14324590 rows into target tables using sql*loader.I used below consideration during the load process.

1) direct=true,parllel=true
2) unrecoverable
3) disable all indexes and triggers.

But, sql loader takes 21 minutes to load 14324590 rows in database? tuning sql loader process? we cannot change data file because it has given by client.

View 2 Replies View Related

Server Utilities :: Table Creation - SQL Loader?

Aug 2, 2010

Check table creation script

CREATE TABLE "SCOTT"."TEST_USER"
("TX_SID" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"TX_FIRST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"TX_LAST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,

[code]...

Here i'm loading data into these three tables through sql loader. Here is the control file

OPTIONS (SKIP=1,ROWS=5)
LOAD DATA
INFILE 'C:SQL LOADER DEMO estuser_data_lat.csv'
INTO TABLE TEST_USER

[code]...

Here are the two function which i'm calling from sql loader control file

CREATE OR REPLACE FUNCTION get_role_id(p_role_name VARCHAR2)
RETURN NUMBER IS
lv_role_id NUMBER;
BEGIN

[code]..

i'hv attached the testuser_data_lat.csv file, which is the data file.Command line

C:SQL LOADER DEMO>SQLLDR scott/sc CONTROL=rd_users_control.ctl

Now let me tell u what is happening Whem i'm running the above sqlldr, log is generating saying

Record 1: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")
Record 2: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

[code]...

But when i remove

INTO TABLE TEST_ROLE(
TX_SID POSITION(1:3) CHAR,
ID_ROLE "get_role_id(:ROLE_NAME)" ,
TX_CREATED_BY CONSTANT "SYSTEM",

[code]...

from Control file, data is getting popupalated in TEST_USER and TEST_TITLE similarly if remove

INTO TABLE TEST_TITLE(
TX_SID POSITION(1:3) CHAR,
ID_TITLE "get_title_id(:TITLE_NAME)" ,

[code]...

from Control file, TEST_USER and TEST_ROLE is getting populated.

Here RD_ROLE_MASTER script

CREATE TABLE RD_ROLE_MASTER (
"ID_ROLE" NUMBER(38,0) NOT NULL ENABLE,
"TX_ROLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE

[code]...

Here is RD_TITLE_MASTER script
CREATE TABLE RD_TITLE_MASTER(
"ID_TITLE" NUMBER(38,0) NOT NULL ENABLE,
"TX_TITLE_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE);

Insert into RD_TITLE_MASTER (ID_TITLE,TX_TITLE_NAME) values (7,'RED_LOB_ESCALATION_L1'); what is the problem?

View 1 Replies View Related

Server Utilities :: Loading Data Using SQL Loader

Oct 15, 2010

I have a problem with loading data using SQL loader.I have such control file

LOAD DATA
INFILE 'D:oracleFIRMAS.txt'
INSERT INTO TABLE FIRMAS
FIELDS TERMINATED BY ","
(FIRMAS_REG_NR char,FIRMAS_NOS char,ID_PRODUCT char)

and data file "firmas.txt"
444557562, Avotoni SIA, 1332
445575627, Avotoni SIA, 1332
444557562, Avotoni SIA, 1332

When I try to execut this command
sqlldr userid=system/a1331 control=d:oraclefirmas.ctrl an error occured (sql*loader-350) what should I do?

View 16 Replies View Related

Server Utilities :: Load To A Table Through Sql*loader

Apr 6, 2010

I Have one csv file.i want to load to a table trough sql*loader.but in table 3 column is there.but in the csv file some record hav one semicolumn in last filed like this

1234;"hogit";78887;89
4567;"rtef";12565;89

how can we load...

View 6 Replies View Related

Server Utilities :: Trunc Function In SQL Loader

Aug 6, 2012

I am using trunc(sysdate) to load only date in a column and it is coming out with error.

SQL*Loader-350: Syntax error at line 44.
Expecting valid column specification, "," or ")", found "trunc".
DATE_LOADED trunc(SYSDATE)

View 2 Replies View Related

Server Utilities :: Call Sequence In Sql Loader?

May 18, 2012

I have to load data from sql loader in a table and the table has got a varchar2 column which accept data from sequence.the expression would be.

TRANSACTION_ID "'AUTO-'||To_Char(prt_trs_id_seq.nextval)"

TRANSACTION_ID is the column name.

When i add this expression the loader doesn't work, when i remove it works and load null for this column and valid data with other column.

View 2 Replies View Related

Server Utilities :: Checking Column Value In SQL Loader?

Jul 12, 2011

My requirement is to load the data from feed file into two tables based on the value of a column in feed file.

say column in feed file is "Activity_Value" . If the Activity_value is 10, the data from feed file should be loaded into Table A and If the Activity Value is 20 the data from feed file should be loaded into Table B.

View 3 Replies View Related

Server Utilities :: SQL Loader Error Message?

Oct 6, 2000

When I try to load a .TXT file into an Oracle table, the following message is given at the command prompt;

SQL*Loader-524: partial record found at end of datafile

and the load is not successful. The control file is as follows;

Load Data
INFILE 'c:spledlsubj.txt'
APPEND
INTO TABLE tblSubjectiveCode

[code]...

Could the .TXT file causing any problems ?

View 7 Replies View Related

Server Utilities :: SQL Loader REG - Number Values?

Jan 2, 2011

we have to load some serious amount of data into an oracle database. We have extracted data out of another database and the number format for negative values is e.g. 50.00- / Positive values doesn't have a plus in e.g. its 50.00.

Is there a way to format the 50.00- to like -50.00 during the load with SQL loader? I was thinking of a regular expression?

View 8 Replies View Related

Server Utilities :: Import Performance Using Sql Loader

Apr 6, 2010

I have a text file of 175G with the delimeter as '|*'. I want to import it using sql *loader. I know that sql *loader can load data parallely. I can employ two schemes for importing it using sql *loader

a) with the text file being imported all at once using option parallel = true
b) with the single text file being broken down into files of smaller size(say 5G each or more) and then imported in parallel using parallel = true.

I can import it in somewhat powerful server with 128G of ram, 16 intel CPus and abundant hard disk space.Now my question is which option would be better in performance (in terms of time) it takes to import data?I donot have sufficient time to test both of these approaches.

View 5 Replies View Related

Server Utilities :: Sql Loader Value Needed From Another Table?

Aug 10, 2011

I have a file having 10k of rows and I need to use *sql loader to insert the data into table. Below are the information.

SQL> desc EMPLOYEE
Name Type
EMP_ID NUMBER(10) -- PrimaryKey
EMP_NAME VARCHAR2(30)
DEPT_ID NUMBER(10) -- ForeignKey from DEPARTMENT

SQL> desc DEPARTMENT
Name Type
DEPT_ID NUMBER(10)
DEPT_NAME VARCHAR2(30)

myFile.txt
------------
1,Edward,Account
2,Andrew,Finance
3,Sam, IT

CONTROL FILE (SQLLOADER)
------------
load data
infile myFile.txt
append into table EMPLOYEE
FIELDS TERMINATED BY ','
(EMP_ID,
EMP_NAME,
DEPT_ID ) <--- ?? What should do in here

what should i do in this line because the value that i want is DEPT_ID but the file is giving the DEPART_NAME. If there any sql statement can be used in control file?

View 6 Replies View Related

Server Utilities :: SQL Loader Not Creating Bad File?

Nov 11, 2011

My sql loader is returning a code 2 but not creating a bad file for the rejected records. also logs look good.

View 14 Replies View Related

Server Utilities :: Sql Loader Error Records

Jul 12, 2012

My oracle is sitting on UNIX, i have a sql loader scripts which load the data in oracle at every 10 min and bad files is written into a directory. since the file names are same it overwrite the badfiles in case of error record. i can devise a code to write the bad file with different name. I want to write error record into oracle table, is this possible and how can i achieve ?

View 5 Replies View Related

Server Utilities :: SQL Loader And Control File

Mar 19, 2010

I'm having a problem with SQL loader and the control file. I want to load a delimited file. The script will eventually be automated where the file name is passed in to the script, it's not a static name.

It's a simple SQL loader Unix script that I have created as follows

Unix file called test_load

# Auto Load
#
#
export data_file=/dev/test_$1$2.csv;
export ORACLE_HOME=/u01/oracle/product/10.2.0;
export ORACLE_SID=XXX;
export PATH=$PATH:$ORACLE_HOME/bin;

sqlldr userid=XXX/XXX data=$data_file
control=/dev/cntrl/test.ctl
errors=99999
bad=/dev/bad/test_$1$2_$$.bad
log=/dev/logs/test_$1$2_$$.log

the top of my control file is as follows

load data
truncate
into table test
fields terminated by "|"
when record_type = 'AA'
(

running at the prompt ./test_load myload 20100319

The following error occurs

SQL*Loader-350: Syntax error at line 5.
Expecting "(", found keyword when.
when record_type = 'AA'
^

I believe the format of my control file is correct but for some reason it won't load.

View 10 Replies View Related

Server Utilities :: Terminators And Datafile Of Sql Loader?

Jan 28, 2011

below query:

1) What are the terminators we can use and cant be used, except ','. eg - '|' - this shows error.

2) What are the file format can be used in DATAFILE other than txt file. Eg - excel, word... And how to terminate the values in that files...

View 3 Replies View Related

Server Utilities :: SQL Loader Lowercase Insert

Jul 17, 2011

Using Oracel SQL*Loader, is it possible to specify insert type statements? I need to know if i can pass a lower type call to a column. Specifically, if one of the entries is: username=MyUserName@gmail.com ... Then I need it to do the following lower(username) record inserts as myusername@gmail.com

I'm a bit stuck at the moment. I've googled and found LCase (), but I don't know if I'm barking up the wrong tree with that function.

View 3 Replies View Related







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