Passing arrays to oracle from unix


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing arrays to oracle from unix
# 1  
Old 04-04-2006
Power Passing arrays to oracle from unix

Hi all...

Im looking to pass the contents of a simple file to Oracle so that it can be stored in a database table.

The best way i can think of to avoid overhead is to loop through the contents of the file and store the data in a bash array. then the array can be passed to SQL Plus where Oracle can loop through the array and write the data away.

Is this a good way of doing it? if it is...does anyone have an exmple of how to pass an array from the bash shell to oracle

eg simple file called name.txt that contains the following..
tom
dick
harry

any help on this would be reatly appreciated?

This can be done by looping through lines in the file and for each line open up an oracle connection to write the data away eg but tis is expensive performance wise.

loop through file contents
"insert into table values variable1, variable2 ect| sqlplus -s scott/tiger
# 2  
Old 04-04-2006
look into Oracle 'sqlldr'
# 3  
Old 04-04-2006
sqlldr is the best choice - vgersh99 is right on top of that.

Otherwise learn about the UTL_FILE package. And learn some PL/SQL.

Sometimes I've used an editor to turn a small flat file into several dozen insert statements, when the data is in columns and my editor supports macros. Turn
Code:
John Doe USA
-- into 
insert into mytable values ( 'John', 'Doe', 'USA');
............
commit;


Last edited by vgersh99; 04-05-2006 at 10:08 AM..
# 4  
Old 04-05-2006
Cant use sql loader or UTL File

Hi Guys, thanks for the sugesstion but i am already using SQL loader to load the contents of a csv file into Oracle and I can't use UTL FIle as That can only be used to manipulate files located on the same server as the database. I am working with files on the applications server which is different to the database server in our environment.

The file contents I want to load into Oracle is actually the LOG file produced by SQL Loader and this file is not of any particulat format hence the use of arrays to pass it to Oracle in 1 go.

It would be good to have an example of passing a bash array into oracle as reference... pretty pleaseeeee Smilie
# 5  
Old 04-05-2006
passing shell array to oracle???????

Dosnt anyone have an example of how to pass a Bash array to to Oracle?? Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing variable from file to Oracle

cat a1 scott robert tom test script : #!/usr/bin/ksh for NAME in `cat a1` do VALUE=`sqlplus -silent "nobody/bobody01@testq" <<END set pagesize 0 feedback off verify off heading off echo off select username from dba_users where username=upper('$NAME'); END` if ; then echo... (3 Replies)
Discussion started by: jhonnyrip
3 Replies

2. Programming

Oracle Variable Passing Test

Hi, I am trying to get the oracle variables and pass the values in sql placed in procedure. VARIABLE vstat='ASDS,FGDS,VCGD,VCXC' Query : select distinct dept from College where section in ('C','D') AND CODES ='' AND NAMES IN ('RAJ','SAM'); I want CODES values to be taken from vstat... (1 Reply)
Discussion started by: Perlbaby
1 Replies

3. Shell Programming and Scripting

Passing unix variable to oracle parameters

Please help me how to pass some unix vairable to oracle. I have used below , but not displaying passed (inval) value. calling() { sqlplus -s $1/$2@$3 <<EOF begin exec call_sql($4); end; exit EOF } calling user pwd inst value1... (17 Replies)
Discussion started by: Jairaj
17 Replies

4. UNIX for Dummies Questions & Answers

Help Passing An Oracle parameter to a unix shell.

I have an Oracle concurrent program that I'm passing a parameter to a unix shell script. An example value of the Oracle parameter is PO_TOP. The Oracle parameter represents the unix env var PO_TOP, meaning, on the unix side there is env var called PO_TOP (ex value: /oradev/apps/po/11.0.3/). My... (7 Replies)
Discussion started by: Mark_Wright
7 Replies

5. Shell Programming and Scripting

passing variable to oracle procedure

using the script below I want to pass a parameters thorugh my sql call(@/unixsxxx/xxxx/helpenv.sql emptab ) as input into an oracle procedure xxxx_package.proc1(%1,emptab); . I tried %1 but it does not work. Any suggestions. #!bin/ksh set -x # export... (0 Replies)
Discussion started by: TimHortons
0 Replies

6. Shell Programming and Scripting

Passing arrays between functions

Hi, I have a function that hold 3 arrayies. I need to pass them to another function as an input, for further use Could you please explain how to do that. Thanks (5 Replies)
Discussion started by: yoavbe
5 Replies

7. Shell Programming and Scripting

Passing parameters form unix to Oracle procedure

Hi, I have screen which was desined in PL/SQL Catridges in apps. In that screen some enterable fields these values r the passing parameters to create value sets, functions, menus etc in apps by using front end screens. Now in that screen i have a button. when i click that button it have to... (0 Replies)
Discussion started by: rajasekharamy
0 Replies

8. Shell Programming and Scripting

passing oracle parameters back to Shell

Hi All, Does anyone have any solutions for passing back multiple variables back to the SHELL from a call to an ORACLE procedure: eg #username='scott' #password='tiger' #database='orcl' username='ITGCD03D03' password='tC5epIew' database='ITGCD03D' sqlplus -s... (4 Replies)
Discussion started by: satnamx
4 Replies

9. Shell Programming and Scripting

Shell arrays in oracle stored procedure

Is it possible to pass unix shell arrays in Oracle stored procedure? Is yes, how? Thanks (6 Replies)
Discussion started by: superprogrammer
6 Replies

10. Shell Programming and Scripting

Passing arrays to oracle from unix

Hi all Iam trying to send an array to oracle procedure from unix. Iam writing a program in K Shell to pass this array to oracle. Is it possible. Please advice thanks Krishna (7 Replies)
Discussion started by: krishnasai
7 Replies
Login or Register to Ask a Question