SQL query in unix shell script

Question

I have written a code that will give me the required output after executing it.

Below is the code:

#!/bin/ksh

sqlplus / << !EOF!
set verify off
set heading off
set pagesize 0
set feedback off;

select * from account where account_name='23553558101';

exit;
!EOF!`

The problem is that I'm getting all the non-required stuffs too, but I need the output only not other things along with that.

Below is the output I'm getting:

$ ./sqltest

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 29 17:32:52 2017

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options

17:32:52 OPS$SP1ITA1B@SVITPRD1 > 17:32:52 OPS$SP1ITA1B@SVITPRD1 > 17:32:52 OPS$SP1ITA1B@SVITPRD1 > 17:32:52 OPS$SP1ITA1B@SVITPRD1 > 17:32:52 OPS$SP1ITA1B@SVITPRD1 > 17:32:52 OPS$SP1ITA1B@SVITPRD1 >   56566309 23553558101                      20-05-2017 05:41:00         57194135        20000184           64,56
20-05-2017 00:00:00  340393871           338845845          500               0
                      0

17:32:52 OPS$SP1ITA1B@SVITPRD1 > 17:32:52 OPS$SP1ITA1B@SVITPRD1 > Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options

it's not entirely clear what you mean by non-required stuff but i'm guessing the oracle connection info so if that's the case, you just need need to call "sqlplus -s /" to make a silent sql connection — Mike
– Mike, Commented May 29, 2017 at 16:52
I've written below code: #!/bin/ksh sqlplus -s /nolog << !EOF! set heading off set trimout on set echo off set pagesize 0 set feedback off select * from account where account_name='23553558101'; exit; !EOF!` I'm getting below error: $ ./sqltest SP2-0640: Not connected — ashish_k
– ashish_k, Commented May 30, 2017 at 7:00
why is there a nolog part after your / in your call "sqlplus -s /nolog"? From a sql perspective, you are asking to connect with no user using nolog as the password. You can either connect "sqlplus -s username/password" or use the database password file "sqlplus -s /" — Mike
– Mike, Commented May 30, 2017 at 14:18

GreenT · Accepted Answer · 2017-05-30 07:02:12Z

When you are using sqlplus in a shell script is better to log in using the silent mode "sqlplus -s" to supress the login messages and also to remove the sql prompt "sql >".

Further more, you need to change some variables like:

heading - it will not display the head of the query result(the name of the columns to be more precise)

set heading off

feedback - it will not return the number of rows after a query select.

set feedback off

verify - is used to display the values in case you have substitution variables

set verify off

The columns presented above are ON by default so it is better to switch them off if you want a clean display (just the result) and also it is mandatory in my opionion if the result of your sqlplus will be collected by a shell variable. You can check the manual for more information about sqlplus system variables: https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG060

Collectives™ on Stack Overflow

SQL query in unix shell script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related