?
Solved

adding records to a table

Posted on 2003-03-31
3
Medium Priority
?
2,504 Views
Last Modified: 2010-03-03
I am new to using PL/SQL and have been trying to do different things to learn how it works. I have created a script to load a table with some data, then I want to load some more data from another file I get an error message, can someone show me what I need to change so I can load more data into my temp table and maintain the sequence, so if I have four types of apples already loaded and want to load another it makes that one number 5. Below is what I am using to initially load the table and what I was trying to run to load more data. Thanks Terri

#!      /bin/ksh
#############################################################################
#       Script: TLC_fruit
#############################################################################
###     Create temp tables
        sqlplus -s login/login <<EOF >/dev/null
       
        drop table tmp_fruit;
        drop trigger tmp_fruit_lineno;
        commit;
        create table tmp_fruit
        (lineno    number(5),
        fruit   varchar2(6),
        variety   varchar2(6),
        quantity   number(5));
/
create trigger tmp_fruit_lineno
before insert on tmp_fruit
for each row
declare
thelineno number;
begin
select max(lineno) into thelineno
from tmp_fruit where fruit = :new.fruit
;
:new.lineno := nvl(thelineno,0) + 1;
end tmp_fruit_lineno;
/

EOF

### Load temp table
sqlldr camps/camps fruit.ctl fruit.log fruit.bad fruit.data rows=1

sqlplus -s login/login <<EOF >/dev/null

alter trigger tmp_fruit_lineno disable;
/

EOF

exit 0

This is my fruit.ctl file:

--      Script:   fruit.ctl
load data
infile fruit.data
into table tmp_fruit
fields terminated by ','
(
fruit,
variety,
quantity
)

Here is my data file fruit.data:

apples,apples,200
apples,gala,20
apples,reddel,50
apples,golden,150
cherry,cherry,100
cherry,bing,60
cherry,stella,10

When I run my script I get this table:

SQL> select * from tmp_fruit;

    LINENO FRUIT  VARIET   QUANTITY
---------- ------ ------ ----------
         1 apples apples        200
         2 apples gala           20
         3 apples reddel         50
         4 apples golden        150
         1 cherry cherry        100
         2 cherry bing           60
         3 cherry stella         10

Now I want to add some more data to my tmp_fruit table, so I another file data file fruit2.data:

apples,yellow,40
grapes,green,20
grapes,red,30
grapes,grapes,50
grapes,black,20
cherry,compak,70

I tried to load it using this TLC_fruit_add script and got the following error message:

#!      /bin/ksh
#############################################################################
#       Script: TLC_fruit_add
#############################################################################
###     Create temp tables

sqlplus -s login/login <<EOF >/dev/null
       
alter trigger tmp_fruit_lineno enable;
/

EOF

### Load temp table
sqlldr camps/camps fruit2.ctl fruit.log fruit.bad fruit2.data rows=1

sqlplus -s login/login <<EOF >/dev/null
       
alter trigger tmp_fruit_lineno disable;
/

EOF

exit 0

This is my fruit2.ctl file:

--      Script:   fruit2.ctl

load data
infile fruit2.data
into table tmp_fruit
fields terminated by ','
(
fruit,
variety,
quantity
)

When I run the Tmp_fruit_add script I get this error message:

SQL*Loader-601: For INSERT option, table must be empty.  Error on table TMP_FRUIT

When the additional data from fruit2.data is loaded I want my tmp_fruit table to look like this:

    LINENO FRUIT  VARIET   QUANTITY
---------- ------ ------ ----------
         1 apples apples        200
         2 apples gala           20
         3 apples reddel         50
         4 apples golden        150
         1 cherry cherry        100
         2 cherry bing           60
         3 cherry stella         10
         5 apples yellow         40
         1 grapes grapes         50
         2 grapes green          20
         3 grapes red            30
         4 grapes black          20
         4 cherry compak         70
0
Comment
Question by:tlcvetan
[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
3 Comments
 
LVL 11

Accepted Solution

by:
pennnn earned 200 total points
ID: 8240619
The default clause for the SQL*Loader is INSERT. And as the message suggests - it expects the table to be empty in order to use that option.
If you want to add new rows then you should use the APPEND clause:
--      Script:   fruit2.ctl

load data
infile fruit2.data
APPEND into table tmp_fruit
fields terminated by ','
(
fruit,
variety,
quantity
)

It's always a good idea to specify the insert/append/replace/truncate clause explicitely, instead of relying on the default clause.
Hope that helps!
0
 
LVL 10

Expert Comment

by:SDutta
ID: 8240663
Change your fruit2.ctl to

load data
infile fruit2.data
into table tmp_fruit
append
fields terminated by ','
(
fruit,
variety,
quantity
)

Then it will try to add new rows if there are already existing records in the table.

0
 

Author Comment

by:tlcvetan
ID: 8240722
Thanks for the added explaination of specifing the action to take, I did not know about the default that really makes more sense to me how that was working in the originial load too.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

800 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