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.
Code:
#!/bin/ksh
ORACLE_SID=db1
ORACLE_HOME=/user/oracle/product/920
PATH=$PATH:$ORACLE_HOME/bin
INPUT=/tmp/input.txt
export ORACLE_SID
export ORACLE_HOME
sqlplus -s db/db@db1 << xxx
set FEEDBACK ON
set TRIMS ON
set ECHO OFF
set LINESIZE 1500
set PAGESIZE 0
set COLSEP '|'
set TERM OFF
set AUTOCOMMIT ON
whenever sqlerror exit sql.sqlcode
whenever oserror exit sql.oscode
drop table my_table_tmp;
create table my_table_tmp (
field1 VARCHAR2(10),
field2 VARCHAR2(10),
field3 VARCHAR2(15)
) tablespace USERS;
INSERT INTO my_table_tmp
VALUES ('field1', 'field2', 'field3')
FROM $INPUT
commit;
exit
xxx
Last edited by pludi; 11-23-2009 at 04:26 PM..
Reason: code tags, please...
SP2-0734: unknown command beginning "BULK INSER..." - rest of line ignored.
SP2-0734: unknown command beginning "FROM '"(/t..." - rest of line ignored.
WITH (fieldterminator= '|', roterminator = '\n')
So I have changed some things and this is where I'm now:
Code:
#!/bin/ksh
ORACLE_SID=db1
ORACLE_HOME=/user/oracle/product/920
PATH=$PATH:$ORACLE_HOME/bin
INPUT=/tmp/input.txt
export ORACLE_SID
export ORACLE_HOME
sqlplus -s db/db@db1 << xxx
set FEEDBACK ON
set TRIMS ON
set ECHO OFF
set LINESIZE 1500
set PAGESIZE 0
set COLSEP '|'
set TERM OFF
whenever sqlerror exit sql.sqlcode
whenever oserror exit sql.oscode
drop table my_table_tmp;
create table my_table_tmp (
field1 VARCHAR2(10),
field2 VARCHAR2(10),
field3 VARCHAR2(15)
) tablespace USERS;
INSERT INTO my_table_tmp
VALUES ('field1', 'field2', 'field3')
FROM $INPUT
WITH (fieldterminator= '|', roterminator = '\n')
commit;
exit
xxx
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"
$
$
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)