Home » RDBMS Server » Server Utilities » decimal value in control file
decimal value in control file [message #634420] Tue, 10 March 2015 01:30 Go to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi ,

I want to insert data into the table , in the control file(.ctl file)

item_rate CHAR NULLIF item_rate = BLANKS "RTRIM(:item_rate )"

But in the item_rate value are like 1.234, 0.456 so is the above code is correct or not?

Thanks
Re: decimal value in control file [message #634430 is a reply to message #634420] Tue, 10 March 2015 02:05 Go to previous messageGo to next message
Littlefoot
Messages: 21482
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you test it? What was the result?
Re: decimal value in control file [message #634458 is a reply to message #634430] Tue, 10 March 2015 05:14 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Did you test it? What was the result?

Hi Littlefoot,
i want to insert data using concurrent program in Oracle Apps R12,so i have upload.csv file(C:\Users\test win1\Desktop\Vision\Upload.csv)

And my control file is in the server path(bin folder) and at the runtime it will asking Data File to pass parameter as above(C:\Users\test win1\Desktop\Vision\Upload.csv)

getting below error
Arguments
------------
C:\Users\test win1\Desktop\Vision\Upload.csv
------------
LRM-00112: multiple values not allowed for parameter 'data'

Re: decimal value in control file [message #634466 is a reply to message #634458] Tue, 10 March 2015 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the control file?
What is the table description?
What is the content of the data file?
What is the Oracle version?
When will you follow the forum guide?

[Updated on: Tue, 10 March 2015 06:33]

Report message to a moderator

Re: decimal value in control file [message #634480 is a reply to message #634458] Tue, 10 March 2015 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 26544
Registered: January 2009
Location: SoCal
Senior Member
mist598 wrote on Tue, 10 March 2015 03:14
Quote:
Did you test it? What was the result?

Hi Littlefoot,
i want to insert data using concurrent program in Oracle Apps R12,so i have upload.csv file(C:\Users\test win1\Desktop\Vision\Upload.csv)

And my control file is in the server path(bin folder) and at the runtime it will asking Data File to pass parameter as above(C:\Users\test win1\Desktop\Vision\Upload.csv)

getting below error
Arguments
------------
C:\Users\test win1\Desktop\Vision\Upload.csv
------------
LRM-00112: multiple values not allowed for parameter 'data'



The error results from the space between "test win1" in line above.
Re: decimal value in control file [message #634525 is a reply to message #634466] Wed, 11 March 2015 03:24 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,
Quote:

What is the control file?

Options(errors=10000,skip=3)
LOAD DATA  
REPLACE  
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'    
trailing nullcols
(
 customer_name  CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
 customer_no  CHAR NULLIF customer_number = BLANKS "RTRIM(:customer_number)",
 item_no  CHAR NULLIF item_number = BLANKS "RTRIM(:item_number)",
valid_amount_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_amount_month1 = BLANKS "to_number(:valid_amount_month1 , '999,999,999.99')"  ,
valid_unit_month1     CHAR TERMINATED BY WHITESPACE NULLIF valid_unit_month1 = BLANKS "to_number(substr(:valid_unit_month1,instr(:valid_unit_month1,'$')+1),'999,999,999.99')",
valid_rate    CHAR NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)

Record 12: Rejected - Error on table SAMPL_TABLE, column VALID_AMOUNT_MONTH1.
ORA-01722: invalid number

Quote:

What is the table description?

  ITEM_NO                  VARCHAR2(240 BYTE),
  ITEM_DESCRIPTION         VARCHAR2(240 BYTE),
  CUSTOMER_NAME            VARCHAR2(240 BYTE),
  CUSTOMER_NO          VARCHAR2(240 BYTE),
  VALID_AMOUNT_MONTH1     NUMBER,
  VALID_UNITS_MONTH1      NUMBER,
  VALID_BUDGET_AMOUNT_MONTH1     NUMBER,
  VALID_BUDGET_UNITS_MONTH1      NUMBER,
  VALID_RATE              NUMBER

Quote:

What is the content of the data file?

Category     CustName    Cust #	    Item No  Amount - Month 1  Units - Month 1  Budget - Month 1  Budget -Unit- Month 1 attribute1	 
------------  ------     -------   --------  ---------------   ---------------- ---------------  ---------------------  --------------  
abc           Rynu       abc123       D                                            34             1234                    

What is the Oracle version?
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Re: decimal value in control file [message #634526 is a reply to message #634525] Wed, 11 March 2015 03:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3146
Registered: May 2013
Location: World Wide on the Web
Senior Member
Blackswan already pointed out,

C:\Users\test win1\Desktop\Vision\Upload.csv


You have a space between test and win1. Thus it is interpreted as two arguments.
Re: decimal value in control file [message #634527 is a reply to message #634525] Wed, 11 March 2015 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your data file is not a csv file as you said... or you didn't post your data file.

Re: decimal value in control file [message #634528 is a reply to message #634526] Wed, 11 March 2015 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lalit Kumar B wrote on Wed, 11 March 2015 09:50
Blackswan already pointed out,

C:\Users\test win1\Desktop\Vision\Upload.csv


You have a space between test and win1. Thus it is interpreted as two arguments.


This is not the lone problem, OP has now fixed it, otherwise he would not have the error
Quote:
Record 12: Rejected - Error on table SAMPL_TABLE, column VALID_AMOUNT_MONTH1.
ORA-01722: invalid number


Re: decimal value in control file [message #634533 is a reply to message #634527] Wed, 11 March 2015 04:24 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Michel ,
  create table SAMPL_TABLE
  (
  ITEM_CATEGORY         VARCHAR2(30 BYTE),
  ITEM_NO                  VARCHAR2(240 BYTE),
  ITEM_DESCRIPTION         VARCHAR2(240 BYTE),
  CUSTOMER_NAME            VARCHAR2(240 BYTE),
  CUSTOMER_NO          VARCHAR2(240 BYTE),
  VALID_AMOUNT_MONTH1     NUMBER,
  VALID_UNITS_MONTH1      NUMBER,
  VALID_BUDGET_AMOUNT_MONTH1     NUMBER,
  VALID_BUDGET_UNITS_MONTH1      NUMBER,
  VALID_RATE              NUMBER
);


Please check the below attached File

Thanks
  • Attachment: Sheet1.csv
    (Size: 0.58KB, Downloaded 125 times)
Re: decimal value in control file [message #634535 is a reply to message #634533] Wed, 11 March 2015 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Using your table and control file I get:
SQL> create table SAMPL_TABLE
  2    (
  3    ITEM_CATEGORY         VARCHAR2(30 BYTE),
  4    ITEM_NO                  VARCHAR2(240 BYTE),
  5    ITEM_DESCRIPTION         VARCHAR2(240 BYTE),
  6    CUSTOMER_NAME            VARCHAR2(240 BYTE),
  7    CUSTOMER_NO          VARCHAR2(240 BYTE),
  8    VALID_AMOUNT_MONTH1     NUMBER,
  9    VALID_UNITS_MONTH1      NUMBER,
 10    VALID_BUDGET_AMOUNT_MONTH1     NUMBER,
 11    VALID_BUDGET_UNITS_MONTH1      NUMBER,
 12    VALID_RATE              NUMBER
 13  );

Table created.

SQL>
SQL> host sqlldr michel/michel control=E:\t4.ctl data=E:\t4.txt

SQL*Loader: Release 11.2.0.1.0 - Production on Mer. Mars 11 10:33:45 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-403: Referenced column CUSTOMER_NUMBER not present in table SAMPL_TABLE.

Re: decimal value in control file [message #634537 is a reply to message #634535] Wed, 11 March 2015 04:43 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Sorry Michel that is printing mistake, now check it again
Options(errors=10000,skip=3)
LOAD DATA  
REPLACE  
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'    
trailing nullcols
(
 customer_name  CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
 customer_no  CHAR NULLIF customer_number = BLANKS "RTRIM(:customer_no)",
 item_no  CHAR NULLIF item_number = BLANKS "RTRIM(:item_no )",
valid_amount_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_amount_month1 = BLANKS "to_number(:valid_amount_month1 , '999,999,999.99')"  ,
valid_unit_month1     CHAR TERMINATED BY WHITESPACE NULLIF valid_unit_month1 = BLANKS "to_number(substr(:valid_unit_month1,instr(:valid_unit_month1,'$')+1),'999,999,999.99')",
valid_rate    CHAR NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)
Re: decimal value in control file [message #634539 is a reply to message #634537] Wed, 11 March 2015 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> host sqlldr michel/michel control=E:\t4.ctl data=E:\t4.txt

SQL*Loader: Release 11.2.0.1.0 - Production on Mer. Mars 11 10:47:21 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-403: Referenced column CUSTOMER_NUMBER not present in table SAMPL_TABLE.

There is still a CUSTOMER_NUMBER in your file.
Fixing it I get:
SQL> host sqlldr michel/michel control=E:\t4.ctl data=E:\t4.txt

SQL*Loader: Release 11.2.0.1.0 - Production on Mer. Mars 11 10:48:18 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-403: Referenced column ITEM_NUMBER not present in table SAMPL_TABLE.

Please TEST your code before you post it.
Re: decimal value in control file [message #634542 is a reply to message #634539] Wed, 11 March 2015 04:56 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Michel please check it now and added new 2 columns to the Table
  create table SAMPL_TABLE
  (
  ITEM_CATEGORY         VARCHAR2(30 BYTE),
  ITEM_NO                  VARCHAR2(240 BYTE),
  ITEM_DESCRIPTION         VARCHAR2(240 BYTE),
  CUSTOMER_NAME            VARCHAR2(240 BYTE),
  CUSTOMER_NO          VARCHAR2(240 BYTE),
  VALID_AMOUNT_MONTH1     NUMBER,
  VALID_UNITS_MONTH1      NUMBER,
  VALID_BUDGET_AMOUNT_MONTH1     NUMBER,
  VALID_BUDGET_UNITS_MONTH1      NUMBER,
  ATTRIBUTE1       VARCHAR2(240 BYTE),
  ATTRIBUTE2       VARCHAR2(240 BYTE),
    VALID_RATE              NUMBER
);

CTL File
--------

Options(errors=10000,skip=3)
LOAD DATA  
REPLACE  
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'    
trailing nullcols
(
 customer_name  CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
 customer_no  CHAR NULLIF customer_number = BLANKS "RTRIM(:customer_no)",
 item_no  CHAR NULLIF item_number = BLANKS "RTRIM(:item_no )",
valid_amount_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_amount_month1 = BLANKS "to_number(:valid_amount_month1 , '999,999,999.99')"  ,
valid_unit_month1     CHAR TERMINATED BY WHITESPACE NULLIF valid_unit_month1 = BLANKS "to_number(substr(:valid_unit_month1,instr(:valid_unit_month1,'$')+1),'999,999,999.99')",
valid_budget_units_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_budget_units_month1 = BLANKS "to_number(:valid_budget_units_month1, '999,999,999.99')"   					       		,
valid_budget_amount_month1  CHAR TERMINATED BY WHITESPACE NULLIF valid_budget_amount_month1 = BLANKS "to_number(trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1)),'999,999,999.99')"	,
valid_rate    CHAR NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)
Re: decimal value in control file [message #634547 is a reply to message #634542] Wed, 11 March 2015 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> create table SAMPL_TABLE
  2    (
  3    ITEM_CATEGORY         VARCHAR2(30 BYTE),
  4    ITEM_NO                  VARCHAR2(240 BYTE),
  5    ITEM_DESCRIPTION         VARCHAR2(240 BYTE),
  6    CUSTOMER_NAME            VARCHAR2(240 BYTE),
  7    CUSTOMER_NO          VARCHAR2(240 BYTE),
  8    VALID_AMOUNT_MONTH1     NUMBER,
  9    VALID_UNITS_MONTH1      NUMBER,
 10    VALID_BUDGET_AMOUNT_MONTH1     NUMBER,
 11    VALID_BUDGET_UNITS_MONTH1      NUMBER,
 12    ATTRIBUTE1       VARCHAR2(240 BYTE),
 13    ATTRIBUTE2       VARCHAR2(240 BYTE),
 14      VALID_RATE              NUMBER
 15  );

Table created.

SQL> host sqlldr michel/michel control=E:\t4.ctl data=E:\t4.txt

SQL*Loader: Release 11.2.0.1.0 - Production on Mer. Mars 11 11:03:22 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-403: Referenced column CUSTOMER_NUMBER not present in table SAMPL_TABLE.


You are boring in the end.

[Updated on: Wed, 11 March 2015 05:04]

Report message to a moderator

Re: decimal value in control file [message #634548 is a reply to message #634547] Wed, 11 March 2015 05:07 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Did you drop existing table> Please try to with new code

where it is coming from?(CUSTOMER_NUMBER) and i don't have in my table and Ctl file

take recent code Please

[Updated on: Wed, 11 March 2015 05:08]

Report message to a moderator

Re: decimal value in control file [message #634550 is a reply to message #634548] Wed, 11 March 2015 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have taken it but you you haven't test it.

Re: decimal value in control file [message #634569 is a reply to message #634550] Wed, 11 March 2015 06:50 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Options(errors=10000,skip=3)
LOAD DATA 
INFILE '/home/appltest/Sheet1.csv'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
REPLACE  
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'    
trailing nullcols
(
 customer_name  CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
 customer_no  CHAR NULLIF customer_no = BLANKS "RTRIM(:customer_no)",
 item_no  CHAR NULLIF item_no = BLANKS "RTRIM(:item_no )",
valid_amount_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_amount_month1 = BLANKS "to_number(:valid_amount_month1 , '999,999,999.99')"  ,
valid_units_month1     CHAR TERMINATED BY WHITESPACE NULLIF valid_units_month1 = BLANKS "to_number(substr(:valid_units_month1,instr(:valid_units_month1,'$')+1),'999,999,999.99')",
valid_budget_units_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_budget_units_month1 = BLANKS "to_number(:valid_budget_units_month1, '999,999,999.99')"                                      ,
valid_budget_amount_month1  CHAR TERMINATED BY WHITESPACE NULLIF valid_budget_amount_month1 = BLANKS "to_number(trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1)),'999,999,999.99')"    ,
valid_rate    CHAR NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)

Please check the below attached File
Re: decimal value in control file [message #634577 is a reply to message #634569] Wed, 11 March 2015 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Take the first error:
Quote:
Record 1: Rejected - Error on table SAMPL_TABLE, column VALID_AMOUNT_MONTH1.
ORA-01722: invalid number

Check the value for this row:
REYNU,1001,ABC001,ABC Renyu,ABC,,,,,82,61,1

Error on column AMOUNT_MONTH1.
Column AMOUNT_MONTH1 is fourth one in the control file.
Fourth value in the row is "ABC Renyu".
This is indeed not a number.
So either the data are wrong, either the control file is wrong.

Do the same thing for all errors.

And try to think: is "123,456" one number or 2 numbers?
Waiting for your answer...

Re: decimal value in control file [message #634607 is a reply to message #634577] Thu, 12 March 2015 00:12 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Michel , check the modified code please
Options(errors=10000,skip=3)
LOAD DATA 
INFILE '/home/appltest/Sheet1.csv'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
REPLACE  
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'    
trailing nullcols
(
 customer_name  CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
 customer_no  CHAR NULLIF customer_no = BLANKS "RTRIM(:customer_no)",
 item_no  CHAR NULLIF item_no = BLANKS "RTRIM(:item_no )",
 item_description CHAR NULLIF item_description = BLANKS "RTRIM(:item_description )",
 item_category    CHAR NULLIF item_category = BLANKS "RTRIM(:item_category )",
valid_amount_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_amount_month1 = BLANKS "to_number(:valid_amount_month1 , '999,999,999.99')",
valid_units_month1     CHAR TERMINATED BY WHITESPACE NULLIF valid_units_month1 = BLANKS "to_number(substr(:valid_units_month1,instr(:valid_units_month1,'$')+1),'999,999,999.99')",
attribute1  CHAR NULLIF attribute1 = BLANKS "RTRIM(:attribute1)",
attribute2  CHAR NULLIF attribute2 = BLANKS "RTRIM(:attribute2)",
valid_budget_units_month1   CHAR TERMINATED BY WHITESPACE NULLIF valid_budget_units_month1 = BLANKS "to_number(:valid_budget_units_month1, '999,999,999.99')",
valid_budget_amount_month1  CHAR TERMINATED BY WHITESPACE NULLIF valid_budget_amount_month1 = BLANKS "to_number(trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1)),'999,999,999.99')",
valid_rate    CHAR NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)

Quote:

And try to think: is "123,456" one number or 2 numbers?
Waiting for your answer...

2 number's ,can you please explain clearly at which column has these types of values getting
Re: decimal value in control file [message #634610 is a reply to message #634607] Thu, 12 March 2015 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
2 number's ,


Wrong, it is one.

Re: decimal value in control file [message #634616 is a reply to message #634610] Thu, 12 March 2015 03:19 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member

  1  select value
  2  from nls_session_parameters
  3* where parameter = 'NLS_NUMERIC_CHARACTERS'
SQL> /

VALUE
------------------------------------------------
.,


I altered the above vale and try with the below code(.ctl code)
Options(errors=10000,skip=3)
LOAD DATA 
INFILE '/home/appltest/Sheet1.csv'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
REPLACE  
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
customer_name  CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
customer_no  CHAR NULLIF customer_no = BLANKS "RTRIM(:customer_no)",
item_no  CHAR NULLIF item_no = BLANKS "RTRIM(:item_no )",
item_description CHAR NULLIF item_description = BLANKS "RTRIM(:item_description )",
item_category    CHAR NULLIF item_category = BLANKS "RTRIM(:item_category )",
valid_units_month1  "to_number(:valid_units_month1, '999,999,999.99')",
valid_amount_month1 "to_number(substr(:valid_amount_month1,instr(:valid_amount_month1,'$')+1),'999,999,999.99')",
attribute1  CHAR NULLIF attribute1 = BLANKS "RTRIM(:attribute1)",
attribute2  CHAR NULLIF attribute2 = BLANKS "RTRIM(:attribute2)",
valid_budget_units_month1   "RTRIM(:valid_budget_units_month1)",
valid_budget_amount_month1  "trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1))",
valid_rate   CHAR  TERMINATED BY WHITESPACE "RTRIM(:valid_rate)"  
)

So 3 record are inserted into the Table so it is fine

I treid with all columns (86 columns) getting below error
Record 61: Rejected - Error on table SAMPL_TABLE, column VALID_BUDGET_AMOUNT_MONTH1.
ORA-01722: invalid number

Record 2162: Rejected - Error on table SAMPL_TABLE, column VALID_BUDGET_UNITS_MONTH1.
ORA-01722: invalid number

Total logical records skipped:          3
Total logical records read:          2162
Total logical records rejected:      1039


Why some of the rows of columns are inserted why another values are rejected?

[Updated on: Thu, 12 March 2015 03:20]

Report message to a moderator

Re: decimal value in control file [message #634617 is a reply to message #634616] Thu, 12 March 2015 03:23 Go to previous messageGo to next message
Littlefoot
Messages: 21482
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Because you are trying to insert invalid numbers into those columns.
Re: decimal value in control file [message #634618 is a reply to message #634617] Thu, 12 March 2015 03:28 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Because you are trying to insert invalid numbers into those columns.

But some of the rows got values within the same column, some has with 0 values

How it is possible?
Re: decimal value in control file [message #634620 is a reply to message #634616] Thu, 12 March 2015 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I altered the above vale and try with the below code


What you showed is session specific and so may not what SQL*Loader will use.

Re: decimal value in control file [message #634621 is a reply to message #634618] Thu, 12 March 2015 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How it is possible?


It is possible that what you think it does or it should do (for you) is not what it actually does.
I showed you how to analyze the issue.

Re: decimal value in control file [message #634624 is a reply to message #634621] Thu, 12 March 2015 03:56 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
It is possible that what you think it does or it should do (for you) is not what it actually does.
I showed you how to analyze the issue.

Ok.
................
................
valid_budget_amount_month1  "trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1))",
valid_code  CHAR TERMINATED BY WHITESPACE NULLIF valid_code = BLANKS "RTRIM(:valid_code)",
valid_rate  CHAR TERMINATED BY WHITESPACE NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)

VALID_CODE
---------
1,USD
1,USD
1,USD

I tried with the above code , valid_code to the control and data file in the same positions , but the both values are inserted into the only one column wht is wrong in my code?
Re: decimal value in control file [message #634625 is a reply to message #634624] Thu, 12 March 2015 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66445
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I give up.

Re: decimal value in control file [message #634626 is a reply to message #634625] Thu, 12 March 2015 05:30 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
I showed you how to analyze the issue.

Yes Thank you
.....
.....
valid_budget_amount_month1 CHAR "to_number(trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1)),'999,999,999.99')",

Getting problem with the above column
1) In the Table 1st row is inserted 2,3 is rejected what is the code need to be change in the ctl file??

Record 2: Rejected - Error on table SAMPL_TABLE, column VALID_BUDGET_AMOUNT_MONTH1.
ORA-01722: invalid number

Record 3: Rejected - Error on table SAMPL_TABLE, column VALID_BUDGET_AMOUNT_MONTH1.
ORA-01722: invalid number

select to_number(substr(146,instr(146,'$')+1),'999,999,999.99') from dual
o/p: 146

select to_number(substr(1469,instr(1469,'$')+1),'999,999,999.99') from dual
Error: ORA-01722: invalid number

can you please help me how to do resolve this error?
Re: decimal value in control file [message #634648 is a reply to message #634626] Thu, 12 March 2015 09:32 Go to previous messageGo to next message
cookiemonster
Messages: 13630
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQL> select to_number(substr('1,469',instr('1,469','$')+1),'999,999,999.99') from dual;
 
TO_NUMBER(SUBSTR('1,469',INSTR
------------------------------
                          1469
 
SQL> select to_number(substr(1469,instr(1469,'$')+1),'999999999.99') from dual;
 
TO_NUMBER(SUBSTR(1469,INSTR(14
------------------------------
                          1469
 
SQL> 
Re: decimal value in control file [message #634676 is a reply to message #634648] Fri, 13 March 2015 01:35 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Yes your code is correct, but i don't how the data file has what type of format mask values ,mean some time values are 1234 or '1,234' or$1,234 then?

Thanks
Re: decimal value in control file [message #634677 is a reply to message #634610] Fri, 13 March 2015 01:37 Go to previous message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:

Quote:
2 number's ,


Wrong, it is one.


Yes i changed my code and thank you Michel it is working fine.. Smile
Previous Topic: SQL Loader Error
Next Topic: Problem faced during export using parfile
Goto Forum:
  


Current Time: Mon Jun 24 08:17:39 CDT 2019