Solved

Loading a database sequence into a column during sql loader

Posted on 2004-08-27
18
2,549 Views
Last Modified: 2012-05-05
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.  
0
Comment
Question by:sumanth_ora
  • 9
  • 4
  • 3
  • +1
18 Comments
 
LVL 8

Expert Comment

by:sapnam
ID: 11919061
Replace the

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

Author Comment

by:sumanth_ora
ID: 11922900
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.

0
 
LVL 4

Expert Comment

by:andertst
ID: 11924165
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.  

0
 

Author Comment

by:sumanth_ora
ID: 11932279
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;


0
 

Author Comment

by:sumanth_ora
ID: 11932716
>>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?

0
 
LVL 8

Expert Comment

by:sapnam
ID: 11932803
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"
0
 
LVL 8

Expert Comment

by:sapnam
ID: 11932875
Does the table have any  constraint on the field SEQ_NO
0
 

Author Comment

by:sumanth_ora
ID: 11933771
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).
 
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 4

Expert Comment

by:andertst
ID: 11934272
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.
0
 

Author Comment

by:sumanth_ora
ID: 11934433
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
)



0
 

Author Comment

by:sumanth_ora
ID: 11934468
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
0
 

Author Comment

by:sumanth_ora
ID: 11935815
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.

   
0
 
LVL 4

Expert Comment

by:andertst
ID: 11936039
Glad you got it figured out!
0
 

Author Comment

by:sumanth_ora
ID: 11936161
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.

0
 
LVL 4

Expert Comment

by:andertst
ID: 11936288
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
0
 

Author Comment

by:sumanth_ora
ID: 11936349
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.
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 11978694
PAQd, 500 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now