In a previous post I went through the steps necessary for connecting to an Oracle Cloud database using SQL*Plus. Shortly after this, I had need to upload data to a table in my Oracle Cloud database. So, as most of the necessary software was already configured, I decided to use SQL*Loader.
The first thing I needed to do was to download the Instant Client Tools package from the Oracle web site ( Instant Client for Microsoft Windows (x64) 64-bit | Oracle United Kingdom ). In fact, as my currently installed Instant Client was a few upgrades out of date, I decided to build a new Instant Client folder, so I downloaded the Basic Light Package and the SQL*Plus Package as well as the Tools Package.
When the files had finished downloading, I extracted the Basic Light Package first, followed by the SQL*Package and, finally, the Tools Package. In this case I extracted all the files to the folder "instantclient_19_11" in my "C:\Oracle" folder.
To finish off, I amended the PATH and TNS_ADMIN path variables to point to the new Instant Client folder and copied the tnsnames.ora from the old Instant Client folder to the new one. After a successful test connection to the cloud database using SQL*Plus, I was ready to do the upload with SQL*Loader.
SQL*Loader isn't something I've made much use of over the years. However, I can see how it can come in handy now and again, so it's worth being able to use, even at a basic level.
I had created a simple table in my cloud database to hold product sales data.
sa_id number(12),
sa_cust_id number(12),
sa_prod_id number(12),
sa_date date,
sa_amount number (15,2)
);
Following this, I created a PL/SQL script, using DBMS_RANDOM to generate 5000 random records of product sales data.
l_seed varchar2(100);
l_low number := 1;
l_high number;
l_year number := 2019;
lcTab varchar2(1) := chr(9);
begin
dbms_output.enable(null);
if is_leap_year(l_year) then
l_high := 366;
else
l_high := 365;
end if;
l_seed := to_char(systimestamp, 'YYYYDDMMHH24MISSFFFF');
--dbms_output.put_line('Run 1: seed=' || l_seed);
dbms_random.seed(val=>l_seed);
--dbms_output.put_line('sa_cust_id' || lcTab || 'sa_prod_id' || lcTab || 'sa_date' || lcTab || 'sa_amount');
for i in 1..5000 loop
dbms_output.put(i || lcTab);
dbms_output.put(trunc(dbms_random.value(low=>10000000, high=>19999999)) || lcTab);
dbms_output.put(trunc(dbms_random.value(low=>500000, high=>599999)) || lcTab);
dbms_output.put(to_char(date_from_day_number(trunc(dbms_random.value(low=>1, high=>l_high)),l_year), 'DD-MON-YYYY') || lcTab);
dbms_output.put_line(round(dbms_random.value(low=>1, high=>10000), 2));
end loop;
end;
The above code uses two user-defined functions. IS_LEAP_YEAR, as the name suggests, checks whether the supplied year is a leap year.
create or replace function is_leap_year (l_year in number) return boolean is
begin
case
when mod(l_year, 400) = 0 then l_result := true;
when mod(l_year, 100) = 0 then l_result := false;
when mod(l_year, 4) = 0 then l_result := true;
else l_result := false;
end case;
return l_result;
end;
