Server Utilities :: Loading String Has Double Quotes In It?
Aug 22, 2013
I am loading .csv file into Oracle using sql loader file has strings, and numberics, Strings are surrounded by double quotes(") and field terminated by comma(,)
load data
BADFILE '/var/opt/app/bad/filename'
DISCARDFILE '/var/opt/app/discard/filename'
append into table source_file
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
Some time String fields may have double quotes in it, at that time it is rejecting the records. how to handle those records to load into table.
I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.
"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"
Expected: the one below is exactly 60bytes.
PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT LOAD DATA INFILE * INTO TABLE TMPTLI_LAWSON_ITM_MST TRUNCATE FIELDS TERMINATED BY "|" (ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD", AVL_CD , ITM_CST_AMT, ITM_SLL_AMT, EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)", PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD", ITM_CMNT "TRIM(:ITM_CMNT)")
Any way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!
select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE from TBL1, TBL2 where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456' order by Material
But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".
select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE from TBL1, TBL2 where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456' order by Material
Since there is an extra double quote (denoting inch) in the third column, im getting an error. Is there any way to avoid this error without modifying the csv file.
Here is a sample function which takes in a string of CSV fields and prints the third field :
create or replace function restr(istr varchar2) return INTEGER is var_1 varchar2(30); begin dbms_output.put_line('input:'||istr); select regexp_substr(',' || istr,1,3,null,1) into var_1 from dual;
[Code]..
When I pass an input string as :
JOHN,MARY,O'DONNEL,O'CONNELLY,MARK
with quotes/apostrophe in it to the function then it prints the input string in the first dbms_output but errors out at the select with ORA-01760.
how we can use the quote/apostrophe character here ?
I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')
My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"
How many quotes are supposed to go around val1 and val2?
I've tried the following and none work: 'val1','val2' ''val1','val2'' ''val1'',''val2'' '''val1'',''val2''' ''''val1'',''val2''''
When I run the procedure in Oracle it works with '''val1'',''val2'''
I have a file which i am loading , the last column is being loaded with special character which looks like sqare shape character for all the rows.How can i prevent it to load as it is not present in the file
data in file
Department|2|Tranport for London|Rail & Underground|ER|ER|ER|ER|ER|ER|555555555|Owner 8|8
the data once loaded for the last column on which is for above example last column value 8 will be loaded with 8 and square share character.
We completed creating a replicate of dB_01 to dB_02 (housed in a single DEV server). But dB_02 had only table structures (no records). What would be our fastest option (tools, commands, etc.) to load more than a thousand new records for each of the 20 tables of dB_02?
I have a small confusion in direct path loading. Will direct path load ever use memory (SGA)? If yes, why it is not generating redo? If no, can we write into a block at file system level directly.
I'm not sure if this is so much a SQL Loader problem as it is a database understanding problem, but here it is. I am having trouble loading data into a table (using SQL Loader) due to the fact that I am trying to load data row by row, into corresponding columns.
TestFile.csv
testvalue1, 123445 testvalue2, test testvalue3, 455321 testvalue4, 65742 testvalue5, 5719
So, using the above data, I am trying to load the value for 'testvalue1' into a column defined as 'testvalue1'; the value for 'testvalue2' into a column defined as 'testvalue2' and so on. From my understanding, SQL loader loads by column not by row, so I am not even sure if this is possible.
I am trying to load multiple XML files into Oracle DB using SQL Loader. The filenames of the XML files starts with a description and then numbers, where the numbers are different each time.
Here's my CTL file:
LOAD DATA INFILE * INTO TABLE XML_TABLE TRUNCATE xmltype(XML_TABLE) FIELDS (
[code]....
I don't want to keep having to go into the ctl file and change the numbers of the xml file. Is there a way where I could just load all .xml files that begins with 'description'? Like maybe
How to load the CLOB data into table..in the attached file 18 column has clob data it's appear like new line..Using external table how to load. i tried it's not working..
I have the following table intra_trades with t_id as the primary key. There is a trigger on that table that gets the next sequence and inserts it into the t_id column for every insert. I need to load data into that table using SqlLoader as chunks of 3000 rows and return the t_id back the script that Sqlload the data so that it can use that t_id's for the next process in the script.
The problem is that the only unique key on that table is the t_id which has a sequence on it and it is the pk. There can be duplicate rows in that table to meet the business needs for the company. So it is hard to associate the rest of the data in a row with t_id. The only thing I can think of is return the t_ids in the order it inserted so if the script keeps the order of rows in the memory it can associate the tid with the rest of the intra_trades info.How can I make the sqlloader return an array of t_ids that inserted? I need to return the t_ids's in the order it inserted so that the script can associate the t_id with the rest of the rest of the data in a row.
SQL> desc stg_query_overflow Name Null? Type ----------------------------------------- -------- ---------------------------- HOSTNAME VARCHAR2(50) NPSID NUMBER NPSINSTANCEID NUMBER OPID NUMBER
[code]....
Here's my controlfile:
load data infile '/u01/tony/server_name/query_overflow.dat' badfile '/opt/oracle/tony/sql_dir/bad/server_name_query_overflow.bad' discardfile '/opt/oracle/tony/sql_dir/discard/server_name_query_overflow.dsc' append into table stg_query_overflow
[code]....
Here's a sample of data that I can't load into the table via sqlldr:
Record 272: Rejected - Error on table STG_QUERY_OVERFLOW, column NPSID. ORA-01722: invalid number Record 273: Rejected - Error on table STG_QUERY_OVERFLOW, column NPSID. ORA-01722: invalid number
As you can see, sqlldr is interpreting this vertical sql code as the npsid column, when in fact it is the querytext column. How can I insert each record when some of my data is in this vertical format?
I have a bunch of data in 50 excel files. I need to load all these 50 files into 50 different tables. I would like to do this in one script. I went through the forum to get this information, people suggested create a shell script etc or list the sqlldr command multiple times etc.
provide some clarity on this as to what's the best approach.If it is through shell scripting provide the shell script and instructions to execute it. Iam new to shell scripting.
I have to load a fixed width file using sql loader utility. But the records have multiple special characters. writing / modifying the loader utility to load the data.
--Script to create the table create table t1 ( ip1 varchar2(2), ip2 number, ip3 number);
--loader utility LOAD DATA INFILE 'c:inputfile.dat' BADFILE 'c:adfile.bad' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '°' ( ip1POSITION(1:2) CHAR, ip2POSITION(3:17) INTEGER EXTERNAL ":ip2/100", ip3POSITION(18:32) INTEGER EXTERNAL ":ip3/100", )
--sql version i am using SQL*Loader: Release 9.2.0.1.0 - Production on Wed Mar 7 18:32:33 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
In the above mentioned data file, records has multiple special characters like '°','¶' ,'-'. All these special characters have some meaning. eg: '°' specifies the above column needs to be multiplied by -1 '¶' specifies the above column needs to be multiplied by -0.1
what changes need to be made in loader utility for the same? Also, will there be any change in the utility if I am using higher version of oracle?
Sl#Emp_noNameAddress 00101Tom1/B-XYZ street 00202Jon1/C-XYZ Street
Employee Datafile 001, 01, Tom, 1/B-XYZ street 002,02,Jon, 1/C-XYZ Street
Above is a sample data file. Now I would like to import the data into an Oracle table called employee using Oracle 9i SQL Loader utility. But the table has only 3 fields (Emp_no,Name & Address), so I would like to skip Sl# while loading data. I do not want to manually modify data file. How should I write .ctl file.
Sample .ctl file.
load data INFILE 'dataEmployee' BADFILE 'Employee.bad' DISCARDFILE 'Employee.dis' into table Employee fields terminated by ',' TRAILING NULLCOLS (Emp_no NULLIF Emp_no = BLANKS, Name NULLIF Name = BLANKS, Address NULLIF Address = BLANKS )
After exporting some data to excel, I noticed that on one row all the columns shifted over some. So I queried this record in the database and noticed that the ADDRESS field has some unknown characters at the end of it. They are little squares. I think they are TABS.
2630 LINDEN BLVD, APT. #8G(2 squares are in here)
ADDRESS_1 "TRIM(:ADDRESS_1)",
Besides trimming the data, is there some other function I can use to clean up the address further?
We load large amount of data into multiple tables using sqlldr. Amount of data that we need to load varies according to the situation. We want to estimate the tablespace usage growth due to this data load, so we can verify/extend the tablespaces before the data load. Though, setting to autoextend will work in this case, We want to avoid extending the tablespace during sqlldr executing due to performance.
Our initial attempt was to note the tablespace size before and after executing the sqlldr and use the delta. But this delta was not consistent in different environments for the same amount of data. Different environments mean different oracle servers, different existing sizes of tablespaces, One data file Vs multiple data files etc.
How do we reliably estimate how much tablespace we need for the given amount of data?
While importing dump to the new database, error occurred. Below are the errors -
ORA-02374: conversion error loading table "INS"."GENMST_FINANCIER_BRANCH" ORA-12899: value too large for column TXT_IFSC_CODE (actual: 19, maximum: 15) ORA-02372: data for row: TXT_IFSC_CODE : 0X'4644524C30303031353739A0A0A0A0' [code]...
I would like to know, why such error occurred during the import.