Tom,

I have a similar need but i am querying v$session to see the number of sessions and this is what i get.

$ cat t2.sh
#!/bin/ksh

x=`sqlplus "/ as sysdba" <<endl | grep KEEP | sed 's/KEEP//;s/[ ]//g'
select 'KEEP' , count(*) from v$session;
exit
endl`
echo the answer is $x

$./t2.sh
the answer is sys@ORA9I>select'',count(*)fromv


I tried using "\" before the $ in the tablename, didn't work either.

Thanks

code=`sqlplus -s / << EOF
select max(sal)
from emp
where empno=934;
EOF`

echo $code

Very nice. But i want to know how to assign multiple values from the query to the linux variables.

Tom,
This is not an Oracle question, but since your knowledge goes much beyond Oracle, I am asking. I have seen a lot of shell scripts like:

sqlplus / << EOF
...
EOF

My question is what is the purpose of << EOF? The scripts just run fine even without << EOF.

Thanks

Basically that just tells sqlplus to treat all the text until it hits EOF as a script. It is inlining a script into a shell script.  You do not have to EOF, it is just a convention.

Of course the '/' indicates they are using OS authentication and the script is logging into the DB as itself.

here is a sample with EOF and another terminator, except I do not have an OS authenticated user.


oracle@lnxdev:~$ sqlplus scott/tiger <<EOF
> select sysdate from dual;
> EOF

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jun 21 10:15:26 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>
SYSDATE
---------
21-JUN-05

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
oracle@lnxdev:~$ sqlplus scott/tiger <<XXX
> select sysdate from dual;
> XXX

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jun 21 10:15:45 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>
SYSDATE
---------
21-JUN-05

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Productio
  

The answer shown above works fine if you are trying to retrieve a single value from sqlplus. If you have a sqlplus script that returns multiple columns, you could read them into shell variables like this:

sqlplus / @myscript.sql | read var1 var2 var3

This will read 3 columns into var1, var2, and var3. Make sure that if you do this, you don't have blank or null values coming back, otherwise the "read" command will skip over the blanks/nulls and get the variable assignments out of sync.

Another variation: if you are retrieving multiple rows as well as columns in your sqlplus script, and want to loop over the rows:

sqlplus / @myscript.sql | while read var1 var2 var3
do
<more shell stuff here>
done

A specific example:

in emps.sql:

set head off
set verify off
set feedback off
set head off
set pages 0

select empno, empname
from scott.emp;

exit;

In test.sh:

sqlplus / @emps.sql | while read eno ename
do
echo "Employee number is $eno Name is $ename"
done

Hope this helps

hi all,
thanks for the great examples;
if someone could expand upon this by providing some simple examples on calling functions, procedures (with in/out params), global variables, that would be great. Perhaps an intro (or a web reference or book) into Ora to Unix communication/interaction might be of great value for those of us migrating from Windoze ;-) to Unix/Linux.

thanks a big bunch

