Home » RDBMS Server » Server Utilities » sql loader control file+ Unix scripting (Oracle 11.2.0.2.0)
sql loader control file+ Unix scripting [message #623614] Thu, 11 September 2014 22:01 Go to next message
f150
Messages: 13
Registered: September 2014
Junior Member
Hello Friends,
I am completely new to Oracle db and Unix scripting.

I have a task of making a process which can pick up multiple files from specific folder, process them and load them into Oracle db table via SQL-LOADER.
My source files are .csv format excel spreadsheet of up to 700MB size.

(1.) the logic I can not figure out is to compare a column from incoming file to my oracle table partition(column name is partition ID), if it matches that partition then it needs to be dropped . if it does not match than create a new partition based on that column and load the data.
(2.) populate the column "File_Name" from which the data is being loaded in to table.

I looked over in other forums they indicated to make a batch file containing sqlldr+ username/password+ control file+ log file + bad file. and asked to manually write a control file containing my data mapping and script for (1) and (2).

I have been instructed to use Unix shell script to invoke SQL-LOADER (using putty) for the process. but I don't know how to do Unix coding which can process/pass each file one by one to SQL-LOADER.

please help me out on this, let me know I am missing any details or specifications.
Re: sql loader control file+ Unix scripting [message #623616 is a reply to message #623614] Thu, 11 September 2014 22:16 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

>but I don't know how to do Unix coding which can process/pass each file one by one to SQL-LOADER.
You need to inform you team leader that this task needs to be assigned somebody who knows more than you.
Re: sql loader control file+ Unix scripting [message #623619 is a reply to message #623616] Thu, 11 September 2014 22:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>(2.) populate the column "File_Name" from which the data is being loaded in to table.
http://www.orafaq.com/forum/mv/msg/12234/35341/#msg_35341


>>(1.) ...compare a column from incoming file to my oracle table partition(column name is partition ID)...
Not sure what you mean/need.
Your "partitioning" strategy is not clear to me.

>>I have a task of making a process which can pick up multiple files from specific folder
http://www.orafaq.com/forum/mv/msg/27068/74127/#msg_74127
Re: sql loader control file+ Unix scripting [message #623677 is a reply to message #623619] Fri, 12 September 2014 09:19 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
Hello Mahesh, thank you for the reply.
partition in table are based on a column value of my incoming (source file) data, as shown below

MODEL YEAR TYPE
focus 2013 se
focus 2014 s
focus 2012 se
focus 2013 h

so in table partitions are based upon 'MODEL', what I am expecting is my script would compare value 'focus' in oracle table. if partition 'focus' exist in table then drop it and load new data, if partition does not exist then make new partition and then load the data.
hope I made it clear to understand.
thank you
Re: sql loader control file+ Unix scripting [message #623678 is a reply to message #623677] Fri, 12 September 2014 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
post CREATE TABLE statement for the target table

post full results from SQL below

SELECT * FROM V$VERSION;
Re: sql loader control file+ Unix scripting [message #623681 is a reply to message #623678] Fri, 12 September 2014 09:45 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
Hi,

CREATE
TABLE schema.YearInventory
(
Model NUMBER ,
Year NUMBER ,
Type VARCHAR2 (10 BYTE),
)
LOGGING TABLESPACE "USER_DATA" PCTFREE 10 INITRANS 1 STORAGE
(
INITIAL 64 NEXT 64 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARTITION BY LIST
(
MODEL
)
(
PARTITION MODEL_FOCUS VALUES
(
'FOCUS'
)

SELECT * FROM V$VERSION;

1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2 PL/SQL Release 11.2.0.3.0 - Production
3 "CORE 11.2.0.3.0 Production"
4 TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
5 NLSRTL Version 11.2.0.3.0 - Production
Re: sql loader control file+ Unix scripting [message #623682 is a reply to message #623681] Fri, 12 September 2014 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
It is a BAD, Bad, bad "design" to include application data, like model name, as part of object (table, partition, or column) name.
It violates Third Normal Form.
It results in inflexible SQL & folks then revert to dynamic SQL to work around the initial design flaw
Re: sql loader control file+ Unix scripting [message #623691 is a reply to message #623682] Fri, 12 September 2014 10:54 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
Hi,BlackSwan
how do you suggest it should be ?
any way to workaround it?
Re: sql loader control file+ Unix scripting [message #623692 is a reply to message #623691] Fri, 12 September 2014 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
what is justification for having table YEARINVENTORY as partitioned table?
Any table should only be partitioned to solve a specific problem.
So what problem does a partitioned YEARINVENTORY table solve?
Re: sql loader control file+ Unix scripting [message #623694 is a reply to message #623692] Fri, 12 September 2014 12:25 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
the table has hundreds of other partitions,and as per my best understanding other team runs query on table and retrieve the data. now the table needs to be updated with most recent data (based upon partition).
Re: sql loader control file+ Unix scripting [message #623700 is a reply to message #623694] Fri, 12 September 2014 12:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As BlackSwan said,
this is a bad design.
And posted DDL will not work.
Your columns are defined as number and list is based/search on character.
  1   CREATE  TABLE YearInventory
  2   (
  3   Model NUMBER ,
  4   Year NUMBER ,
  5   Type VARCHAR2 (10 BYTE)
  6   )
  7   PARTITION BY LIST (MODEL)
  8*  ( PARTITION MODEL_FOCUS VALUES  ('FOCUS'))
SQL> /
 ( PARTITION MODEL_FOCUS VALUES  ('FOCUS'))
                                  *
ERROR at line 8:
ORA-01722: invalid number
Re: sql loader control file+ Unix scripting [message #623703 is a reply to message #623700] Fri, 12 September 2014 13:17 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
thank you for the reply Mahesh, I will change the property of the first column to VARCHAR 2(10 BYTES). it was really silly mistake, I am assuming upon changing it will work
Re: sql loader control file+ Unix scripting [message #623704 is a reply to message #623703] Fri, 12 September 2014 13:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Something like this. Make it fancy to fit your needs.
##
## read the source file.
## Look for the first value. Process one-by-one.
##
oracle@kanada#cat somescript
awk -F" " '{print$1}' source.dat | sort | uniq | while read pname
do
sq=`sqlplus -s dbadmin/xx@xx <<EOF
set head off
select count(partition_name) from user_tab_partitions where table_name ='T1' and partition_name like (upper('MODEL_$pname'));
exit;
EOF`
if [ "$sq" -eq 1 ]; then
        echo "Partition $pname found. Do whatever you want";
else
        echo "Partition $pname notfound. ";
fi
done
##
## Sample source file
##
oracle@kanada#cat source.dat
focus 2013 se
focus 2014 s
focus 2012 se
focus 2013 h
something 2013 som
this 2014 that


##sample run
oracle@kanada#./somescript
Partition focus found. Do whatever you want
Partition something notfound.
Partition this notfound.

[Updated on: Fri, 12 September 2014 13:59]

Report message to a moderator

Re: sql loader control file+ Unix scripting [message #623720 is a reply to message #623704] Sat, 13 September 2014 01:23 Go to previous message
f150
Messages: 13
Registered: September 2014
Junior Member
thank you a ton Mahesh Smile
Previous Topic: TKPROF output
Next Topic: Parallel IMPDP not working, Jobs spawning only a single Worker Process
Goto Forum:
  


Current Time: Mon Oct 21 03:19:42 CDT 2019