Sponsored Content
Top Forums Shell Programming and Scripting How to read a dynamically changing file and load into Oracle? Post 302074500 by tmarikle on Tuesday 23rd of May 2006 04:21:17 PM
Old 05-23-2006
By the way, if you don't want to use an external table, you can still use sqlldr. You need a controlfile as follows:

Code:
LOAD DATA

INTO TABLE "MY_EXTERNAL_TABLE"
APPEND

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
(
my_key_no "SUBSTRB(:my_key_no, 1, 45)"
,load_date "TO_DATE(:load_date, 'YYYYMMDD')"
,col_a "SUBSTRB(:col_a , 1, 30)"
,col_b "SUBSTRB(:col_a , 1, 30)"
,col_c "SUBSTRB(:col_a , 1, 30)"
,col_d "SUBSTRB(:col_a , 1, 30)"
,col_e "SUBSTRB(:col_a , 1, 30)"
,col_f "SUBSTRB(:col_a , 1, 30)"
)

Then you can script your sqlldr call in Unix as follows:

Code:
sqlldr user/password control=test.ctl data=test.dat

Any column missing will be set to null. This is accompished in the "fields terminated by" statement in the control file.

Please note that this example assume CSV not tab delimited. Tab delimited use this syntax:

Code:
FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS

 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

How to load comma seperated values file (*.csv) into Oracle table

Hi all I need to input values in a .csv file into my Oracle table running in Unix, I wonder what would be the command to do so... The values are recorded in an excel file and I tried using a formatted text file to do so but failed because one of the field is simply too large to fit in the... (4 Replies)
Discussion started by: handynas
4 Replies

2. Shell Programming and Scripting

load a data from text file into a oracle table

Hi all, I have a data like, 0,R001,2,D this wants to be loaded into a oracle database table. Pl let me know how this has to be done. Thanks in advance (2 Replies)
Discussion started by: raji35
2 Replies

3. Shell Programming and Scripting

Load data from a flat file to oracle.

I have a flat file with records like Header 123 James Williams Finance2000 124 Pete Pete HR 1500 125 PatrickHeather Engg 3000 Footer The structure is: Eno:4 characters Name:8 characters Surname : 9 characters Dept:7 characters Sal:4characters These are sample... (1 Reply)
Discussion started by: Shivdatta
1 Replies

4. Shell Programming and Scripting

Can't load external file from unix into Oracle DB

Hi all, I'm traying to run a script on Unix with in it sql +. In this script I want to load a externall file that stand on my unix system into a tmp table of a oracle db, but for some reason it won't work. Find below the script. #!/bin/ksh ORACLE_SID=db1... (7 Replies)
Discussion started by: Tuut-Tuut
7 Replies

5. Shell Programming and Scripting

Check value of load balance dynamically

Hi All, I want to check load balance(or CPU utilzation) on server dynamically based on that value i want to perform some operation . Means if load balance is <10 then start server . Am using prstat -a 1 commadn it is listing CPU utilzation/load balancing dynamically .. How to take this... (7 Replies)
Discussion started by: vivek1489
7 Replies

6. UNIX for Dummies Questions & Answers

Read a file dynamically

Hi my requriment is read the file name dynamically my code is #!/bin/sh file="/c/work/loan/" Header_Trailer_move() { sed '1d;$d' $file| cat >sam.txt } Header_Trailer_move in above given path my list of files or there i have to read file dyanamically when i entered particular file name... (2 Replies)
Discussion started by: sgoud
2 Replies

7. Shell Programming and Scripting

How to read a dynamically changing file

I want to read a constantly changing file and do some operation on text found in that file. Actually that is log file of linux system and whenever i find a matching string in that file i want to create a text file with timestamp. is it possible to read that file? here is sample output of log... (7 Replies)
Discussion started by: kashif.live
7 Replies

8. Shell Programming and Scripting

Script to load daily average I/O stats from a .ksh file into Oracle db

Hi can anyone help me with a script to load output of the .ksh file into an Oracle database. I have attached sample output of the information that i need to load to the database (2 Replies)
Discussion started by: LucyYani
2 Replies

9. Shell Programming and Scripting

Parameterizing to dynamically generate the extract file from Oracle table using Shell Script

I have below 2 requirements for parameterize the generate the extract file from Oracle table using Shell Script. Could you please help me by modifying the script and show me how to execute it. First Requirement: I have a requirement where I need to parameterize to generate one... (0 Replies)
Discussion started by: hareshvikram
0 Replies

10. Shell Programming and Scripting

Script to load XML file to Oracle table

Experts. I have created a oracle table as below. create table xml_tab ( File_No number , File_content Varchar2(2000), file_type xmltype ); Daily we are receiving many XML files as below. here is our sample xml file. File1 : (7 Replies)
Discussion started by: vasuvv
7 Replies
Apache::Session::Oracle(3pm)				User Contributed Perl Documentation			      Apache::Session::Oracle(3pm)

NAME
Apache::Session::Oracle - An implementation of Apache::Session SYNOPSIS
use Apache::Session::Oracle; #if you want Apache::Session to open new DB handles: tie %hash, 'Apache::Session::Oracle', $id, { DataSource => 'dbi:Oracle:sessions', UserName => $db_user, Password => $db_pass, Commit => 1 }; #or, if your handles are already opened: tie %hash, 'Apache::Session::Oracle', $id, { Handle => $dbh, Commit => 1 }; DESCRIPTION
This module is an implementation of Apache::Session. It uses the Oracle backing store and no locking. See the example, and the documentation for Apache::Session::Store::Oracle for more details. USAGE
The special Apache::Session argument for this module is Commit. You MUST provide the Commit argument, which instructs this module to either commit the transaction when it is finished, or to simply do nothing. This feature is provided so that this module will not have adverse interactions with your local transaction policy, nor your local database handle caching policy. The argument is mandatory in order to make you think about this problem. This module also respects the LongReadLen argument, which specifies the maximum size of the session object. If not specified, the default maximum is 8 KB. AUTHOR
This module was written by Jeffrey William Baker <jwbaker@acm.org>. SEE ALSO
Apache::Session::File, Apache::Session::Flex, Apache::Session::DB_File, Apache::Session::Postgres, Apache::Session perl v5.10.1 2010-10-18 Apache::Session::Oracle(3pm)
All times are GMT -4. The time now is 08:13 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy