Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Can't load external file from unix into Oracle DB
Top Forums Shell Programming and Scripting Can't load external file from unix into Oracle DB Post 302374234 by durden_tyler on Monday 23rd of November 2009 11:30:13 PM
Old 11-24-2009
Well, this -

Code:
INSERT INTO my_table_tmp
VALUES ('field1', 'field2', 'field3')
FROM $INPUT 
WITH (fieldterminator= '|', roterminator = '\n')

doesn't appear to be Oracle SQL syntax.

And this -

Code:
BULK INSERT <tablename>
FROM <file> ...

is SQL Server syntax.

You have a few options for loading a delimited file into Oracle.

(a) Use SQL*Loader which is a client command-line utility designed exactly for this purpose.

(b) Create an external table in Oracle based on the format of the input file. You then have complete access to the input data and can process and load it into the main table.

(c) Convert your input file into an "insert-script" i.e. a SQL script that has INSERT statements in it. You can then simply call this insert-script in sqlplus in your shell script with the syntax - "@<path_to_my_insert_script>".

If your input format is trivial, then I'd say (b) is the easiest method.

Given below is an idea for implementation of (c):

Code:
$
$ # Let's say the input file looks like this.
$ cat input.txt
a,ab,abc
b,bc,bcd
c,cd,cde
d,de,def
$
$ # And this has to modified in such a way that each line must
$ # have an "INSERT" statement that inserts the delimited data
$ # as a single row in the Oracle table. Then, you may do this -
$
$ perl -pi.bak -e "s/,/','/g;
                   s/^/insert into t\(x,y,z\) values \('/g;
                   s/$/'\);/" input.txt
$
$ # check the script
$ cat input.txt
insert into t(x,y,z) values ('a','ab','abc');
insert into t(x,y,z) values ('b','bc','bcd');
insert into t(x,y,z) values ('c','cd','cde');
insert into t(x,y,z) values ('d','de','def');
$
$ # The original data is saved in a backup file - "input.txt.bak"
$
$

HTH,
tyler_durden
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

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... (5 Replies)
Discussion started by: handynas
5 Replies

2. 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

3. Programming

How can i load or insert a table in oracle from c language thru unix environment

I'm having a oracle server and i'm having a table in that. I'm having a linux server which is in network with the oracle server. I need to write a c program in linux env when on execution loads the table with the text file given as input. Please explain me the flow of process in that and also... (6 Replies)
Discussion started by: rramprasad
6 Replies

4. Shell Programming and Scripting

How to read a dynamically changing file and load into Oracle?

I have a tab delimited file which has 27 character fields. The file needs to be loaded into an Oracle table. But the challenge is that everytime the file comes it may or may not have values in all 27 fields. Column Definition of the 27 fields: TYPE: Char (1) NAME: Char (30) CUSTOM_VAL: Char... (8 Replies)
Discussion started by: madhunk
8 Replies

5. 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

6. 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

7. Shell Programming and Scripting

load file to sharepoint using unix

I have a requirement to upload a file to the MS sharepoint site using unix or any other programming language. Does sharepoint support ftp? I heard it doesn't ...is it true...if yes is it different from what regular ftp we use....is there any other programming way out to upload the file to... (1 Reply)
Discussion started by: RubinPat
1 Replies

8. Shell Programming and Scripting

why do we need UNIX shell script to load data into Oracle database

Hello everyone, I am new to shell scripting/ loading data into a database. I want to load data into Oracle database using SQL loader. Can some one please explain why do we need unix shell script to load the data into the database? Also can someone please explain what has to be in that script?... (5 Replies)
Discussion started by: new_prog
5 Replies

9. 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

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

LEARN ABOUT OSF1

uudemon.cleanu

uudemon(4)						     Kernel Interfaces Manual							uudemon(4)

NAME

       uudemon.admin,  uudemon.cleanu,	uudemon.hour,  uudemon.poll  -	Administrative shell scripts for polling remote systems, cleaning up spool
       directories, reporting status to the system administrator, and routine invocations of the uuxqt and uusched daemons

SYNOPSIS

       These shell scripts reside in the following directory:
	  /usr/lib/uucp

DESCRIPTION

       All the scripts can be run from the command line or can be run automatically by the cron daemon. To automatically run the  scripts,  remove
       the comment character (#) from the beginning of the relevant line in the /var/spool/cron/crontabs/uucp file.

       This  script  reports  status  to  the system administrator. It issues the uustat command to find out the status of uucp jobs. It mails the
       results to the uucp login ID. The script may be modified to send mail to any login ID such as the uucp administrative login ID  (uucpa)	or
       root.  This script cleans up the /var/spool/uucp and /var/spool/uucppublic directories by running the uucleanup command. The uucleanup com-
       mand is run with the following parameters: -C7, -D7, X2, -o2, -W1.  This script runs the uusched and uuxqt daemons in the background.  This
       script  polls  the  systems  listed  in	the  /usr/lib/uucp/Poll file.  The uudemon.poll script should be scheduled before the uudemon.hour
       script. This allows uudemon.poll to create any command files before cron runs the uudemon.hour script.

FILES

       Contains the uudemon.admin, uudemon.cleanu, uudemon.hour and uudemon.poll files.  Contains the uucp file.

RELATED INFORMATION

       Commands: cron(8), uucleanup(8), uusched(8), uuxqt(1)

       Files: /usr/lib/uucp delim off

																	uudemon(4)
All times are GMT -4. The time now is 12:04 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy