Link to home
Start Free TrialLog in
Avatar of sumanth_ora
sumanth_ora

asked on

Loading a database sequence into a column during sql loader

Hi,

  I want to load the NEXTVAL of the sequence stored in that database into a column of the table iam loading using sql loader.

   The seq name is:
   
   conv_seq.


   The column name is seq_no. So, I was trying this :

LOAD DATA
INFILE 'imptree.csv'
TRUNCATE INTO TABLE conv
FIELDS TERMINATED BY ","
(imp "trim(:imp)",
tree "trim(:tree)",
SEQ_NO "select conv_seq.nextval from dual"
)


    Tried all the combinations i could. didn't work. so, asking for your help.  
Avatar of sapnam
sapnam

Replace the

SEQ_NO "select conv_seq.nextval from dual"
with
SEQ_NO "conv_seq.nextval"
Avatar of sumanth_ora

ASKER

I tried that but somehow it was loading the column with the null rows.

  also tried

SEQ_NO conv_seq.nextval

   too. This one was giving me error.

Check your sequence.  What do you get when you

select min_value, max_value, increment_by, cache_size, last_number, cycle_flag
from dba_sequences
where sequence_name = 'CONV_SEQ';

If last_number is within cache_size of max_value and cycle_flag is N, you need to rebuild your sequence.  

This was what I had in the control file:
LOAD DATA......

(
SEQ_NO fin_data_seq.nextval
)

    And this is the error I am getting.


SQL*Loader-350: Syntax error at line 37.
Expecting "," or ")", found "conv.seq.nextval".
SEQ_NO conv_seq.nextval


   But when I have this:


LOAD DATA......

(
SEQ_NO "fin_data_seq.nextval"
)


  then, no error but that column SEQ_NO is all nulls after the load;


>>If last_number is within cache_size of max_value and cycle_flag is N, you need to rebuild your sequence.  

   What do you mean?

   THis is what I have :

 
max_value = 999999999999999999999999999
increment_by 1
cycle_flag N
order_flag Y
cache_size 20
last_number 0
min_value 0

    Now, what should I do?

Let us take this step by step.  Some of this you may have done earlier plz do it again
1. Please issue the following SQL command
select fin_data_seq.nextval from dual;

and post the result

2.  What is the table structure

3.  What type of data is there in the CSV file.  Can you post a few sample records

4. Why are you using a function trim while loading.  Just try a simple load and see what happens ?

In my earlier post I had said

SEQ_NO "select conv_seq.nextval from dual"
with
SEQ_NO "conv_seq.nextval"

that should be
SEQ_NO "fin_seq.nextval"
Does the table have any  constraint on the field SEQ_NO
1 .   select fin_data_seq.nextval from dual;   15


2.   The desc of the table column is:

SEQ_NO                                    NUMBER

3. The CSV file contains all types of data.

4. I don't have any trouble loading the other columns. This is the only column that is giving me a problem.

   Anyway, I am trimming the fields so that I don't have any leading or trailing nulls.

   
5. No, the table doesn't have any constraint at all on any of the columns. This is just a staging table.


(Don't worry about the name of the sequence. CONV_SEQ (same as fin_data_seq....)is the name I used to put in EE. But apparently, I had forgot to do the same change in one of my posts).
 
Another issue I have seen is when the ctl file fields do not match the order of the table.  If there are additional comma's (,) within the fields (i.e. if #'s are stored as 1,000.00 instead of 1000.00) the comma could throw the whole ctl off.

As previously requested, post a few sample lines. Be sure to change anything private, but don't change the type of data.

i.e. change Stephen to Xxxxxxx
and 9,672.02 to 9,999.99

Also, if the ctl file above is only partial, it would probably help to get the best answer quickly if you posted the whole thing and the full DESC of the table.

With more information, we can help better and quicker.
Okey Dokey guys,

   Here we go.

DESC fin_data.

Describing fin_data....
NAME                            Null?     Type
------------------------------- --------- -----
GLC                                       VARCHAR2(12)
BU                                        VARCHAR2(5)
BUORIG                                    VARCHAR2(5)
SERNO                                     VARCHAR2(17)
BASRCE                                    VARCHAR2(4)
AREA                                      VARCHAR2(10)
RCORIG                                    VARCHAR2(9)
RCCHRGD                                   VARCHAR2(9)
MAINACCT                                  VARCHAR2(4)
SUBACCT                                   VARCHAR2(4)
FRC                                       VARCHAR2(10)
TRANSDTE                                  DATE
JOURNAL_SRC                               VARCHAR2(7)
PACKNO                                    VARCHAR2(4)
INVOICE_NO                                VARCHAR2(13)
FUNCCODE                                  VARCHAR2(6)
HOURS                                     NUMBER(7,2)
ACTDTE                                    DATE
USERID                                    VARCHAR2(8)
EXTC                                      VARCHAR2(5)
DOLLARS                                   NUMBER(24,4)
SSN                                       VARCHAR2(9)
REG_ACCT                                  VARCHAR2(8)
ACCOUNT                                   VARCHAR2(8)
CATEGORY                                  NUMBER
KOW                                       VARCHAR2(2)
REFNO                                     VARCHAR2(10)
RUNDATE                                   DATE
SEQ_NO                                    NUMBER



