Home » RDBMS Server » Server Utilities » Transforming Data During a Load (SQL*Loader) with SQL function (Oracle 10g)
Transforming Data During a Load (SQL*Loader) with SQL function [message #600690] Mon, 11 November 2013 04:40 Go to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
This my control file

> cat loader.ctl
load data
 infile '/opt/oracle/tools/data/server.dat'
 REPLACE
 into table mxonline_test1
 fields terminated by ";" optionally enclosed by '"'
 ( OS_FQDN, OS_OS, OS_OSVersion, OS_Virtual, OS_IP, HDW_NodeName, HDW_Location, HDW_Producer, ALIAS_FQDN, HDW_Model, ALIAS_IP, ALIAS_NetworkAddressType, HWLifecycle, Maintainer)


This destination table

SQL> desc MXONLINE_TEST1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OS_FQDN                                            VARCHAR2(120)
 OS_OS                                              VARCHAR2(60)
 OS_OSVERSION                                       VARCHAR2(60)
 OS_VIRTUAL                                         VARCHAR2(60)
 OS_IP                                              VARCHAR2(60)
 HDW_NODENAME                                       VARCHAR2(60)
 HDW_LOCATION                                       VARCHAR2(60)
 HDW_PRODUCER                                       VARCHAR2(60)
 HDW_MODEL                                          VARCHAR2(60)
 ALIAS_FQDN                                         VARCHAR2(60)
 ALIAS_IP                                           VARCHAR2(60)
 ALIAS_NETWORKADDRESSTYPE                           VARCHAR2(60)
 HWLIFECYCLE                                        VARCHAR2(60)
 MAINTAINER                                         VARCHAR2(60)
 APPLICATION                                        VARCHAR2(60)
 IMPORTED                                           DATE


This sample entries from *.csv file

 > head -n2 server.dat
"hostname.domain.net";"Sun/Solaris";"Solaris 9";"xxx";"xxx";"xxxx";"xx";"xxx";"xxx";"Netra T1405";"xxx";"xxx";"xxx";"xxx";"
"hostname2.domain.net";"Sun/Solaris";"Solaris 9";"xxx";"xxx";"xxx";"xxx";"xxx";"xxx";"Netra T1405";"xxx";"xxx";"xxx";"xxx";"


Now wish to add this sql function to field

SELECT (SUBSTR (LOWER (OS_FQDN), 1, INSTR (LOWER (OS_FQDN), '.') - 1))
          AS HOSTNAME
  FROM MXONLINE_TEST1


and load in new field "Hostname". Transform data with SQL function during load.

found with Google http://my.safaribooksonline.com/book/databases/sql/1565929489/transforming-data-during-a-load/orsqlloader-chp-8-sect-1

sadly behind Paywall:
Quote:
One of the most powerful capabilities at your disposal when using SQL*Loader is the ability to define a SQL expression that operates on a field being loaded.
Instead of loading the contents of the field, SQL*Loader loads the results of the expression.

Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600691 is a reply to message #600690] Mon, 11 November 2013 04:42 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a function based on "this SQL function" (don't just talk about it) (i.e. put SELECT statement into CREATE FUNCTION), and then call that function from the control file.
Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600725 is a reply to message #600690] Mon, 11 November 2013 11:52 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Or you could try using "EXPRESSION":
LOAD DATA
 INFILE '/opt/oracle/tools/data/server.dat'
 REPLACE INTO TABLE mxonline_test1
 FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'
( os_fqdn, os_os, os_osversion, os_virtual, os_ip, hdw_nodename
, hdw_location, hdw_producer, alias_fqdn, hdw_model, alias_ip
, alias_networkaddresstype, hwlifecycle, maintainer
, hostname EXPRESSION "SUBSTR (LOWER (:OS_FQDN), 1, INSTR (LOWER (:OS_FQDN), '.') - 1))"  
)

[Updated on: Mon, 11 November 2013 12:17] by Moderator

Report message to a moderator

Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600852 is a reply to message #600725] Wed, 13 November 2013 03:03 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
thanks LKBrwn_DBA

now ORA-00911: invalid character error

control file

load data
 infile '/opt/oracle/tools/data/server.dat'
 REPLACE
 into table mxonline_test1
 fields terminated by ";" optionally enclosed by '"'
 (HOSTNAME EXPRESSION "SUBSTR (LOWER (:OS_FQDN), 1, INSTR (LOWER (:OS_FQDN), '.') - 1));", OS_FQDN, OS_OS, OS_OSVersion, OS_Virtual, OS_IP, HDW_NodeName, HDW_Location)


log

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Nov 13 09:52:54 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   /opt/oracle/tools/datalod/control/loader.ctl
Data File:      /opt/oracle/tools/datalod/jdbc/server.dat
  Bad File:     /opt/oracle/tools/datalod/control/server.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table MXONLINE_TEST1, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
HOSTNAME                                                  EXPRESSION
    SQL string for column : "SUBSTR (LOWER (:OS_FQDN), 1, INSTR (LOWER (:OS_FQDN), '.') - 1));"
OS_FQDN                             FIRST     *   ;  O(") CHARACTER
OS_OS                                NEXT     *   ;  O(") CHARACTER
OS_OSVERSION                         NEXT     *   ;  O(") CHARACTER
OS_VIRTUAL                           NEXT     *   ;  O(") CHARACTER
OS_IP                                NEXT     *   ;  O(") CHARACTER
HDW_NODENAME                         NEXT     *   ;  O(") CHARACTER
HDW_LOCATION                         NEXT     *   ;  O(") CHARACTER

Record 1: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 2: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 3: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 4: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 5: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 6: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 7: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 8: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 9: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 10: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 11: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 12: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 13: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 14: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 15: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 16: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 17: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 18: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 19: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 20: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 21: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 22: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 23: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 24: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 25: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 26: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 27: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 28: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 29: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 30: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 31: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 32: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 33: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 34: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 35: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 36: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 37: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 38: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 39: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 40: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 41: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 42: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 43: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 44: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 45: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 46: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 47: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 48: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 49: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 50: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character

Record 51: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00911: invalid character


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table MXONLINE_TEST1:
  0 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 115584 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            64
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Wed Nov 13 09:52:54 2013
Run ended on Wed Nov 13 09:52:54 2013

Elapsed time was:     00:00:00.28
CPU time was:         00:00:00.10
Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600853 is a reply to message #600852] Wed, 13 November 2013 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Semi-colon here?

(HOSTNAME EXPRESSION "SUBSTR (LOWER (:OS_FQDN), 1, INSTR (LOWER (:OS_FQDN), '.') - 1));", OS_FQDN,
Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600856 is a reply to message #600853] Wed, 13 November 2013 04:52 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
ORA-00933: SQL command not properly ended

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table MXONLINE_TEST1, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
OS_FQDN                             FIRST     *   ;  O(") CHARACTER
OS_OS                                NEXT     *   ;  O(") CHARACTER
OS_OSVERSION                         NEXT     *   ;  O(") CHARACTER
OS_VIRTUAL                           NEXT     *   ;  O(") CHARACTER
OS_IP                                NEXT     *   ;  O(") CHARACTER
HDW_NODENAME                         NEXT     *   ;  O(") CHARACTER
HDW_LOCATION                         NEXT     *   ;  O(") CHARACTER
HDW_PRODUCER                         NEXT     *   ;  O(") CHARACTER
ALIAS_FQDN                           NEXT     *   ;  O(") CHARACTER
HDW_MODEL                            NEXT     *   ;  O(") CHARACTER
ALIAS_IP                             NEXT     *   ;  O(") CHARACTER
ALIAS_NETWORKADDRESSTYPE             NEXT     *   ;  O(") CHARACTER
HWLIFECYCLE                          NEXT     *   ;  O(") CHARACTER
MAINTAINER                           NEXT     *   ;  O(") CHARACTER
HOSTNAME                                                  EXPRESSION
    SQL string for column : "SUBSTR (LOWER (:OS_FQDN), 1, INSTR (LOWER (:OS_FQDN), '.') - 1))"

Record 1: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 2: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 3: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 4: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 5: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 6: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 7: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 8: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 9: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 10: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 11: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 12: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 13: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 14: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 15: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 16: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 17: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 18: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 19: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 20: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 21: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 22: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 23: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 24: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 25: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 26: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 27: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 28: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 29: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 30: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 31: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 32: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 33: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 34: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 35: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 36: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 37: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 38: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 39: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 40: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 41: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 42: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 43: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 44: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 45: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 46: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 47: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 48: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 49: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 50: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended

Record 51: Rejected - Error on table MXONLINE_TEST1, column HOSTNAME.
ORA-00933: SQL command not properly ended


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table MXONLINE_TEST1:
  0 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 231168 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            64
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Wed Nov 13 11:50:21 2013
Run ended on Wed Nov 13 11:50:21 2013

Elapsed time was:     00:00:00.28
CPU time was:         00:00:00.09

Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600867 is a reply to message #600856] Wed, 13 November 2013 05:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
See whether this helps.

Table:
SQL> create table test
  2  (id number,
  3   os_fqdn varchar2(20),
  4   hostname varchar2(20));

Table created.


Control file:
load data
infile *
into table test
replace
fields terminated by ','
trailing nullcols
  (
   id,
   os_fqdn,
   hostname EXPRESSION "substr(:os_fqdn, instr(:os_fqdn, '-') + 1)"
  )

begindata
100,foot-little
101,foot-big


Loading session:
SQL> $sqlldr scott/Tiger@ora10 control=test10.ctl log=test10.log

SQL*Loader: Release 11.2.0.2.0 - Production on Sri Stu 13 12:52:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2

SQL> select * From test;

        ID OS_FQDN              HOSTNAME
---------- -------------------- --------------------
       100 foot-little          little
       101 foot-big             big

SQL>
Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600907 is a reply to message #600856] Wed, 13 November 2013 12:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You got rid of the ; that didn't belong, but you still have an extra ) that doesn't belong.

"SUBSTR (LOWER (:OS_FQDN), 1, INSTR (LOWER (:OS_FQDN), '.') - 1))"

should be

"SUBSTR (LOWER (:OS_FQDN), 1, INSTR (LOWER (:OS_FQDN), '.') - 1)"

[Updated on: Wed, 13 November 2013 12:56]

Report message to a moderator

Re: Transforming Data During a Load (SQL*Loader) with SQL function [message #600946 is a reply to message #600907] Thu, 14 November 2013 02:57 Go to previous message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
thank you Barbara. That fixed it.
Previous Topic: Sql Loader
Next Topic: unix command from oracle
Goto Forum:
  


Current Time: Fri Mar 29 00:53:57 CDT 2024