Visit Our UNIX and Linux User Community


SQLplus in Shell scripts


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQLplus in Shell scripts
# 1  
Old 03-16-2007
SQLplus in Shell scripts

How to execute a query which is stored in a variable.
Say for example :

v_source_query=”select count(*) from emp”
v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF | tr '\n' ' '

set feed off
set pagesize 0
set head off
set linesize 2000

$v_source_query (I want to execute the query here)

EOF
`

echo $v_source_value

Any help is appreciated.

Trupti.

Last edited by trupti_d; 03-16-2007 at 06:58 AM..
# 2  
Old 03-16-2007
variables are case sensitive. Remove | tr '\n' ' '.
Rest is ok.
Code:
v_source_query=”select count(*) from emp”
v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF 

set feed off
set pagesize 0
set head off
set linesize 2000

$v_source_query
EOF
`

echo $v_source_value

# 3  
Old 03-16-2007
Well, thanks.
It is small v, may be it changed while copy pasting or something. Sorry about that.
But it is not working.

This is the error I get:

Syntax error at line 2 : `;' unexpected
# 4  
Old 03-16-2007
Add semicolon
Code:
v_source_query=”select count(*) from emp;

Did you do any modification to your script?
# 5  
Old 03-16-2007
Okay, this is the actual program.
The column source_query from the table source has the query “select count(*) from emp;”

v_source_query=`sqlplus -S "$DATABASE_LOGIN" << EOF

set feed off
set pagesize 0
set head off
set linesize 2000
select source_query from source where source_id = $1;

EOF
`
echo $v_source_query

v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF

set feed off
set pagesize 0
set head off
set linesize 2000

$v_source_query;

EOF
`
echo $v_source_value
# 6  
Old 03-16-2007
try this
Code:
v_source_query="select source_query from source where source_id = $1;"

echo $v_source_query

v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF

set feed off
set pagesize 0
set head off
set linesize 2000

$v_source_query;

EOF
`
echo $v_source_value

# 7  
Old 03-16-2007
Okay i tried your example

It throws an error:

SP2-0223: No lines in SQL buffer.

Previous Thread | Next Thread
Test Your Knowledge in Computers #479
Difficulty: Medium
A address usually refers to a single byte of memory (called bitwise-addressing).
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sqlplus shell

friends because when calling a sqlplus from a shell it brings me the following message sqlplus -s $BDDUSER/$BDDPASS@$BDDHOST @$DIR_SQL/prueba.sql echo "bandera 3 " $? STATUS=$? if ;then echo "bandera 4 " $? #log_info "La ejecucion de... (1 Reply)
Discussion started by: tricampeon81
1 Replies

2. Red Hat

TNS Timeout Error when connecting to SQLPLUS through scripts only

Hi, I am facing a strange issue when connecting to SQLPLUS via a shell scripts. I am using Linux 2.6.18-274.18.1 and gbash shell. When I connect to SQLPLUS through scripts then it throws TNS Time Out error ""sometimes"" and connects successfully other times.This is only happening when... (9 Replies)
Discussion started by: aashish.sharma8
9 Replies

3. UNIX and Linux Applications

how to execute multiple .sql scripts from within a shell script using sqlplus

using sqlplus I want to execute a .sql script that has dbms_output statments in rhe script. I want to write the dbms_output statements from .sql file to a log file. is this possible. thanks any help would be appreciated :wall: (1 Reply)
Discussion started by: TRS80
1 Replies

4. Shell Programming and Scripting

list all scripts in crontab which contains the string "sqlplus"

Hi folks I use a Solaris 10 box with Bash shell. I have here a script (it works!) to list all scripts in crontab which contains the string "sqlplus": for i in $(ls `crontab -l | grep -v '#' | awk '{ print $6 }' | grep -v '^$'`); do grep -l 'sqlplus' "$i"; done Is there a more elegant... (1 Reply)
Discussion started by: slashdotweenie
1 Replies

5. Programming

Shell SQLPlus

Hi, I am trying to execute the update statment in shell sqlplus.But nothing prompts.if i do ctrl+c i got the below error. SQL> update table set enabled='N' where type_code='xx'; ^C update table set enabled='N' where type_code='xx' * ERROR at line 1: ORA-01013: user requested... (2 Replies)
Discussion started by: nmahendran
2 Replies

6. UNIX for Dummies Questions & Answers

editing sqlplus id@passwd in multiple scripts, users and directories

hi all, i was given by my supervisor a task to search for scripts which contain oracle sqlplus i.e "myusername/mypasswd @myDB" in every /home/userfolder, which are, all the scripts made by different user. I've done some find command to search string for sqlplus, but it may up too long to respond.... (8 Replies)
Discussion started by: Helmi
8 Replies

7. Shell Programming and Scripting

connect to sqlplus from shell

I'm writting a shell script and at the begining I login to sqlplus by sqlplus -l user_name/password@instance what I would like is to check if the database is down or not , and if the database has started moved to the next step else sleep for a certain time and then check again . I know how to... (4 Replies)
Discussion started by: aya_r
4 Replies

8. Shell Programming and Scripting

calling sqlplus from shell

Hi All, I am executing the following code :- sqlplus -s ${DATABASE_USER} |& print -p -- 'set feed off pause off pages 0 head off veri off line 500' print -p -- 'set term off time off serveroutput on size 1000000' print -p -- "set sqlprompt ''" print -p -- "SELECT run_command from... (2 Replies)
Discussion started by: suds19
2 Replies

9. UNIX for Advanced & Expert Users

sqlplus and sh scripts (to_char command))

Hi evrybody!!!! I have a problem with this shell script INICIO=$(sqlplus -s user/user@db1 << END | awk '{printf $1}' set head off set feed off select to_char(min(create_dt) , 'HH24') from table_name where trunc(create_dt)=trunc(sysdate-2); END) I want to recover, in INICIO, the min... (4 Replies)
Discussion started by: josecollantes
4 Replies

10. UNIX for Dummies Questions & Answers

sqlplus and shell scripting

i would like to learn how to integrate my little knowledge in shell scripting with sqlplus. well... i know how to make basic query in sqlplus but i dont know how i can integrate it with shell script. can someone :) please help me on this? can you give me some basic example on how to do this kind of... (10 Replies)
Discussion started by: inquirer
10 Replies

Featured Tech Videos