?
Solved

Loading a database sequence into a column during sql loader

Posted on 2004-08-27
18
Medium Priority
?
2,563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

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
 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this article, we’ll look at how to deploy ProxySQL.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

770 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