Here is the sample data. The fields RUNDATE or SEQ_NO are obviously not in the feed.(The first line is the header which I am deleting before the load..Eventhough I don't delete it, it will obviously go into the bad file).  There are 25 fields actually but 28 columns I think.

one column FRC is a combination of two other fields used in the feed (KOW,CATEGORY).

LOCATION,BU,BU FROM,SERIAL NUMBER,LEGACY SRC,GEO AREA,RCO,RCC,ACCOUNT MAIN,ACCOUNT SUB,CATEGORY,KOW,TRAN-DATE,PACK ORIGIN,PACK CNTL,REFERENCE ID,FUNC CODE,HOURS,ACCOUNTING DATE,OPRID,EXTC CD,AMOUNT,SSN,REG ACCOUNT,ACCOUNT

7000000   ,ME   ,ME   ,                 ,B50 , ,A00000000,A05A04000,4120,3000,     ,  ,2004-07-19,       ,0000,CSJLJP0004,5DQ
U  ,0000000000000.00 ,2004-07-31,BATK10Y ,CY1  ,0000000100000.00-,         ,41203290,41203290,

7000000   ,ME   ,ME   ,                 ,B50 , ,A00000000,A05A04000,4120,3000,     ,  ,2004-07-19,       ,0000,CSJLJP0005,5DQ
U  ,0000000000000.00 ,2004-07-31,BATK10Y ,CY1  ,0000000018000.00-,         ,41203290,41203290,

7000000   ,ME   ,ME   ,                 ,B50 , ,A00000000,A05A04000,4120,3000,     ,  ,2004-07-19,       ,0000,CSJLJP0006,5DQ
U  ,0000000000000.00 ,2004-07-31,BATK10Y ,CY1  ,0000000001100.00-,         ,41203290,41203290,

3105006   ,RI   ,RI   ,04070223051036369,H0  , ,A05A0A1E0,A05A0A1E0,2121,1000,60   ,C ,2004-07-02,       ,0000,          ,C01
0  ,0000000000000.00 ,2004-07-31,APBATCH ,239  ,0000000000110.88 ,999999999,21212100,21212100,

    Apparently I have put extra spacing between the rows for clarity.

and here is the CTL file that I am using .
LOAD DATA
INFILE 'fin_data.csv'
TRUNCATE INTO TABLE fin_data1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(GLC "trim(:GLC)",
BU "trim(:BU)",
BUORIG "trim(:BUORIG)",
SERNO "trim(:SERNO)",
BASRCE "trim(:BASRCE)",
AREA "trim(:AREA)",
RCORIG "trim(:RCORIG)",
RCCHRGD "trim(:RCCHRGD)",
MAINACCT "trim(:MAINACCT)",
SUBACCT "trim(:SUBACCT)",
CATEGORY "trim(:CATEGORY)",
KOW "trim(:KOW)",
TRANSDTE "to_date(:TRANSDTE,'YYYY-MM-DD')",
JOURNAL_SRC "trim(:JOURNAL_SRC)",
PACKNO "trim(:PACKNO)",
REFNO "trim(:REFNO)",
FUNCCODE "trim(:FUNCCODE)",
HOURS "to_number(replace(:HOURS,' ','+'),'9999999999999999.99S')",
ACTDTE "to_date(:ACTDTE,'YYYY-MM-DD')",
USERID "trim(:USERID)",
EXTC "trim(:EXTC)",
DOLLARS "to_number(replace(:DOLLARS,' ','+'),'9999999999999999999.99S')",
SSN "trim(:SSN)",
REG_ACCT "trim(:REG_ACCT)",
ACCOUNT "trim(:ACCOUNT)",
FRC "trim(to_char(:CATEGORY))||trim(:KOW)",
RUNDATE "trunc(sysdate)",
SEQ_NO fin_data_seq.nextval
)



Sorry guys, make that 26 fields and 29 columns.

FRC , RUNDATE and SEQ_NO are the extra one. The feed fields are:

LOCATION,
BU,
BU FROM,
SERIAL NUMBER,
LEGACY SRC,
GEO AREA,
RCO,
RCC,
ACCOUNT MAIN,
ACCOUNT SUB,
CATEGORY,
KOW,
TRAN-DATE,
PACK ORIGIN,
PACK CNTL,
REFERENCE ID,
FUNC CODE,
HOURS,
ACCOUNTING DATE,
OPRID,
EXTC CD,
AMOUNT,
SSN,
REG ACCOUNT,
ACCOUNT
Hey guys,

   I figured it out. I was doing a direct load into the table. So, it was not loading any sequence. So, now Iam doing a conventional load. It is working fine.

   Thank you for your help though.

   
Glad you got it figured out!
So, direct load is not for every situation.

  Anybody know any good links for learning about external tables? I have to learn that stuff over the long weekend I believe.

If you are a member of IOUG, I presented there 2003 and 2004 and the white paper includes a pretty good overview of External Tables.  Search for "SQL* Loader Tuning".  The 2nd half covers External Tables how and when you may want to use them.

Stephen Andert
Hi Stephen,

   Thanks for your comment. But unfortunately I don't have a IOUG membership(to tell you the truth, I just figured out that IOUG is International Oracle Users Group). So, I was wondering if you could guide me to another site.
ASKER CERTIFIED SOLUTION
Avatar of GhostMod
GhostMod
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial