| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> SQL*Loader problem in 10g R2
HI
We are migrating our 9i databases to 10gR2.
Because of that we are configuring some scripts that load data into
some tables.
These scripts are batch processes that load the application core of the
bussiness (very important).
The fact is that they run perfectly well in 9i but the same scripts
cant load data in 10gR2 (!!!)
I send you here all the scripts.
Could you please tell me what is wrong in 10g that we cant load tables
with SQL Loader?
Any help ?
thanks in advance !
Laura
Error:
SQL*Loader-297: Invalid syntax or bind variable in SQL string for
column
SRAM_CREADO_POR.
ORA-01756: quoted string not properly terminated
Scripts:
Main:
sqlldr userid=saciw/xxx_at_dbprep02,
control=/oracle1/app/aplicaciones/inmueblesWeb/ctl/precargar_avisos_redi.ctl, log=/oracle1/app/aplicaciones/inmueblesWeb/out/migracion_REDI.20070119.105813.loader.redi_FORMATO.txt.out, bad=/oracle1/app/aplicaciones/inmueblesWeb/logs/migracion_REDI.20070119.105813.loader.redi_FORMATO.txt.bad,data=/oracle1/app/aplicaciones/inmueblesWeb/archivos/redi_FORMATO.txt, discard=/oracle1/app/aplicaciones/inmueblesWeb/logs/migracion_REDI.20070119.105813.loader.redi_FORMATO.txt.dis, errors=1000000, silent=all, readsize=2470000, bindsize=2470000, rows=100
control=/oracle1/app/aplicaciones/inmueblesWeb/ctl/precargar_avisos_redi.ctl LOAD DATA
INTO TABLE SACIREDI_AVISO_MIGRACION REPLACE
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
SRAM_CODINMUEBLE integer EXTERNAL,
SRAM_CODINMUEBLE_EN_BI CHAR(50 ),
SRAM_TIPOOPERACION CHAR(10 ),
SRAM_DESCRIPCIONRUBRO CHAR(50 ),
SRAM_TIPOINMUEBLE CHAR(50 ),
SRAM_OPERACION_1 integer EXTERNAL,
SRAM_MONTOOPERACION_1 integer EXTERNAL ,
SRAM_MONEDAOPERACION_1 CHAR(50 ),
SRAM_OPERACION_2 integer EXTERNAL ,
SRAM_MONTOOPERACION_2 integer EXTERNAL,
SRAM_MONEDAOPERACION_2 CHAR(50 ),
SRAM_SISTEMA CHAR(50 ),
SRAM_UNIDADMEDIDA CHAR(50 ),
SRAM_FECHAALTA Date "DD/MM/YYYY
HH:Mi:SS PM" ,
SRAM_FECHAMODIFICACION Date "DD/MM/YYYY HH:Mi:SS PM" ,
SRAM_OBSERVACIONES CHAR(4000 ),
SRAM_CONDICIONINMUEBLE CHAR(50 ),
SRAM_TIPOCONSTRUCCION CHAR(50 ),
SRAM_ORIENTACION CHAR(50 ),
SRAM_TIPOCOCHERA CHAR(50 ),
SRAM_TIPOACCESO CHAR(50 ),
SRAM_TIPOTECHO CHAR(50 ),
SRAM_CANTIDADAMBIENTES integer EXTERNAL,
SRAM_ANTIGUEDAD CHAR(50 ),
SRAM_SUPERFICIE_CUBIERTA integer EXTERNAL,
SRAM_SUPERFICIE_DESCUBIERTA integer EXTERNAL,
SRAM_PARTICULARIDADES CHAR(4000 ),
SRAM_PAIS CHAR(50 ),
SRAM_PROVINCIA CHAR(50 ),
SRAM_LOCALIDAD CHAR(50 ),
SRAM_BARRIO CHAR(50 ),
SRAM_SUBBARRIO CHAR(50 ),
SRAM_CALLESINCODIFICAR CHAR(50 ),
SRAM_CALLECODIFICADA CHAR(50 ),
SRAM_NUMERO integer
EXTERNAL,
SRAM_PISO CHAR(10 ),
SRAM_DEPARTAMENTO CHAR(10 ),
SRAM_CODIGOPOSTAL CHAR(50 ),
SRAM_LATITUD integer EXTERNAL,
SRAM_LONGITUD integer EXTERNAL,
SRAM_FOTOS CHAR(1000 )
"REPLACE(REPLACE(:SRAM_FOTOS,'Plana,',''), 'Plano,', '')",
SRAM_EXPENSAS CHAR(50 ) ,
SRAM_MONTODEEXPENSAS integer EXTERNAL,
SRAM_PCODINMO integer EXTERNAL,
SRAM_NOMBREINMO CHAR(50 ),
SRAM_CUIT CHAR(50 ),
SRAM_NOMBREARCHIVOLOGO CHAR(50 ),
SRAM_SISTEMAINMO CHAR(50 ),
SRAM_PAISINMO CHAR(50 ),
SRAM_PROVINCIAINMO CHAR(50 ),
SRAM_LOCALIDADINMO CHAR(50 ),
SRAM_BARRIOINMO CHAR(50 ),
SRAM_CALLEINMOCODIFICADA CHAR(50 ),
SRAM_CALLEINMOSINCODIFICAR CHAR(50 ),
SRAM_NUMEROINMO integer EXTERNAL,
SRAM_PISOINMO CHAR(10 ),
SRAM_DEPARTAMENTOINMO CHAR(10 ),
SRAM_CODIGOPOSTALINMO CHAR(50 ),
SRAM_TELEFONO CHAR(50 ),
SRAM_EMAIL CHAR(50 ),
SRAM_NOMBRECONTACTO CHAR(50 ),
SRAM_APELLIDOCONTACTO CHAR(50 ),
SRAM_TELEFONOCONTACTO CHAR(50 ),
SRAM_EMAILCONTACTO CHAR(50 ),
SRAM_VIGENCIA integer EXTERNAL,
SRAM_SUP_TERRENO CHAR(10)
"REPLACE(:SRAM_SUP_TERRENO, chr(13),'')",
SRAM_DESTAQUE integer EXTERNAL,
SRAM_CREADO_POR "'MIGRADOR_REDI'",
SRAM_FECHA_CREACION "sysdate",
SRAM_ESTADO "'INICIAL'"
)
data=/oracle1/app/aplicaciones/inmueblesWeb/archivos/redi_FORMATO.txt :
227385|AAL-328|M|Vivienda|Casa|Venta|45000.00|Dolares||||2||13/06/2006||linda
casa 217|Excelente|Casa|N|NULL|NULL||3|35|||1,Pi
leta,1,Termotanque|Argentina|Capital Federal|Capital Federal|Villa
Urquiza||Roosevelt Franklin D.|B5P0179P|3987||||-34.5673318
|-58.4755816|Plana,227385_1.JPG|NO||2058|My
Inmo|27-18555555-6||BuscaInmuebles|Argentina|Capital Federal|Capital
Federal|Villa
Urquiza|Monroe|Monroe|3944||||---|sdirr_at_agea.com.ar|||||7|130.00|0
Output:
SQL*Loader: Release 10.2.0.1.0 - Production on Vie Ene 19 15:52:55 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File:
/oracle1/app/aplicaciones/inmueblesWeb/ctl/precargar_avisos_redi.ctl
Data File:
/oracle1/app/aplicaciones/inmueblesWeb/archivos/redi_FORMATO.txt
Bad File:
/oracle1/app/aplicaciones/inmueblesWeb/logs/migracion_REDI.20070119.105813.loader.redi_FORMATO.txt.bad
Discard File:
/oracle1/app/aplicaciones/inmueblesWeb/logs/migracion_REDI.20070119.105813.loader.redi_FORMATO.txt.dis
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 1000000 Bind array: 100 rows, maximum of 2470000 bytes Continuation: none specified Path used: Conventional
Table "SACIW_H"."SACIREDI_AVISO_MIGRACION", loaded from every logical
record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- SRAM_CODINMUEBLE FIRST * | CHARACTER SRAM_CODINMUEBLE_EN_BI NEXT 50 | CHARACTER SRAM_TIPOOPERACION NEXT 10 | CHARACTER SRAM_DESCRIPCIONRUBRO NEXT 50 | CHARACTER SRAM_TIPOINMUEBLE NEXT 50 | CHARACTER SRAM_OPERACION_1 NEXT * | CHARACTER SRAM_MONTOOPERACION_1 NEXT * | CHARACTER SRAM_MONEDAOPERACION_1 NEXT 50 | CHARACTER SRAM_OPERACION_2 NEXT * | CHARACTER SRAM_MONTOOPERACION_2 NEXT * | CHARACTER SRAM_MONEDAOPERACION_2 NEXT 50 | CHARACTER SRAM_SISTEMA NEXT 50 | CHARACTER SRAM_UNIDADMEDIDA NEXT 50 | CHARACTER SRAM_FECHAALTA NEXT * | DATE DD/MM/YYYY HH:Mi:SS PM SRAM_FECHAMODIFICACION NEXT * | DATE DD/MM/YYYY HH:Mi:SS PM SRAM_OBSERVACIONES NEXT 4000 | CHARACTER SRAM_CONDICIONINMUEBLE NEXT 50 | CHARACTER SRAM_TIPOCONSTRUCCION NEXT 50 | CHARACTER SRAM_ORIENTACION NEXT 50 | CHARACTER SRAM_TIPOCOCHERA NEXT 50 | CHARACTER SRAM_TIPOACCESO NEXT 50 | CHARACTER SRAM_TIPOTECHO NEXT 50 | CHARACTER SRAM_CANTIDADAMBIENTES NEXT * | CHARACTER SRAM_ANTIGUEDAD NEXT 50 | CHARACTER SRAM_SUPERFICIE_CUBIERTA NEXT * | CHARACTER SRAM_SUPERFICIE_DESCUBIERTA NEXT * | CHARACTER SRAM_PARTICULARIDADES NEXT 4000 | CHARACTER SRAM_PAIS NEXT 50 | CHARACTER SRAM_PROVINCIA NEXT 50 | CHARACTER SRAM_LOCALIDAD NEXT 50 | CHARACTER SRAM_BARRIO NEXT 50 | CHARACTER SRAM_SUBBARRIO NEXT 50 | CHARACTER SRAM_CALLESINCODIFICAR NEXT 50 | CHARACTER SRAM_CALLECODIFICADA NEXT 50 | CHARACTER SRAM_NUMERO NEXT * | CHARACTER SRAM_PISO NEXT 10 | CHARACTER SRAM_DEPARTAMENTO NEXT 10 | CHARACTER SRAM_CODIGOPOSTAL NEXT 50 | CHARACTER SRAM_LATITUD NEXT * | CHARACTER SRAM_LONGITUD NEXT * | CHARACTER SRAM_FOTOS NEXT 1000 | CHARACTER
SQL string for column : "REPLACE(REPLACE(:SRAM_FOTOS,'Plana,',''), 'Plano,', '')"
SRAM_EXPENSAS NEXT 50 | CHARACTER
SRAM_MONTODEEXPENSAS NEXT * | CHARACTER
SRAM_PCODINMO NEXT * | CHARACTER
SRAM_NOMBREINMO NEXT 50 | CHARACTER
SRAM_CUIT NEXT 50 | CHARACTER
SRAM_NOMBREARCHIVOLOGO NEXT 50 | CHARACTER
SRAM_SISTEMAINMO NEXT 50 | CHARACTER
SRAM_PAISINMO NEXT 50 | CHARACTER
SRAM_PROVINCIAINMO NEXT 50 | CHARACTER
SRAM_LOCALIDADINMO NEXT 50 | CHARACTER
SRAM_BARRIOINMO NEXT 50 | CHARACTER
SRAM_CALLEINMOCODIFICADA NEXT 50 | CHARACTER
SRAM_CALLEINMOSINCODIFICAR NEXT 50 | CHARACTER
SRAM_NUMEROINMO NEXT * | CHARACTER
SRAM_PISOINMO NEXT 10 | CHARACTER
SRAM_DEPARTAMENTOINMO NEXT 10 | CHARACTER
SRAM_CODIGOPOSTALINMO NEXT 50 | CHARACTER
SRAM_TELEFONO NEXT 50 | CHARACTER
SRAM_EMAIL NEXT 50 | CHARACTER
SRAM_NOMBRECONTACTO NEXT 50 | CHARACTER
SRAM_APELLIDOCONTACTO NEXT 50 | CHARACTER
SRAM_TELEFONOCONTACTO NEXT 50 | CHARACTER
SRAM_EMAILCONTACTO NEXT 50 | CHARACTER
SRAM_VIGENCIA NEXT * | CHARACTER
SRAM_SUP_TERRENO NEXT 10 | CHARACTER
SQL string for column : "REPLACE(:SRAM_SUP_TERRENO, chr(13),'')"
SRAM_DESTAQUE NEXT * | CHARACTER
SRAM_CREADO_POR NEXT * | CHARACTER
SQL string for column : "'MIGRADOR_REDI'"
SRAM_FECHA_CREACION NEXT * | CHARACTER
SQL string for column : "sysdate"
SRAM_ESTADO NEXT * | CHARACTER
SQL string for column : "'INICIAL'"
SQL*Loader-297: Invalid syntax or bind variable in SQL string for
column SRAM_CREADO_POR.
ORA-01756: quoted string not properly terminated
Received on Fri Jan 19 2007 - 15:27:42 CST
 |
 |