Passing string from SQL to a BASH script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing string from SQL to a BASH script
# 1  
Old 12-04-2013
Passing string from SQL to a BASH script

OS Solaris 10,
DB oracle 10g

Hello,
We currently have a BASH script that runs and moves image files from a remote server to the local db server. A snippet of the code shows that we are picking up all Images that are 'mtime -1'

Code:
some code...
for file in `ssh user@10.200.200.10 'find /u01/bea/madeup/Images -name "*.jpeg" -mtime -1'`
do
scp user@10.200.200.10:${file} /oraworking/madeup/images/orig/
done
...more code

It's simple and it works.

Now, due to development requirements we need a way of picking up those images from different directories within the base directory of /u01/bea/madeup/Images. The plan is to have an 'index' table that will hold the location (path and filename) of the image file and it is this that I would like to read from the table and somehow use in place of the hard coded file path above.
Can I Just do something like this example: (This may work with some tweaking, but it gives you an idea of what i am trying to achieve).

Code:
for file in `ssh user@10.200.200.10 'find /oracle/product/db/10gR2/bin/sqlplus -s /nolog  <<EOF  #Find is just going to try and find something in '/oracle/product/db/10gR2/bin/sqlplus' so this is wrong!

conn / as sysdba

set heading off
set feedback off
set pagesize 0
set linesize 30000
set trimout on
SELECT f.file_path||'/'||f.file_name
FROM fileupload f
INNER JOIN imagelog i
ON f.id = i.fileupload_id;'

EOF`

do
if [ -f ${file} ]
then
        echo "Skipping $file file..."
continue
fi

scp user@10.200.200.10:${file} /oraworking/madeup/images/orig/

done
...more code

Thanks in Advance,

Jonathan

Last edited by JonP; 12-04-2013 at 07:05 AM..
# 2  
Old 12-04-2013
Hi

u can first try running the sql and spool required fields or coulmn/s to afile and then pass the list to find command

Code:
for i in `cat list` do ssh user@10.200.200.10  "find path -name "$i" >> file
done

then u can use the list in the file named file to scp or sftp

Last edited by zozoo; 12-04-2013 at 07:49 AM..
# 3  
Old 12-05-2013
Crude method

Thanks for the advice zozoo. I tried to write the output to a file but had issues with the text wrapping or putting the pathname and the filename on different lines likely due to something I was or was not doing. Anyway I went back to my code and tweaked it to the following:
Code:
for file in `ssh user@10.200.200.10 find

${ORACLE_HOME}/bin/sqlplus -s /nolog  <<EOF

conn user/user
SET WRAP OFF
SET HEADING OFF

SELECT f.file_path||'/'||f.file_name
FROM fileupload_index@tardev1 f
INNER JOIN imagelog@tardev1 i
ON f.id = i.fileupload_id
WHERE f.file_name IS NOT NULL;

EOF
-type f`

do
scp user@10.200.200.10:${file} /oraworking/madeup/images/orig/
done

Despite the Crudity, this seems to do what I want it to do. Full test coming up...

Thanks again
# 4  
Old 12-11-2013
Very Helpful!

OK so that worked and then didn't, and then did and then didn't!
So I am using zozoo's method which works a treat (thank you!).

Code:
some code...

IMG_OUT=`${ORACLE_HOME}/bin/sqlplus -s /nolog  <<EOF

conn user/password

set feed off
set head off
set pages 0
@getimages
EOF`

echo "${IMG_OUT}" >> list

for i in `cat list`
do scp  csuser@10.202.200.11:${i} /oraworking/madeup/images/orig/
done

...more code

Thanks again for your help zozoo!

Now how does one close a thread!?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing string from bash to sqlplus

Hello, I have file (PARFILE) with string on first line: INCLUDE=SCHEMA:"IN\( 'SCHEMA1','SCHEMA2','SCHEMA3' \)"In .sh script I use: .... IMPORT_SCHEMA=`awk 'NR==1{print $2}' ${PARFILE}` ...print $2 is because 'SCHEMA1','SCHEMA2','SCHEMA3' is 2nd column in file echo "$IMPORT_SCHEMA"... (5 Replies)
Discussion started by: DjukaZg
5 Replies

2. Shell Programming and Scripting

Passing variable from file to sql from script

Hi Friend, I have one file in which some number are mentioned and number of lines are vary every time And i need to pass that number to my sql command from script. Suppose i have file acc.txt 45456546456 45464564565 67854353454 67657612132 Number of records are vary every time.... (20 Replies)
Discussion started by: pallvi_mahajan
20 Replies

3. Shell Programming and Scripting

Passing string as variable(s) in bash

I'm trying to write a basic bash script that takes input you give (what directory, if any, what name, if any ....) and passes the information to find. I'm trying to just create a string with all variables and then pass it to find. So far I have this extremely simple: #!/bin/bash -f ... (2 Replies)
Discussion started by: Starting_Leaf
2 Replies

4. Shell Programming and Scripting

Passing a string variable from Unix to Sql Plus

Hi Guys, I am trying to pass a string variable from Unix shell script to sqlplus as a parameter. I have tried using single quotes with the variable name but it does not work. Please help me with it. I am using BASH. My code: Your help is much appreciated. Thanks, shil (2 Replies)
Discussion started by: infintenumbers
2 Replies

5. Shell Programming and Scripting

passing arguments to sql script

Hi Gurus, i have one requirement in unix script, i have a file called abc.txt in that few lines are there with the empid, i need to read each line and pass to .sql script. ex: abc.txt 2345 2346 1243 1234 i need to pass these arguments to .sql script rom unix ex: select * from... (1 Reply)
Discussion started by: Devendar
1 Replies

6. Shell Programming and Scripting

Problem in Passing sql query for a script

Unix prompt ========= echo "Enter the query" read q ========== User has entered : SELECT * FROM employee ===================== Now the problem starts.. echo $q Output: SELECT "all files names in the PWD" FROM employee ================================================ ... (5 Replies)
Discussion started by: Niroj
5 Replies

7. Shell Programming and Scripting

passing variable from bash to perl from bash script

Hi All, I need to pass a variable to perl script from bash script, where in perl i am using if condition. Here is the cmd what i am using in perl FROM_DATE="06/05/2008" TO_DATE="07/05/2008" "perl -ne ' print if ( $_ >="$FROM_DATE" && $_ <= "$TO_DATE" ) ' filename" filename has... (10 Replies)
Discussion started by: arsidh
10 Replies

8. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

9. Shell Programming and Scripting

Passing argumnets from shell script to sql

hi I all , I have sql statment in my shell script , I pass two argument to the script I need to pass the this two arguments to the sql statment example : runsql.sh "1" "2" sql : updat tables_x set y=0 where A=:x should subsituted by "1" and B=:y shuold subsituted bt "2"... (1 Reply)
Discussion started by: habuzahra
1 Replies

10. Shell Programming and Scripting

passing parameter from Shell-script to Sql-script

Dear Friends, Please help me to achieve the following: I want to pass one parameter from Shell-script to Sql-script. Example: My ShellScript.sh is calling report.sql like this: /bin/sqlplus /reports.sql And My report.sql is calling many Stored-Procedures like this: exec... (0 Replies)
Discussion started by: subodhbansal
0 Replies
Login or Register to Ask a Question