| SQL Load..char string problem with 10g [message #227535] |
Wed, 28 March 2007 08:56  |
yog_23
Messages: 79
Registered: March 2007
|
Member |
|
|
The load script seems to be working in older database versions but doesn't work in a new environment
group_status "'P'",
Getting an error
SQL*Loader-297: Invalid syntax or bind variable in SQL string for column group_status.
ORA-01756: quoted string not properly terminated
Any idea what will be the fix. ?
|
|
|
|
|
|
| Re: SQL Load..char string problem with 10g [message #227540 is a reply to message #227535] |
Wed, 28 March 2007 09:30  |
yog_23
Messages: 79
Registered: March 2007
|
Member |
|
|
Thank you.
Here is the table and control file.
Table:
CREATE TABLE SCHEMA.GROUPS(
HEADER_ID NUMBER NOT NULL,
FILE_ID NUMBER NULL,
FILE_NAME VARCHAR2(250) NULL,
RECORD_TYPE VARCHAR2(1) NULL,
ACCOUNT VARCHAR2( NULL,
BATCH_NUMBER NUMBER NULL,
TRANS_COUNT NUMBER NULL,
GROUP_STATUS VARCHAR2(5) NULL,
CREATED_BY VARCHAR2(30) NULL,
CREATION_DATE DATE NULL,
LAST_UPDATED_BY VARCHAR2(30) NULL,
LAST_UPDATE_DATE DATE NULL,
PRIMARY KEY(HEADER_ID)
)
GO
LOAD DATA
APPEND
INTO TABLE SCHEMA.GROUPS
WHEN record_type = '1' -- condition to skip the header and trailer recs
TRAILING NULLCOLS(
header_id "header_seq.NEXTVAL",
record_type POSITION(1:1) INTEGER EXTERNAL,
account POSITION(2:8) CHAR,
batch_number POSITION(16:21) INTEGER EXTERNAL "RTRIM(:batch_number, '0')",
trans_count POSITION(22:29) INTEGER EXTERNAL,
group_status "'P'",
creation_date "SYSDATE",
last_update_date "SYSDATE"
)
|
|
|
|
|
|
| Re: SQL Load..char string problem with 10g [message #227544 is a reply to message #227542] |
Wed, 28 March 2007 09:58 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
try using a constant. Like this
oracle@store:~> cat somectl.ctl
LOAD DATA
infile *
APPEND
INTO TABLE test
WHEN record_type = '1' -- condition to skip the header and trailer recs
TRAILING NULLCOLS(
header_id "header_seq.NEXTVAL",
record_type POSITION(1:1) INTEGER EXTERNAL,
account POSITION(2:8) CHAR,
batch_number POSITION(16:21) INTEGER EXTERNAL "RTRIM(:batch_number, '0')",
trans_count POSITION(22:29) INTEGER EXTERNAL,
group_status constant "'P'",
creation_date "SYSDATE",
last_update_date "SYSDATE"
)
begindata
"11234567 S5803 1111111 "
Anyhow, the provided test data fails with when clause.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
