Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Passing string from bash to sqlplus

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing string from bash to sqlplus
# 1  
Old 11-08-2017
Passing string from bash to sqlplus
Hello,

I have file (PARFILE) with string on first line:

Code:
INCLUDE=SCHEMA:"IN\( 'SCHEMA1','SCHEMA2','SCHEMA3' \)"

In .sh script I use:

Code:
....
IMPORT_SCHEMA=`awk 'NR==1{print $2}' ${PARFILE}`
...

print $2 is because 'SCHEMA1','SCHEMA2','SCHEMA3' is 2nd column in file

Code:
echo "$IMPORT_SCHEMA" is
'SCHEMA1','SCHEMA2','SCHEMA3'

Now, I want pass this to sqlplus where i execute some script which has:
"...where owner in SCHEMA_LIST.."

How can i do that?

Code:
sqlplus -s / as sysdba @$LOCATION_SCRIPT/test.sql  $IMPORT_SCHEMA

is not good
Last edited by RudiC; 11-08-2017 at 09:54 AM..
# 2  
Old 11-08-2017
Quote:
Originally Posted by DjukaZg
Code:
INCLUDE=SCHEMA:"IN\( 'SCHEMA1','SCHEMA2','SCHEMA3' \)"

In .sh script I use:

Code:
....
IMPORT_SCHEMA=`awk 'NR==1{print $2}' ${PARFILE}`
...

This seem to be awfully dangerous to me, because i.e. this:
Code:
INCLUDE=SCHEMA:"IN\( 'SCHEMA1','SCHEMA2', 'SCHEMA3' \)"

(notice the additional space) is perhaps perfectly within the syntax requirements too and your script would fail in this case. But anyway, this was not your question.

Quote:
Originally Posted by DjukaZg
Code:
sqlplus -s / as sysdba @$LOCATION_SCRIPT/test.sql  $IMPORT_SCHEMA

is not good
You need to create a "here-document" from the test.sql file and then you can use the variable you created. Like this:

Code:
sqlplus -s / as sysdba <-EOF
      ......
      ...... $IMPORT_SCHEMA ....
      ......
EOF

Note that $IMPORT_SCHEMA will in this case simply be replaced by the contents of the variable, which is in your example "'SCHEMA1','SCHEMA2','SCHEMA3'". So effective this will be executed by sqlplus:

Code:
sqlplus -s / as sysdba <-EOF
      ......
      ...... 'SCHEMA1','SCHEMA2','SCHEMA3' ....
      ......
EOF


I hope this helps.

bakunin
# 3  
Old 11-08-2017
You can also accept arguments and use it in a .sql file:-
Code:
select * from table_name where column_name in ( &1 );

# 4  
Old 11-08-2017
Quote:
Originally Posted by bakunin
This seem to be awfully dangerous to me
Yes, i know that, but (yet) i dont have other idea.. Smilie


Quote:
You need to create a "here-document"
Can you explain this?

Thank you for help

---------- Post updated at 09:51 AM ---------- Previous update was at 09:50 AM ----------

Quote:
Originally Posted by Yoda
You can also accept arguments and use it in a .sql file:-
Code:
select * from table_name where column_name in ( &1 );

I understand that. Problem is it will take only 'SCHEMA1', not whole string
# 5  
Old 11-08-2017
Quote:
Originally Posted by DjukaZg
I understand that. Problem is it will take only 'SCHEMA1', not whole string
You have to enclose the argument in double quotes:-
Code:
@$LOCATION_SCRIPT/test.sql  "$IMPORT_SCHEMA"

# 6  
Old 11-08-2017
Quote:
Originally Posted by DjukaZg
Yes, i know that, but (yet) i dont have other idea..
Try:
Code:
IMPORT_SCHEMA="$(sed -n '1 {;s/.*\\\( *//;s/ *\\\).*//;p;q;}' ${PARFILE} )"

Quote:
Originally Posted by DjukaZg
Can you explain this?
hmm, i did give an example. Immediately thereafter, no?

I hope this helps.

bakunin
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing sqlplus output to shell variable

Hi , I am using below code : for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'` do ORACLE_SID=$i export ORACLE_SID; dest=`sqlplus "/ as sysdba" <<EOF set heading off feedback on verify off select DESTINATION from v\\$archive_dest where target in... (5 Replies)
Discussion started by: admin_db
5 Replies

2. Shell Programming and Scripting

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' some code... for file in `ssh user@10.200.200.10 'find... (3 Replies)
Discussion started by: JonP
3 Replies

3. Shell Programming and Scripting

Passing a parameter from a shell script to sqlplus

Hi All, I'm new to Linux and scripting, apologies in advance for 'stupid' questions. Please help... Im writing a script that calls a sqlplus script but the sqlplus requires inputs and i cant seem to get this to work. here is my code. #!/bin/sh TERM=vt100 export TERM... (4 Replies)
Discussion started by: Mahomed
4 Replies

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

5. Shell Programming and Scripting

Shell Script passing parameters to sqlplus code

Hello All, I am interested in finding out a way to pass parameters that are entered at the prompt from HP unix and passed to SQLPlus code with a Shell Script. Is this possible? Thanks (4 Replies)
Discussion started by: compprog11
4 Replies

6. UNIX for Dummies Questions & Answers

Passing a Unix parameter to SQLPlus login command

hi All, i m trying to pass a user choice paramter from unix to sqlplus connect command here i want the user to enter the username and password he wants to connect in sql plus through read in unix and then automatically connect to that instance. sqlplus -s $1/$2 where $ 1 and $2 will b... (2 Replies)
Discussion started by: Jcpratap
2 Replies

7. Shell Programming and Scripting

Passing Shell array to SQLPlus

Dears, Newbie here and tried to search this topic for 3 days now with results. I have a shell array and I want to use it in sqlplus with one connection. here is what I have for now #!/bin/ksh FileName=1000 FileName=2000 FileName=3000 FileName=4000 sqlplus /nolog <<EOF connect... (20 Replies)
Discussion started by: roby2411
20 Replies

8. Shell Programming and Scripting

Passing the unix variable to sqlplus

Hi, I am writing a script which creates an external table using a shell script. My requirement is like this. Usage: . ./r.ksh <table_name> - this should create an external table. e.g . ./r.ksh abc - this should create an external table as abc_external. How do i achieve this? Please... (5 Replies)
Discussion started by: Anaramkris
5 Replies

9. UNIX for Advanced & Expert Users

passing unix variable to sqlplus without a file name

Hi, I want to input unix variable to sqlplus.The following is working fine sqlplus username/password @dummy.sql param1 param2 << EOF create user $1 identified by $2; EOF But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that?? Thanks... (3 Replies)
Discussion started by: sakthi.abdullah
3 Replies

10. Shell Programming and Scripting

passing parameters from a shell script to sqlplus

Hi , I want to pass parameters from a shell script to a sql script and use the parameter in the sql query ..and then I want to spool a particular select query on to my unix box... for 4 different locations by writing only one sql script Right now no file is generated on the unix box...it is a... (2 Replies)
Discussion started by: phani
2 Replies
Login or Register to Ask a Question