SQL Loader with Expression-Function

[maik.b...@arcor.de]

Hello all,

I have a problem with the SQL-Loader using the expression-function. I
get the following error but I cannot find whats wrong with my
control-file.

Perhaps anyone can help me.

Thx and brdgs,
Maik

Record 1: Rejected - Error on table DHL.SA_CUSTOMER_IMPORT, column
UPLOAD_ID.
ORA-00936: missing expression

Control-File:

LOAD DATA APPEND INTO TABLE DHL.SA_CUSTOMER_IMPORT FIELDS TERMINATED BY
" " OPTIONALLY ENCLOSED BY '\''
TRAILING NULLCOLS (
APPLICATION_ID CONSTANT 1,
UPLOAD_ID EXPRESSION "SELECT
'LISA'||to_char(to_number(to_char(sysdate,'YYYYMM'))-1)||'ACT' from
DUAL",
VERSION CONSTANT 'F2',
CUSTOMER_ID,
BU_ID,
YEAR_ID "REPLACE(:YEAR_ID,'Y','')",
PERIOD_ID "REPLACE(:PERIOD_ID,'M','')",
ACTIVITY_ID "REPLACE(:ACTIVITY_ID,'A_','')",
SEGMENT_ID CONSTANT '',
PRODUCT_ID CONSTANT '',
SCENARIO_ID,
ACCOUNT_ID "DECODE(REPLACE(:ACCOUNT_ID,'ACC_',''), '10_A_10','10',
'10_A_20','10',
'10_A_30','10',
'10_A_40','10',
'10_A_50','10',
'20_A_10','20',
'20_A_50','20',REPLACE(:ACCOUNT_ID,'ACC_',''))",
DATA_UNIT_ID,
USER_ID CONSTANT 'LISAASIAEXPORT',
CHANGE_DATE SYSDATE,
DATA_VALUE,
ROW_NUMBER SEQUENCE(1,1),
ACTION_ID CONSTANT 10)

[DA Morgan]

Could you please
1. give your Oracle version number
2. provide the DDL for the SA_CUSTOMER_IMPORT table
3. provide 5 rows of data you are trying to load.

It will make it easier to figure out what is happening.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[fred]

Hello, in my system your expresion for column upload_id is wrong. I guess a blank is missing.
If your problem is not solved yet try " 'yyyymm')) - 1)" instead of " 'yyyymm'))-1)"
best regards
DA Morgan <damo...@psoug.org> hat geschrieben: