Home » RDBMS Server » Server Utilities » Issue with SQL LOADER Control file [merged two by jd]
icon1.gif  Issue with SQL LOADER Control file [merged two by jd] [message #668868] Tue, 20 March 2018 02:33 Go to next message
sp_siva
Messages: 3
Registered: March 2018
Junior Member
I have a issue with SQL Control file

Here is my feed file which I am trying to load into oracle table using sqlldr
No|Name|ID|Location|value
12|simple||Singapore|gold
34|barate|TEST|London|siler
45|sdfsdfs||America|diamond

Now the requirement on the column 3 (ID)
Option#1 ==> if the column3 (ID) is empty and then we can update the oracle sequence number --> its working with with below codes
ID "Sequence.NEXTVAL",

Option#2 ==> if the column3 (ID) is having some values for few records and then we can skip the values and update the oracle sequence number --> its not working

The output would be like this, where 123,124,125 is the sequence no Mad

No|Name|ID|Location|value
12|simple|123|Singapore|gold
34|barate|124|London|siler Mad
45|sdfsdfs|125|America|diamond

Kindly help me out on this.
how to add tow condition on SQL loader control file column [message #668869 is a reply to message #668868] Tue, 20 March 2018 02:46 Go to previous messageGo to next message
sp_siva
Messages: 3
Registered: March 2018
Junior Member
Hi all,

I have a test file and control file to load into oracle table

one column I want to add oracle sequence no if its null/empty/any values

I tried to update the NVL function to update the sequence number if the column is empty its working
but the sequence number is not updated if the column is having any values

how can I add to function on one column

for example

EMP_ID "NVL((:EMPID,sequence.nextval)") --> its not working if EMP_ID column is having some values
its working if REC_ID column is completely empty

Kindly help me on this
Re: Issue with SQL LOADER Control file [message #668880 is a reply to message #668868] Tue, 20 March 2018 08:03 Go to previous messageGo to next message
joy_division
Messages: 4866
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe showing us your control file would be a good start.

Quote:
having some values for few records and then we can skip the values
How is Oracle supposed to define what a few records are?
Re: Issue with SQL LOADER Control file [message #668881 is a reply to message #668880] Tue, 20 March 2018 08:10 Go to previous messageGo to next message
joy_division
Messages: 4866
Registered: February 2005
Location: East Coast USA
Senior Member
ok, I see now you posted the same question with different wording under a different heading.

Just leave out the NVL and set the column to the sequence.
Re: Issue with SQL LOADER Control file [message #668920 is a reply to message #668881] Thu, 22 March 2018 23:25 Go to previous messageGo to next message
sp_siva
Messages: 3
Registered: March 2018
Junior Member
Hi Joy,
Seuqnce no is working if the column (EM_ID) is empty in the feed file with below command
EMB_ID "sequence_name.nextval

But if the column (EMP_ID) has any values in the feed file then the sequence is skipping. Nothing is loading

I want to update the sequence values in the EM_ID column in both conditions (either feed file have values or not having values in EMP_ID field)

I need the suggessions

Thanks!
Re: Issue with SQL LOADER Control file [message #668928 is a reply to message #668920] Fri, 23 March 2018 13:59 Go to previous message
Barbara Boehmer
Messages: 8856
Registered: November 2002
Location: California, USA
Senior Member
What joy division suggested does work, as demonstrated below.

SCOTT@orcl_12.1.0.2.0> host type feed_file.dat
No|Name|ID|Location|value
12|simple||Singapore|gold
34|barate|TEST|London|siler
45|sdfsdfs||America|diamond

SCOTT@orcl_12.1.0.2.0> host type test.ctl
load data
infile 'feed_file.dat'
into table oracle_table
fields terminated by '|' trailing nullcols
( no, name
, id "sequence.nextval"
, location, value)

SCOTT@orcl_12.1.0.2.0> create table oracle_table
  2    (no        number,
  3     name      varchar2(10),
  4     id        number,
  5     location  varchar2(10),
  6     value     varchar2(10))
  7  /

Table created.

SCOTT@orcl_12.1.0.2.0> create sequence sequence
  2  /

Sequence created.

SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Mar 23 11:56:33 2018

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

Path used:      Conventional
Commit point reached - logical record count 4

Table ORACLE_TABLE:
  3 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl_12.1.0.2.0> select * from oracle_table
  2  /

        NO NAME               ID LOCATION   VALUE
---------- ---------- ---------- ---------- ----------
        12 simple              3 Singapore  gold
        34 barate              4 London     siler
        45 sdfsdfs             5 America    diamond

3 rows selected.
Previous Topic: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause
Next Topic: Moving entire DB
Goto Forum:
  


Current Time: Wed Jul 18 03:59:59 CDT 2018