SQL & PL/SQL :: Load CSV File Into External Table / ORA-01722 / Invalid Number
Apr 26, 2011
I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.
The log file has the following errors:
KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/tmpclie.csv
error processing column EMPNO in row 2 for datafile /usr/tmpclie.csv
ORA-01722: invalid number
This is the script for the table:
create table emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
[code]....
And this is csv:
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
[code]....
View 37 Replies
ADVERTISEMENT
Mar 26, 2012
my table having doj 30-jan-2003
SQL> select to_char(date_of_joining,'yyyy') from t1;
select to_char(date_of_joining,'yyyy') from t1
*
ERROR at line 1:
ORA-01722: invalid number
View 1 Replies
View Related
Jan 23, 2013
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
View 5 Replies
View Related
Mar 9, 2007
INSERT INTO t_category m(
m.service_id,
m.customer_id)
SELECT
u.service_id,
(SELECT p.add_data
FROM t_add p
WHERE
p.service_id=u.service_id AND p.add_type='CUSTOMER_ID')
FROM t_iservice u
I got this error: ORA-01722: invalid number. The problem is m.customer_id has data type NUMBER, p.add_data is VARCHAR.
View 1 Replies
View Related
Jul 26, 2011
I have a problem when executing the statement below querying a specific ID# in view
select * from VW_MML_LTR_MSTR where LTR_ID = 26 order by CLNT_CDE ,LTR_GRP, ltr_purp_id, LTR_CDE
I checked the data type acquired by the view and the table involved and confirmed that it was of number type.Weird thing for me is that I changed the where statement as
where LTR_ID LIKE 26
where LTR_ID != 25
Is this on the database settings? What should be done here?
View 9 Replies
View Related
Jul 6, 2012
Am getting invalid number error for the query ..
select ROUND( ( SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', 2, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,1)- 2)
+ SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,3) - (instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1))
)/ 2) AS x from dual;
View 3 Replies
View Related
May 18, 2011
Enviroment is Embarcadero RAD Studio XE. Work with DataSnap WebBroker Application. Its Server methods ancestor is TDSServerModule.
I've got SQLConnection (dbx), SQLDataSet, DataSetProvider on server side and SQLConnection, DSProviderConnection, ClientDataSet plus DataSource, DBGrid, DBNavigator on client side.
SQLConnection on server side uses Oracle driver - dbxora.dll. DB: Oracle 11g.
SQLConnection on client side uses Datasnap driver.
SQLDataSet has DbxCommandType set to Dbx.SQL, CommandText: "Select * from Table1".
All fields except indexed one have their pfInWhere set to false.
updateMode of DataSetProvaider is set to upWhereKeyOnly.
All is well up to point where ApplayUpdates is fired. It does nothing. HandleReconcileError shows ORA-01722: invalid number.
Oracle explains: ORA-01722:invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation. But all updated fields are of character strings type. There is no need for conversion. I suppose the automatically created SQL has some extra checking. But I cannot see those SQLs. I guess must be a way of controlling those SQLs thru params, but don't know which. May be comparison old value - new value of index column is depending on this conversion?
View 14 Replies
View Related
Sep 27, 2010
We are using hand scanning machine for attendance, machine saved data in a TEXT file now I want to load data into my oracle base payroll system.
data saved in this format.
31201009240928000100000002690001
31201009240933000100000000060001
as per my understanding
20100924 is date
0928 is time
269 is employee code
but I am unable to understand, is this IN or OUT time?
View 3 Replies
View Related
Sep 5, 2010
I am trying to load my table using a external table. I have data in .DAT file and I have created a directory and its path and everything is complied and created properly. But problem is, when I say "insert into my table select from external table". It's inserting all null values into my table. Instead of inserting data from my .DAT file its inserting all null values,
View 10 Replies
View Related
Oct 9, 2013
My db version: Oracle 11g I have an empty csv file.I created a external table for the empty csv file.When I run:select count(*) from externaltblname;It returns 1. It should return 0 right. In the definition, I specified "SKIP 1"But still it returns 1. When I use this external table to load into a target table. It loads a single row with null values.How to fix this.
View 2 Replies
View Related
Jan 24, 2013
I need to load csv file using an external table.
Structure of External Table:
---------------------------
create table A (col1 varchar2(30), col3 varchar2(30), col5 varchar2(30));
CSV FILE:
-----------
col1,col2,col3,col4,col5
A,B,C,D,E
1,2,3,4,5
The table data should look like
COL1 COL3 COL5
A C E
1 3 5
need to skip the columns in CSV file.
View 5 Replies
View Related
Apr 7, 2011
I have question on the following, that gets defined for the bad file and the log file
BADFILE 'bad_%a_%p.bad'
LOGFILE 'log_%a_%p.log'
What does the %a and % p indicate? Also if I wanted to get the value of %p and %a into a variable how would I do it? I want to be able to append %p and %a to the below variable, but unsure how to achieve it..
l_badfile := file_nm || '.bad' ;
View 3 Replies
View Related
May 26, 2010
Below is the external table definition and the content of csv file, why the first record fails ?
0546-0*LB-CRP*16*"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --FAILED
0546-0*LB-CRP*16*ID"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
0546-0*LB-CRP*16*"Tech, ZAO"*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
[code]....
View 8 Replies
View Related
Jun 13, 2013
I've been working on Oracle for many years but fot the first time I was asked to load a XML file into a table.As an example, I've found this on the web, but it doesn't work. the file acct.xml is this:
<?xml version="1.0"?><ACCOUNT_HEADER_ACK><HEADER><STATUS_CODE>100</STATUS_CODE><STATUS_REMARKS>check</STATUS_REMARKS></HEADER><DETAILS><DETAIL><SEGMENT_NUMBER>2</SEGMENT_NUMBER><REMARKS>rp polytechnic</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>3</SEGMENT_NUMBER><REMARKS>rp polytechnic administration</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>4</SEGMENT_NUMBER><REMARKS>rp polytechnic finance</REMARKS>[code]......
For the two tags HEADER and DETAILS I have the table:
create table xxrp_acct_details(status_code number,status_remarks varchar2(100),segment_number number,remarks varchar2(100));
before I've created a create directory test_dir as 'c:esterno'; --
where I have my acct.xml and after, can you give me a script for loading data by using XMLTABLE? I've tried this but it doesn't work:
DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code, x1.status_remarks, x2.segment_number, x2.remarks from xmltable( '/ACCOUNT_HEADER_ACK/HEADER' [code]......
This should allow me to get something like this: select * from xxrp_acct_details; Statuscode status remarks segement remarks --------------- -------------------- ------------- ------------ 100 check 2 rp polytechnic100 check 3 rp polytechnic administration100 check 4 rp polytechnic finance100 check 5 rp polytechnic logistics500 process exception 20 base polytechnic500 process exception 30500 process exception 40 base polytechnic finance500 process exception 50 base polytechnic logistics but I get:
Error report:ORA-06550: line 19, column 11:PL/SQL: ORA-00932: inconsistent datatypes: expected - got NUMBERORA-06550: line 4, column 2:PL/SQL: SQL Statement ignored06550. 00000 - "line %s, column %s:
%s"*Cause: Usually a PL/SQL compilation error. and if I try to change the script without using the column HEADER_NO o keep track of the header rank inside the document:
DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code, x1.status_remarks, x2.segment_number, x2.remarks from xmltable( '/ACCOUNT_HEADER_ACK/HEADER' [code]......
I get this message: Error report:ORA-19114: error during parsing the XQuery expression: ORA-06550: line 1, column 13:PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignoredORA-06512: at line 419114. 00000 - "error during parsing the XQuery expression: %s"*Cause:
An error occurred during the parsing of the XQuery expression.*Action: Check the detailed error message for the possible causes. My oracle version is 10gR2 Express Edition
View 3 Replies
View Related
Aug 6, 2012
how can I load data into a table from *.ldr* file? How exactly I can use such files to run in loader?
View 16 Replies
View Related
Nov 20, 2012
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I am new in external table so i have tried following cmd.
create directory dir_1 as 'E:ora_dirt' ;
grant read, write on directory dir_1 to HR;
select * from all_directories;
create table emp_ext
(emp_id number,
emp_name varchar2(30)
[code]...
since I am not able to see DIR_1 in E: drive due to which i havnt created 'emp.dat' file and on executing select on external table i m geting expected error *"ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04043: table column not found in external source: EMP_ID"*
how to create that file in directory "DIR_1" .
View 2 Replies
View Related
Jul 4, 2013
I'm trying to load xml file into table having xmltype datatype, but it is throwing below given error.I even tried to load data by changing '&' into '&' but still getting same error.
Error at line 6
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.
Version : ORACLE 11g, Windows 7
CREATE TABLE xml_test
(
id NUMBER(5),
NAME VARCHAR2(50),
xmldata xmltype
);
INSERT INTO xml_test(id, name, xmldata) VALUES(1,'file1', XMLTYPE(bfilename('SCOTTDIR', 'TEST_XML.XML'), nls_charset_id('AL32UTF8')));
TEST_XML.XML file content:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- excelFolder:"c:CSD'TEST.XSL" -->
<File1>
<Version>1.6.1</Version>
<FileSource>Java&Conversion 1.2</FileSource>
<FileDateTime>2013-06-13T15:55:54</FileDateTime>
<Filecreatedby>Lo&ki</Filecreatedby>
<FileLocation>Bangalore</FileLocation>
</File1>
insert xmlfile having '&' data into xmltype column.
View 4 Replies
View Related
Mar 16, 2010
I am new to oracle designer, forms. The requirement is to select a csv file in a form ,read the file and load selected columns from a csv file into a table.
I am using CLIENT_TEXT_IO. I want to know how to extract the data from selected columns from csv file and insert into a table if the lenth of the columns are of variable length.
Another condition is that if there are duplicate rows based on orderid then take the maximum order seq nbr.Do I need to use
temp table for this logic?
View 1 Replies
View Related
Jun 25, 2013
i have a problem in my ODI 11g with the load into Oralce table of a fixed width file, i configured all the datasource in ODI and when i do view data i see all correct, the end of file is signed like "0D0A" but when i try my load interface i receive the message that my last field is more big than the one declared.
My file have an header of fields and the last field is a data-field of 2000 characters. I controlled and is really fixed the length cause is a COBOL file from a Mainframe. So it looks that ODI don't understand the end of that field and go ahead to the other, i just tryed to enlarge the limit but is always more big like if the file is shifting on the right.
Have i forgot some configuration in some place? The definition of the file present the end of file like Microsoft hexadecimal \u000D\u000A i try all the combination there but no way to avoid this problem.
View 1 Replies
View Related
Jul 24, 2013
I'm trying to load xml file into table using dbms_xslprocessor.read2clob package, it loads for small file, but it throws READ ERROR for big file. I have given READ, WRITE permission on directory and also DBA role to user. Also I have attached file (change file extension to xml)
check below given code.
CREATE TABLE loadxmlfile (xmldata CLOB);
CREATE TABLE loadxml (id NUMBER, xmldata XMLTYPE);
create or replace PROCEDURE load_xmlfile
[Code]...
Note: Reason I'm doing is to remove NON BREAKING SPACE (NBS) character from xml file
View 6 Replies
View Related
Dec 29, 2010
send me the procedure for loading the data in an oracle table into an excel file.
View 5 Replies
View Related
Oct 26, 2012
I am having query regarding sql loader. my data file is comm(,) seperated and I want to load the whole file in oracle table 'bill_temp' except 1st column data of data file.
e.g.
File name: bill_file.dat
fields seperated by comma ','
values are like
emp_id,emp_name,emp_sal,join_date
oracle table bill_temp having the below column:
emp_name,emp_sal,join_date
Here I want load the emp_name,emp_sal and join_date into oracle table bill_temp.
emp_id should not get loaded into table.
Is there any way to skip the loading of particular column data from data file into table?
View 12 Replies
View Related
Jul 17, 2010
My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.
View 6 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
Aug 27, 2012
I want to read the csv file and load into oracle table.But I am getting file with filename_<today date> for every day. Is it possible to use single External table to read file in dynamic.
or what is the best way to do this? My oracle version 10g in windows OS.
View 3 Replies
View Related
Aug 16, 2011
I have a table revenue
create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)
and i have data in a file like below
Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
i want to load it into the table in the following way.
Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.
View 2 Replies
View Related
Jun 29, 2011
I need to load (using SQL Loader) an huge XML file, with several hundreds of records into an Oracle Table.The XML file schema is pretty simple, and it's anything like this:
<dataroot>
<record>
<companyname>LimitSoft S.A.</companyname>
<address>Street Number 1</address>
[code]...
I'm trying to use the help included in this link [URL]...
When they refer to schema[URL].... what should I use?? I do not need to use the Oracle website to register anything, right?
View 4 Replies
View Related
May 31, 2010
what i miss to load date and time from text file to oracle table through sqlloader
this is my data in this path (c:externalmy_data.txt)
7369,SMITH,17-NOV-81,09:14:04,CLERK,20
7499,ALLEN,01-MAY-81,17:06:08,SALESMAN,30
7521,WARD,09-JUN-81,17:06:30,SALESMAN,30
7566,JONES,02-APR-81,09:24:10,MANAGER,20
7654,MARTIN,28-SEP-81,17:24:10,SALESMAN,30
my table in database emp2
create table emp2 (empno number,
ename varchar2(20),
hiredate date,
etime date,
ejob varchar2(20),
deptno number);
the control file code in this path (c:externalctrl.ctl)
load data
infile 'C:externalmy_data.txt'
into table emp2
fields terminated by ','
(empno, ename, hiredate, etime, ejob, deptno)
this is the error :
C:>sqlldr scott/tiger control=C:externalctrl.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 09:45:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 5
C:>
View 21 Replies
View Related
Sep 20, 2013
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
View 3 Replies
View Related
Dec 3, 2012
select to_char(to_date(mcih_date, 'dd-mm-yyyy')),
mcih_terms from I_MEMO_CONF_H;
TO_CHAR(TO_DATE(MCIH_DATE,'DD-MCIH_TERMS
118-OCT-1245
206-NOV-1222
[Code]...
why i am getting this error?
View 3 Replies
View Related
