OraFAQ Forum: Server Utilities » decimal value in control file

Home » RDBMS Server » Server Utilities » decimal value in control file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

decimal value in control file [message #634420]

Tue, 10 March 2015 01:30

mist598
Messages: 1195
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

Report message to a moderator

Re: decimal value in control file [message #634430 is a reply to message #634420]

Tue, 10 March 2015 02:05

Littlefoot
Messages: 21759
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you test it? What was the result?

Report message to a moderator

Re: decimal value in control file [message #634458 is a reply to message #634430]

Tue, 10 March 2015 05:14

mist598
Messages: 1195
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'

Report message to a moderator

Re: decimal value in control file [message #634466 is a reply to message #634458]

Tue, 10 March 2015 06:32

Michel Cadot
Messages: 68417
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

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: decimal value in control file [message #634525 is a reply to message #634466]

Wed, 11 March 2015 03:24

mist598
Messages: 1195
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

Report message to a moderator

Re: decimal value in control file [message #634526 is a reply to message #634525]

Wed, 11 March 2015 03:50

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: decimal value in control file [message #634527 is a reply to message #634525]

Wed, 11 March 2015 03:50

Michel Cadot
Messages: 68417
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.

Report message to a moderator

Re: decimal value in control file [message #634528 is a reply to message #634526]

Wed, 11 March 2015 03:53

Michel Cadot
Messages: 68417
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

Report message to a moderator

Re: decimal value in control file [message #634533 is a reply to message #634527]

Wed, 11 March 2015 04:24

mist598
Messages: 1195
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 459 times)

Report message to a moderator

Re: decimal value in control file [message #634535 is a reply to message #634533]

Wed, 11 March 2015 04:34

Michel Cadot
Messages: 68417
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.

Report message to a moderator

Re: decimal value in control file [message #634537 is a reply to message #634535]

Wed, 11 March 2015 04:43

mist598
Messages: 1195
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)"
)

Report message to a moderator

Re: decimal value in control file [message #634539 is a reply to message #634537]

Wed, 11 March 2015 04:49

Michel Cadot
Messages: 68417
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.

Report message to a moderator

Re: decimal value in control file [message #634542 is a reply to message #634539]

Wed, 11 March 2015 04:56

mist598
Messages: 1195
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)"
)

Report message to a moderator

Re: decimal value in control file [message #634547 is a reply to message #634542]

Wed, 11 March 2015 05:04

Michel Cadot
Messages: 68417
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

mist598
Messages: 1195
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

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: decimal value in control file [message #634569 is a reply to message #634550]

Wed, 11 March 2015 06:50

mist598
Messages: 1195
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

Attachment: sampletest.log
(Size: 3.15KB, Downloaded 665 times)

Report message to a moderator

Re: decimal value in control file [message #634577 is a reply to message #634569]

Wed, 11 March 2015 09:38

Michel Cadot
Messages: 68417
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...

Report message to a moderator

Re: decimal value in control file [message #634607 is a reply to message #634577]

Thu, 12 March 2015 00:12

mist598
Messages: 1195
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

Report message to a moderator

Re: decimal value in control file [message #634610 is a reply to message #634607]

Thu, 12 March 2015 01:54

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

2 number's ,

Wrong, it is one.

Report message to a moderator

Re: decimal value in control file [message #634616 is a reply to message #634610]

Thu, 12 March 2015 03:19

mist598
Messages: 1195
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

Littlefoot
Messages: 21759
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Because you are trying to insert invalid numbers into those columns.

Report message to a moderator

Re: decimal value in control file [message #634618 is a reply to message #634617]

Thu, 12 March 2015 03:28

mist598
Messages: 1195
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?

Report message to a moderator

Re: decimal value in control file [message #634620 is a reply to message #634616]

Thu, 12 March 2015 03:48

Michel Cadot
Messages: 68417
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.

Report message to a moderator

Re: decimal value in control file [message #634621 is a reply to message #634618]

Thu, 12 March 2015 03:49

Michel Cadot
Messages: 68417
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.

Report message to a moderator

Re: decimal value in control file [message #634624 is a reply to message #634621]

Thu, 12 March 2015 03:56

mist598
Messages: 1195
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?

Report message to a moderator

Re: decimal value in control file [message #634625 is a reply to message #634624]

Thu, 12 March 2015 04:08

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I give up.

Report message to a moderator

Re: decimal value in control file [message #634626 is a reply to message #634625]

Thu, 12 March 2015 05:30

mist598
Messages: 1195
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?

Report message to a moderator

Re: decimal value in control file [message #634648 is a reply to message #634626]

Thu, 12 March 2015 09:32

cookiemonster
Messages: 13904
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>

Report message to a moderator

Re: decimal value in control file [message #634676 is a reply to message #634648]

Fri, 13 March 2015 01:35

mist598
Messages: 1195
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

Report message to a moderator

Re: decimal value in control file [message #634677 is a reply to message #634610]

Fri, 13 March 2015 01:37

mist598
Messages: 1195
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

Report message to a moderator

Previous Topic:	SQL Loader Error
Next Topic:	Problem faced during export using parfile

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 22 22:50:00 CDT 2023