#!/bin/ksh
echo "------------------------------"
echo "using an array..."
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev <<EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 4;
exit;
EOF`

echo "there are ${#my_arr[*]} elements in the array"
element=0
while [ $element -lt ${#my_arr[*]} ]
do
echo "==>"${my_arr[$element]}
let element=$element+1;
done

echo "Echo all in one command now!"
echo ${my_arr[*]}

echo "------------------------------"
echo "using while read..."
echo "------------------------------"
sqlplus -s scott/tiger@dev <<EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 4;
exit;
EOF

while read reslt_line
do
echo "==>"$reslt_line
done < tmp.txt




The output:
------------------------------
using an array...
------------------------------
there are 3 elements in the array
==>TAB1
==>TAB2
==>TAB3
Echo all in one command now!
TAB1 TAB2 TAB3
------------------------------
using while read...
------------------------------
==>TAB1
==>TAB2
==>TAB3

#!/bin/ksh
#======================================================
# stored proc with parms
#======================================================
## CREATE OR REPLACE PROCEDURE p1 (i_parm IN NUMBER, o_parm OUT NUMBER)
## IS
## BEGIN
## o_parm := 5 * i_parm;
## END;
## /
my_in_parm=5
echo =============================
echo FIRST
echo =============================
RETVAL=`sqlplus -s scott/tiger@dev <<EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number := $my_in_parm;
y number;
begin
p1(x, y);
dbms_output.put_line('o_parm from p1 is '||y);
end;
/
exit;
EOF`

echo $RETVAL
X=`echo $RETVAL | awk '{print $5}'`
echo $X

## CREATE OR REPLACE PROCEDURE p2 (i_parm IN NUMBER, o_parm OUT NUMBER)
## IS
## BEGIN
## o_parm := 5 * i_parm;
## DBMS_OUTPUT.put_line ('Line one');
## DBMS_OUTPUT.put_line ('Line two');
## END;
## /
my_in_parm=5
echo =============================
echo SECOND
echo =============================
set -A my_arr `sqlplus -s scott/tiger@dev <<EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number := $my_in_parm;
y number;
begin
p2(x, y);
dbms_output.put_line('o_parm from p1 is '||y);
end;
/
exit;
EOF`

echo "there are ${#my_arr[*]} elements in the array"
element=0
while [ $element -lt ${#my_arr[*]} ]
do
echo "==>"${my_arr[$element]}
let element=$element+1;
done


>t.ksh

=============================
FIRST
=============================
o_parm from p1 is 25 PL/SQL procedure successfully completed.
25
=============================
SECOND
=============================
there are 13 elements in the array
==>Line
==>one
==>Line
==>two
==>o_parm
==>from
==>p1
==>is
==>25
==>PL/SQL
==>procedure
==>successfully
==>completed.

Just wanted to add that the following construction:

some_command <<ARBITRARY-TOKEN
other_command_returning_a_value
ARBITRARY-TOKEN

uses what is known as a "here document". Zip to section 2.3 of the following document for a more detailed description:

</code> http://www.ceri.memphis.edu/computer/docs/unix/bshell.htm <code>

Apparently the Bourne shell was the first environment to possess this feature, and it was carried over into the Korn and Bourne-again shells, as well as Perl apparently.

just a quick word of thanks to everyone contributing to this forum. You are all great and making this into a very valuable knowledge source.

Keep up the awesome job, everyone!!!

Thanks chaps.

This is most useful. I have done lots of this sort of stuff using SQSH and ISQL with SYBASE but my ORACLE is very rusty.

This will be invaluable.

Cheers

Damian

Hi Tom.
The example above are very useful.
Could you please recommend on a book or any other
documentation that deal with shell progrmaning for
oracle dba
Regards.

Hi Tom,

I am thinking of writing a shell script to tkprof trace files for me. I'm struggling with how to get the udump directory programmatically though because not everyone here uses the default location. I'd like to query the data dictionary and return that into a variable but I can't find anywhere that is stored?

All my other ideas are hack like, eg greping the spfile, or using sho parameter. That would involve chopping up string that could vary in length. Any suggestions?

[tkyte@tkyte-pc ~]$ cat test.sh
#!/bin/bash

X=`sqlplus / <<EOF | grep '^UDD ' | awk '{print $2}'

set serveroutput on
declare
        l_status number;
        l_dummy binary_integer;
        l_user_dump_dest long;
begin
        l_status := dbms_utility.get_parameter_value( 'user_dump_dest', l_dummy, l_user_dump_dest );
        dbms_output.put_line( 'x' );
        dbms_output.put_line( 'UDD ' || l_user_dump_dest );
end;
/
exit
EOF`

echo $X
[tkyte@tkyte-pc ~]$ ./test.sh
/home/ora10gr2/admin/ora10gr2/udump

Ok it's stupid question time. I can't figure out why mine just spits out a blanks line?

$ vi test.sh
"test.sh" [New file]
#!/bin/bash

X=`sqlplus / <<EOF | grep '^UDD ' | awk '{print $2}'

