- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- oracle sqlldr question
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 01:27 AM
тАО01-23-2004 01:27 AM
oracle sqlldr question
We are currently loading CSV flat files data to an oracle 9i DB table via sqlldr using the direct parallel method.This
is done using a unix script included in cron. What we want to do is create an additional table that stores
a record of all the flat files loaded, does anyone know if/how to do this within sqlldr or is there a more
optimal way? Please advise
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 01:36 AM
тАО01-23-2004 01:36 AM
Re: oracle sqlldr question
A bit of scripting is required, but this should not be that difficult.
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 01:41 AM
тАО01-23-2004 01:41 AM
Re: oracle sqlldr question
Could you help out with the source code for what you suggested?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 01:44 AM
тАО01-23-2004 01:44 AM
Re: oracle sqlldr question
Since you're using 9i you could be using external tables rather than sqlldr, but again I think you are looking at scripting something in pl/sql to do what you want.
-- Graham
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 01:55 AM
тАО01-23-2004 01:55 AM
Re: oracle sqlldr question
example
#!/bin/sh
logfile=$1
inputfile=$(grep ^"Data File:" $logfile | awk '{print $3;}')
nbloaded=$(grep "successfully loaded" $logfile | awk '{print $1;}')
echo table "|" $inputfile "|" $nbloaded
========
replace table with your tablename. (I usually have it in the log file name).
My example is based on a simple sqlldr (one table only , no condition) but it gives an idea of what you can build.
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 01:57 AM
тАО01-23-2004 01:57 AM
Re: oracle sqlldr question
Thanks for the feedback! How would you propose I script the PL/SQL code?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 09:38 AM
тАО01-23-2004 09:38 AM
Re: oracle sqlldr question
Do you want information from the logfiles, or just a flatfile?
In a unix shell script, you can just do the following:
sqlplus user/pass <> /dev/null
insert into loadtable values (sysdate,$LOADFILE);
commit;
exit;
!
You could get more advanced than this, but this would just be a date that it was inserted, and the filename (assuming that LOADFILE is the internal name for the file). If you want to load extra data (recommended) then follow Jean-Luc's example to get the information out of the log file, and load it in as extra fields.
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-23-2004 03:41 PM
тАО01-23-2004 03:41 PM
Re: oracle sqlldr question
If I understand your problem correctly, you need to create a control file(loadcsd.ctl) to describe the format of the record as below:
-----------
load data
infile '/u01/app/oracle/local/tmp/loadcsd.dat'
append
into table CSD_tab
fields terminated by ','
(mon_date date(24) 'Dy Mon DD HH24:MI:SS YYYY', db_name, col1, col2)
-----------
Another cron script run to load the above data into the CSD_tab:
-----------
datfile=$TMPDIR/loadcsd.dat
#
if test -s $datfile
then
# Load data to ORACLE database
$ORACLE_HOME/bin/sqlldr $ORAUSER/$ORAPASS@$ORACLE_SID control=$TMPDIR/loadcsd.ctl LOG=$TMPDIR/loadcsd.log BAD=$TMPDIR/loadcsd.bad
fi
-----------
Hope this can help.
Good Luck
twang