Server Utilities :: Trapping SQL Loader Summary Counts
Jun 30, 2011
I have got another curly one with regard to Sql Loader. My original issue via [message #513696] was resolved. In summary, SQL Loader opens a comma delimited csv file and it is able to successfully populate the appropriate Oracle table. However, during the run to process each unique csv file, SQL Loader produces a both a .log file (indicating what transpired during the load) and a .bad file of the records from the csv file that where rejected by SQL Loader.
the users have requested that "is it possible to update a separate table of the summary results of what went wrong and what was right?" It just so happens that the csv file contains some summary records (rows) with processing audit infor...as the following .bad file wrote out....
___________________________
ModuleID,ModuleSN,Lat,Lon,GMT Time,Tag Count,GMT Date,Client,Farm,Field,Variety,Machine
No Module ID ,No SN ,-30.178958,149.63844,9:55:58,0,21/04/2011,101,Widgen ,9,71BRF ,5987
3500B9880611170260179BFB,10202094587,-30.199379,149.661333,7:10:35,2,22/04/2011,101,Widgen ,30,71BRF ,5987
,,,,,,,,,,,
Total Modules,1602,,,,,,,,,,
Mis-reads,1,,,,,,,,,,
Mis-read %,0.06,,,,,,,,,,
1. The first record (starting in the 1st postion is "ModuleID") is a heading record in the csv file and can be disregarded.
2. The secord record (starting in the 1st postion is "No Module ID") is an error record in the csv file and can be disregarded.
3. The third record (starting in the 1st postion 3500B9880611170260179BFB, ) is a valid record, however, is was rejected as it is a duplicate of a preceeding record ...and the log indicates ...
Record 124: Rejected - Error on table PTLIVE.MODULE_CSV_LOADS.
ORA-00001: unique constraint (PTLIVE.MODULE_SERIAL_NUMBER_UK) violated
4. The fourth record (starting in the 1st postion is ,,,,,,,,,,,
) is a blank record in the csv file and can be disregarded.
5. The fifth record (starting in the 1st postion is "Total Modules" has a value of 1602 which I need to capture to write into a separate table.
6. The sixth record (starting in the 1st postion is "Mis-reads" ) has a value of 1 which I need to capture to write into a separate table.
7. The last record in the csv file and can be disregarded.
The summary Oracle table is
CREATE TABLE PTLIVE.SUBMODULES_SUMMARY
( IMPORT_CSV_FILE_NAME VARCHAR2(256 ),
TOTAL_SUBMODULES_LOADED NUMBER (5),
TOTAL_SUBMODULES_MIS_READ NUMVER (5),
DATEIMPORTED DATE default SYSDATE
)
Now I would like to trap the value 1602 and load that into the Oracle column TOTAL_SUBMODULES_LOADED and the value 1 and load this into TOTAL_SUBMODULES_MIS_READ.
The name of the csv file is known and I can (as the previous [message #513696] resolved) simply use a SQL Loader control file command > IMPORT_ CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv".
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.
- 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....
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".
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?
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"
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
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)
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?
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 ?
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
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.
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?
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
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.
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?
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.
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?
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 ?
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
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.