set serveroutput on
declare
l_status number;
l_dummy binary_integer;
l_user_dump_dest long;
begin
l_status := dbms_utility.get_parameter_value( 'user_dump_dest', l_dummy, l_user_dump_dest
);
dbms_output.put_line( 'x' );
dbms_output.put_line( 'UDD ' || l_user_dump_dest );
end;
/
exit
EOF`

echo $X

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"test.sh" [New file] 21 lines, 415 characters
$ chmod u+x test.sh
$ ./test.sh

$

Does this mean anything to you:

sqlplus / <<EOF | grep '^UDD ' | awk '{print $2}'
++ sqlplus /
++ grep '^UDD '
++ awk '{print $2}'
+ X=

echo $X
+ echo

[TIGGER:/home/oracle]$ ./t.sh
/ora_admin/TIGGER/udump
[TIGGER:/home/oracle]$ cat t.sh
#!/bin/bash
X=`sqlplus -SL / <<EOF
set pause off pages 0 echo off verify off feed off termout off
select value from v\\$parameter where name='user_dump_dest';
exit
EOF`
echo $X
exit
[TIGGER:/home/oracle]$

Yes I can, thanks that was the issue though. I can't just use / I need / as sysdba.

Hi Tom,

I am running some sql in unix. I am planning to transfer some array into a PL/SQL array. var_list is the collection name. I have created str_array as follows:

create or replace type str_array as table of varchar2(2000);

I have an array by name sql_string formed in my unix script. I am trying to retrieve the array into the collection. I can do that by embedding the following script in while loop. But i have to avoid it. Now i am not able to use a index on the array sql_string as it is a unix variable. If i use the SQL variable as index, it gives syntax error saying bad number. But i am able to assign value to var_list(variable1) if i use

var_list(variable1):=${sql_string[1]}; ==> This works.

var_list(variable1):=${sql_string[variable1]}; ==> Bad number as it is a sql variable and unix is not able to identify the same

var_list(variable1):=${sql_string[$k]}; ==> k is a unix variable. This works, but i cannot increment the variable inside sql block. So i cannot continue to the next record

Is there any way to use a variable in the index of the array sql_string. If i use a unix variable i won't be able to increment that variable and move to the next value. I can understand that it is achievable using a loop in unix. But that means i am connecting to Oracle every now and then.

The code is as shown below. I have not included any loop in the same as of now. But thats what i intend to do if i can assign the values correctly.


sqlplus -s <Connect-String> <<XYZ
whenever sqlerror exit failure
whenever oserror exit failure
set serveroutput on
DECLARE
var_list str_array;
variable1 NUMBER;
BEGIN
variable1:=1;
var_list := str_array('');
var_list(variable1):=${sql_string[variable1]};
dbms_output.put_line(variable1);
dbms_output.put_line(var_list(variable1));
--variable1:=variable1+1;
END;
/
EXIT;
XYZ

So i was wondering if there is any way i can achieve my purpose

Tom,
Similar kind of problem i got, when i executing a Unix script of sqlquery with return value to variable.

Script:
SQLQUERY="select 1 from dual;"

RESULTSET=`$DB_CONNECTION <<eof 
$SQLQUERY
exit;
eof`
echo "Resultset : $Resultset"

I am getting error and also I am running through MKS tool.

SQL> Error 45 initializing SQL*Plus Internal error

Thanks,
Chandru

[tkyte@dellpe ~]$ cat test.sh
#!/bin/bash
SQLQUERY="select 1 from dual;"

DB_CONNECTION="$ORACLE_HOME/bin/sqlplus /"

RESULTSET=`$DB_CONNECTION <<eof
$SQLQUERY
exit;
eof`

echo "Resultset : " $RESULTSET
[tkyte@dellpe ~]$ ./test.sh
Resultset :  SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 8 12:12:40 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ops$tkyte%ORA10GR2> 1 ---------- 1 ops$tkyte%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
[tkyte@dellpe ~]$

Thanks, Tom it was veru useful one :)

#!/bin/ksh
. /home/fctntwrk/gile.properties
v_test=`sqlplus $USER/$PASS@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TC
P)(HOST=$SCHEMA)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=$REGION)))<<EOF | grep KEEP | sed 's/KEEP//;s/[ ]//g'
SELECT 'KEEP' , count(*) FROM DUAL;
EXIT;
EOF
echo $v_test

Please let me know if there are any errors in the above code, I executed the script at first when i got a return value of 1 . But after modifying the code to read from property file I am not getting any values . I fear of an edting mistake

I want this output so that I can check no of reports to excute .and am going to use this value again.

#!/bin/ksh
v_count=`sqlplus -_s "/" << end1 | grep A | sed 's/A//;s/[ ]//1'
select 'A',count(1) from drg_5 where process_date is null ;
EXIT
end1`
echo $v_count

output :-

hwfin21 $ sh 123.sh

hwfin21 $


Hi I have excuted this but am not getting any output no error is there .can u please suggest as am new to unix script.

[tkyte@localhost ~]$ cat x.sh
#!/bin/ksh 
x=`echo "select '===='||count(*) from all_users;" | $ORACLE_HOME/bin/sqlplus / | grep '====' | grep -v 'COUNT' | sed 's/====//'`

echo "x = " $x
[tkyte@localhost ~]$ ./x.sh
x =  54
[tkyte@localhost ~]$ 

Hello,

I am new in shell scripting and i am stuck , my code is producing below error:

ORACLE_BASE=/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.4/db_1; export ORACLE_HOME
ORACLE_SID=pindb; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
login='system/manager'
code=`
sqlplus -s $login <<EOF
set heading off
set feedback off
SELECT tablespace_name,(SUM(bytes/1024/1024)) FROM dba_free_space WHERE tablespace_name IN ('PIN00','PINX00','SYSTEM','SYSAUX','UNDOTBS1','STATSPACK') GROUP BY tablespace_name;
exit`

output:

PIN00 28287.1172 PINX00 93813.1367 STATSPACK 54.1875 SYSAUX 215.125 SYSTEM .1015625 UNDOTBS1 745.8125 6 rows selected.

Here its working very fine but when i am passing code variable to utl_mail.send procedure, its giving error same as i mentioned previous. I have put all the environment variable as well. But when i put utl_mail.send and pass CODE variable into it it gives error.

ORACLE_BASE=/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.4/db_1; export ORACLE_HOME
ORACLE_SID=pindb; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
login='system/manager'
code=`
sqlplus -s $login <<EOF
set heading off
set feedback off
SELECT tablespace_name,(SUM(bytes/1024/1024)) FROM dba_free_space WHERE tablespace_name IN ('PIN00','PINX00','SYSTEM','SYSAUX','UNDOTBS1','STATSPACK') GROUP BY tablespace_name;
exit`
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect / as sysdba
exec UTL_MAIL.SEND(sender=>'ashish.kumar1@mycp.com', recipients=>'ashish.kumar1@mycp.com', cc =>'ashish.kumar1@mycp.com' , subject=>'$ORACLE_SID BACKUP', message =>'$code')
exit;
EOF
echo "sqlplus exited"

ERROR:
ORA-01756: quoted string not properly terminated

SP2-0734: unknown command beginning "PIN00 ..." - rest of line ignored.
SP2-0734: unknown command beginning "PINX00 ..." - rest of line ignored.
SP2-0734: unknown command beginning "STATSPACK ..." - rest of line ignored.
SP2-0734: unknown command beginning "SYSAUX ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "SYSTEM ..." - rest of line ignored.
SP2-0734: unknown command beginning "UNDOTBS1 ..." - rest of line ignored.
sqlplus exited

Thanks,
Ashish

Hello,

when code varibale is getting more than one record its throwing error.

How to pass multiline shell variable into my code.

Thanks,
Ashish

Hello,

now i am getting error like
ERROR at line 1:
ORA-29278: SMTP transient error: 451 See http://pobox.com/~djb/docs/smtplf.html
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 345
ORA-06512: at "SYS.UTL_MAIL", line 591
ORA-06512: at "SYS.UTL_MAIL", line 608

but i am able to run it on sqlplus, only problem i am getting while running it through shell script.

Thanks
Ashish

Hello,

Below is my shell script
#!/bin/ksh
ORACLE_BASE=/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1/db_1; export ORACLE_HOME
ORACLE_SID=rman; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
cd /oralogs/catp/rman_db_bkp
DTIME="`date +%d%m%y`_`date +%H%M%S`"
rman target / catalog rman/cat@catp log=/oralogs/catp/rman_db_bkp/${ORACLE_SID}_${DTIME}.log<<EOF
run { execute script level_0; }
EOF

when i run above script from crontab it runs fine and making log file same as i wanted(rman_300412_125900.log).

but problem is the same script i run using dba_scheduler it works fine but creating log file like this(rman__.log).

Please help

Thanks,
Ashish

Tom,

I want to pass an IN OUT parameter to a pl/sql procedure and call that from a shell script. I want the return value from procedure to capture in a shell variable. can you advise?

create or replace procedure p1(i_id in number, io_step_id in out number)
is
v_step_id number := io_step_id + 1;
begin

dbms_output.put_line('io_step_id =>'|| io_step_id);
io_step_id := v_step_id + 1;
dbms_output.put_line('io_step_id =>'|| io_step_id);


end;

exec p1(1, 1);

#!/bin/bash

x=1
y=1

z=`sqlplus / <<EOF | grep ^answer | sed "s/^answer: //"
set serveroutput on
declare
    l_tmp number := $y;
begin
   p1( $x, l_tmp );
   dbms_output.put_line( 'answer: ' || l_tmp );
end;
/
exit
EOF`

echo I got $z

Tom,

I have a shell script which does the following -
1) downloads file using wget command
2) Loads to database using SQL*LOADER
3) Calls pl/sql package to load data into staging and after
cleaning up data in staging moves to business area achema

PL/SQL package calls logs data into logging tables in addition to cleaning up data in staging and moving to business area schema. I wanted to capture all the actions that happens in the shell script like calling shell function to download file, loading data using sqlloader, shell function which calls pl/sql package, actions that happen in database. STEP_ID will be unique for all these steps. The sub_step_id shloud contain 1,2,3,4,....(sequential). This was the reason, I was trying to use a IN, OUTparameter in the above question, that I asked on July 10th. I pass these sub_Step_id back and forth from shell scrip to DB and DB to shell script. Is there any better way to handle this?

My LOG_TABLE table is as shown below -

CREATE TABLE LOG_TABLE
(
STEP_ID NUMBER NOT NULL,
SUB_STEP_ID NUMBER NOT NULL,
SCRIPT_TYPE VARCHAR2(100),
SCRIPT VARCHAR2(1000),
PROCEDURE_CALL VARCHAR2(4000),
EVENT VARCHAR2(20),
PROCESS_TABLE VARCHAR2(100),
ROW_COUNT NUMBER,
LOG_MESSAGE VARCHAR2(4000),
LOG_MESSAGE_DT DATE DEFAULT sysdate NOT NULL,
PROCESS_CODE VARCHAR2(100) NOT NULL,
FILE_DATE DATE,
COMMIT_DT DATE DEFAULT sysdate NOT NULL,
COMMIT_USR VARCHAR2(30) DEFAULT USER NOT NULL
);

Sample data:
100 1 SHELL load.sh f_get_file DOWNLOAD http://www.xyz.com/test.csv
100 2 SHELL load.sh f_truncate_load_table FUNCTION_CALL Truncate load table - Shell function
100 3 DB pkg_stg pkg_stg.sp_main( 20120709); PROCEDURE_CALL Begin sp_main procedure
100 4 DB pkg_stg pkg_stg.proc1( 100); EXCEPTION No Data Found
100 6 SHELL load.sh f_build_ctl_file SQLDR_CTL Build SQLLDR control file
100 7 SHELL load.sh f_load_file LOAD_FILE Load data using SQL*Loader
100 8 SHELL load.sh f_archive_file ARCHIVE Archive files
100 9 SHELL load.sh f_publish FUNCTION_CALL Truncate stg table - Shell function
100 10 DB load.sh pkg_stg.proc2(100) PROCEDURE_CALL

DB -->
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Unix-->
Linux 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686 i686 i386 GNU/Linux

Code -->
#!/bin/bash

IFS=","

echo "Please enter the Utility iD's?"
read input_details

invalid_utility=`sqlplus -s / << END
set heading off
set pagesize 0
set linesize 9999
set tab off
set feedback off
whenever sqlerror exit 1

SELECT listagg (utility
, ',')
WITHIN GROUP (ORDER BY utility)
AS utility_list
FROM (WITH utilities
AS ( SELECT REGEXP_SUBSTR (
'$input_details'
, '[^,]+'
, 1
, ROWNUM)
utility
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (
'$input_details')
- LENGTH (
REPLACE (
'$input_details'
, ','))
+ 1)
SELECT utility
FROM utilities
WHERE UPPER (TRIM (utility)) NOT IN (SELECT UPPER(name)
FROM s_table));
exit
END`

echo $invalid_utility

#<<Code Ends>>
The issue I am facing is that the utility_list consists of comma seperated values, but when "echo $invalid_utility" is executed it replaces "," with Whitespaces.

Query is used to return values which do not exists in s_table in Db.


Say I input: Apple, Maple, Traple, Chappel
Let's assume we have Maple in the Database, so it should return: Apple, Traple, Chappel
But instead it returns:Apple Traple Chappel

[tkyte@localhost ~]$ cat test.sh
#!/bin/bash

x=`sqlplus -s / <<END
set heading off
set pagesize 0
set linesize 9999
set tab off
set feedback off
whenever sqlerror exit 1
select 'A, B, C' from dual;
END`

echo $x
[tkyte@localhost ~]$ sh test.sh
A, B, C
[tkyte@localhost ~]$ 

Greeting thomas,

And thanks like always, I just want to suggest using shell functions instead of using (`) directly, since it will make it easier to troubleshoot, example:

get_char () {
sqlplus -s / <<!
set heading off
set feedback off
set pages 0
select * from dual;
!
}

y=`get_char`

echo $y

I am planning to have a shell script to extend datafiles and add a datafile to the tablespace when it runs out of space. Please provide me any example script. Thanks.

Borrowed from https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#3_2

SQL> var maxsal number
SQL> begin
  2    select max(sal) into :maxsal from emp;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> column maxsal new_value msal noprint
SQL> select :maxsal maxsal from dual;



SQL> host x='&msal' ; echo the answer is $x
the answer is 5000

sqlplus -s username/pwd@db |&
print -p -- "select sysdate from dual;"
read -p -- date_now

echo $date

print -p -- "select A from dual;"
read -p -- random_var

echo $random_var

print -p -- "exit;"

The advantage here is you need to open only one Oracle connection from the shell script and execute as many sql statements as you need. You can do any shell processing in between sql queries.
Of course,you need to be sure that the query returns results and no null values.

#!/bin/sh

a=`sqlplus -s /nolog <<EOF
connect DTD_TRADE/encr0achdpc@S3LNDT11

var exitCode number

-- calling the PL/SQL procudure (that can raise an exception)
begin
:exitCode :=12222;
end;
/
exit :exitCode
EOF
`
echo $exitCode
echo $a

#!/bin/sh

a=`sqlplus -s /nolog <<EOF
connect testuser/xxxxx@testdb

var exitCode number

-- calling the PL/SQL procudure (that can raise an exception)
begin
:exitCode :=12222;
end;
/
exit :exitCode
EOF
`
echo $exitCode
echo $a

Security best practice is to put the connect inside the here doc. If you are not using os authentication, and you put user/passwd before the << the password will show up in ps.

Try echo $? after you call sqlplus.
On my system that returned your exit code.

Also good to use
whenever oserror exit failure
whenever sqlerror exit failure