Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2538
  • Last Modified:

adding records to a table

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
tlcvetan
Asked:
tlcvetan
1 Solution
 
pennnnCommented:
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
 
SDuttaCommented:
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
 
tlcvetanAuthor Commented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now