Home » RDBMS Server » Server Utilities » SQL Load..char string problem with 10g
SQL Load..char string problem with 10g [message #227535] Wed, 28 March 2007 08:56 Go to next message
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 #227536 is a reply to message #227535] Wed, 28 March 2007 09:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
More information needed.
Post your create table statement,sqlldr control file and some sample data.
Re: SQL Load..char string problem with 10g [message #227540 is a reply to message #227535] Wed, 28 March 2007 09:30 Go to previous messageGo to next message
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(Cool 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 #227542 is a reply to message #227540] Wed, 28 March 2007 09:41 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
the data is just one line..attached below. thank you

"11234567 S5803 1111111 "
Re: SQL Load..char string problem with 10g [message #227544 is a reply to message #227542] Wed, 28 March 2007 09:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: SQL Load..char string problem with 10g [message #227550 is a reply to message #227544] Wed, 28 March 2007 10:53 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Didn't understand, what is the difference ?
Re: SQL Load..char string problem with 10g [message #227551 is a reply to message #227550] Wed, 28 March 2007 10:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
group_status constant "'P'",
Re: SQL Load..char string problem with 10g [message #227586 is a reply to message #227535] Wed, 28 March 2007 13:24 Go to previous message
yog_23
Messages: 79
Registered: March 2007
Member
thank you. that works.
Previous Topic: Import file Size
Next Topic: sql loader using function returing a varray
Goto Forum:
  


Current Time: Sun Dec 11 08:09:09 CST 2016

Total time taken to generate the page: 0.07947 seconds