Columns with leading underscore in Oracle SQL Loader
0
I want to load data in an Oracle database via SQL Loader.
Here is my control file. I cut out the middle part that its not too long..
OPTIONS (BINDSIZE=512000, ROWS=10000)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE EIDESWTDOC
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
MANDT CHAR(6)"NVL(:MANDT,' ')"
, SWITCHNUM CHAR(40)"NVL(:SWITCHNUM,' ')"
, POD CHAR(44)"NVL(:POD,' ')"
, SWITCHTYPE "NVL(:SWITCHTYPE, 0.0)"
, OWNER CHAR(20)"NVL(:OWNER,' ')"
, SWTVIEW "NVL(:SWTVIEW, 0.0)"
, MOVEINDATE DATE 'YYYY-MM-DD'
, MOVEOUTDATE DATE 'YYYY-MM-DD'
.....
, SP_INITIATOR CHAR(20)"NVL(:SP_INITIATOR,' ')"
, _IDEXGE_NONFIXED CHAR(2)"NVL(:_IDEXGE_NONFIXED,' ')"
, _IDEXGE_MRPERIO CHAR(4)"NVL(:_IDEXGE_MRPERIO,' ')"
)
Because of the columns beginning with an underscore I get a syntax error (illegal combination of non-alphanumerical characters).
I already tried quoting it but nothing works. Has anybody a solution for this issue?
Best regards, Chris
oracle sql-loader controlfile
asked Nov 20 '18 at 9:02
csnr
396
add a comment |
0
I want to load data in an Oracle database via SQL Loader.
Here is my control file. I cut out the middle part that its not too long..
OPTIONS (BINDSIZE=512000, ROWS=10000)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE EIDESWTDOC
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
MANDT CHAR(6)"NVL(:MANDT,' ')"
, SWITCHNUM CHAR(40)"NVL(:SWITCHNUM,' ')"
, POD CHAR(44)"NVL(:POD,' ')"
, SWITCHTYPE "NVL(:SWITCHTYPE, 0.0)"
, OWNER CHAR(20)"NVL(:OWNER,' ')"
, SWTVIEW "NVL(:SWTVIEW, 0.0)"
, MOVEINDATE DATE 'YYYY-MM-DD'
, MOVEOUTDATE DATE 'YYYY-MM-DD'
.....
, SP_INITIATOR CHAR(20)"NVL(:SP_INITIATOR,' ')"
, _IDEXGE_NONFIXED CHAR(2)"NVL(:_IDEXGE_NONFIXED,' ')"
, _IDEXGE_MRPERIO CHAR(4)"NVL(:_IDEXGE_MRPERIO,' ')"
)
Because of the columns beginning with an underscore I get a syntax error (illegal combination of non-alphanumerical characters).
I already tried quoting it but nothing works. Has anybody a solution for this issue?
Best regards, Chris
oracle sql-loader controlfile
asked Nov 20 '18 at 9:02
csnr
396
add a comment |
0
0
0
I want to load data in an Oracle database via SQL Loader.
Here is my control file. I cut out the middle part that its not too long..
OPTIONS (BINDSIZE=512000, ROWS=10000)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE EIDESWTDOC
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
MANDT CHAR(6)"NVL(:MANDT,' ')"
, SWITCHNUM CHAR(40)"NVL(:SWITCHNUM,' ')"
, POD CHAR(44)"NVL(:POD,' ')"
, SWITCHTYPE "NVL(:SWITCHTYPE, 0.0)"
, OWNER CHAR(20)"NVL(:OWNER,' ')"
, SWTVIEW "NVL(:SWTVIEW, 0.0)"
, MOVEINDATE DATE 'YYYY-MM-DD'
, MOVEOUTDATE DATE 'YYYY-MM-DD'
.....
, SP_INITIATOR CHAR(20)"NVL(:SP_INITIATOR,' ')"
, _IDEXGE_NONFIXED CHAR(2)"NVL(:_IDEXGE_NONFIXED,' ')"
, _IDEXGE_MRPERIO CHAR(4)"NVL(:_IDEXGE_MRPERIO,' ')"
)
Because of the columns beginning with an underscore I get a syntax error (illegal combination of non-alphanumerical characters).
I already tried quoting it but nothing works. Has anybody a solution for this issue?
Best regards, Chris
oracle sql-loader controlfile
asked Nov 20 '18 at 9:02
csnr
396
I want to load data in an Oracle database via SQL Loader.
Here is my control file. I cut out the middle part that its not too long..
OPTIONS (BINDSIZE=512000, ROWS=10000)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE EIDESWTDOC
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
MANDT CHAR(6)"NVL(:MANDT,' ')"
, SWITCHNUM CHAR(40)"NVL(:SWITCHNUM,' ')"
, POD CHAR(44)"NVL(:POD,' ')"
, SWITCHTYPE "NVL(:SWITCHTYPE, 0.0)"
, OWNER CHAR(20)"NVL(:OWNER,' ')"
, SWTVIEW "NVL(:SWTVIEW, 0.0)"
, MOVEINDATE DATE 'YYYY-MM-DD'
, MOVEOUTDATE DATE 'YYYY-MM-DD'
.....
, SP_INITIATOR CHAR(20)"NVL(:SP_INITIATOR,' ')"
, _IDEXGE_NONFIXED CHAR(2)"NVL(:_IDEXGE_NONFIXED,' ')"
, _IDEXGE_MRPERIO CHAR(4)"NVL(:_IDEXGE_MRPERIO,' ')"
)
Because of the columns beginning with an underscore I get a syntax error (illegal combination of non-alphanumerical characters).
I already tried quoting it but nothing works. Has anybody a solution for this issue?
Best regards, Chris
oracle sql-loader controlfile
oracle sql-loader controlfile
asked Nov 20 '18 at 9:02
csnr
396
asked Nov 20 '18 at 9:02
csnr
396
asked Nov 20 '18 at 9:02
csnr
396
asked Nov 20 '18 at 9:02
csnr
396
asked Nov 20 '18 at 9:02
csnr
396
396
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Hi Quoting seems to work.
desc temp ;
Name Null? Type
--------- ----- -------------
FIRSTNAME VARCHAR2(255)
_Address VARCHAR2(255)
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
"_Address" CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 00:59:36 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
But whenever i try without quoting am getting the exact same error that your reported
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
_Address CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 01:05:23 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-350: Syntax error at line 5.
Illegal combination of non-alphanumeric characters
_Address CHAR)
^
PS : Try to quote all column names that are beginning with underscore.
answered Nov 20 '18 at 19:39
venkat
365
2
The problem is referring to the column in the NVL() function:"NVL(:_IDEXGE_NONFIXED,' ')"
– Gary_W
Nov 20 '18 at 20:46
Thanks! Totally forgot quoting the column in the NVL function... It works now.
– csnr
Nov 21 '18 at 7:18
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389468%2fcolumns-with-leading-underscore-in-oracle-sql-loader%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Hi Quoting seems to work.
desc temp ;
Name Null? Type
--------- ----- -------------
FIRSTNAME VARCHAR2(255)
_Address VARCHAR2(255)
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
"_Address" CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 00:59:36 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
But whenever i try without quoting am getting the exact same error that your reported
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
_Address CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 01:05:23 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-350: Syntax error at line 5.
Illegal combination of non-alphanumeric characters
_Address CHAR)
^
PS : Try to quote all column names that are beginning with underscore.
answered Nov 20 '18 at 19:39
venkat
365
2
The problem is referring to the column in the NVL() function:"NVL(:_IDEXGE_NONFIXED,' ')"
– Gary_W
Nov 20 '18 at 20:46
Thanks! Totally forgot quoting the column in the NVL function... It works now.
– csnr
Nov 21 '18 at 7:18
add a comment |
1
Hi Quoting seems to work.
desc temp ;
Name Null? Type
--------- ----- -------------
FIRSTNAME VARCHAR2(255)
_Address VARCHAR2(255)
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
"_Address" CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 00:59:36 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
But whenever i try without quoting am getting the exact same error that your reported
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
_Address CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 01:05:23 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-350: Syntax error at line 5.
Illegal combination of non-alphanumeric characters
_Address CHAR)
^
PS : Try to quote all column names that are beginning with underscore.
answered Nov 20 '18 at 19:39
venkat
365
2
The problem is referring to the column in the NVL() function:"NVL(:_IDEXGE_NONFIXED,' ')"
– Gary_W
Nov 20 '18 at 20:46
Thanks! Totally forgot quoting the column in the NVL function... It works now.
– csnr
Nov 21 '18 at 7:18
add a comment |
1
1
1
Hi Quoting seems to work.
desc temp ;
Name Null? Type
--------- ----- -------------
FIRSTNAME VARCHAR2(255)
_Address VARCHAR2(255)
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
"_Address" CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 00:59:36 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
But whenever i try without quoting am getting the exact same error that your reported
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
_Address CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 01:05:23 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-350: Syntax error at line 5.
Illegal combination of non-alphanumeric characters
_Address CHAR)
^
PS : Try to quote all column names that are beginning with underscore.
answered Nov 20 '18 at 19:39
venkat
365
Hi Quoting seems to work.
desc temp ;
Name Null? Type
--------- ----- -------------
FIRSTNAME VARCHAR2(255)
_Address VARCHAR2(255)
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
"_Address" CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 00:59:36 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
But whenever i try without quoting am getting the exact same error that your reported
Control File
LOAD DATA
APPEND INTO TABLE TEMP
FIELDS TERMINATED BY ','
(FirstName CHAR,
_Address CHAR)
Execution log
$ sqlldr ***/*** control="/home/venkat/Desktop/sqlLoader/load.ctl" data = "/home/venkat/Desktop/sqlLoader/data.txt"
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Nov 21 01:05:23 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-350: Syntax error at line 5.
Illegal combination of non-alphanumeric characters
_Address CHAR)
^
PS : Try to quote all column names that are beginning with underscore.
answered Nov 20 '18 at 19:39
venkat
365
answered Nov 20 '18 at 19:39
venkat
365
answered Nov 20 '18 at 19:39
venkat
365
answered Nov 20 '18 at 19:39
venkat
365
365
2
The problem is referring to the column in the NVL() function:"NVL(:_IDEXGE_NONFIXED,' ')"
– Gary_W
Nov 20 '18 at 20:46
Thanks! Totally forgot quoting the column in the NVL function... It works now.
– csnr
Nov 21 '18 at 7:18
add a comment |
2
The problem is referring to the column in the NVL() function:"NVL(:_IDEXGE_NONFIXED,' ')"
– Gary_W
Nov 20 '18 at 20:46
Thanks! Totally forgot quoting the column in the NVL function... It works now.
– csnr
Nov 21 '18 at 7:18
2
2
The problem is referring to the column in the NVL() function:
"NVL(:_IDEXGE_NONFIXED,' ')"– Gary_W
Nov 20 '18 at 20:46
The problem is referring to the column in the NVL() function:
"NVL(:_IDEXGE_NONFIXED,' ')"– Gary_W
Nov 20 '18 at 20:46
Thanks! Totally forgot quoting the column in the NVL function... It works now.
– csnr
Nov 21 '18 at 7:18
Thanks! Totally forgot quoting the column in the NVL function... It works now.
– csnr
Nov 21 '18 at 7:18
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389468%2fcolumns-with-leading-underscore-in-oracle-sql-loader%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
