Oracle Query results to be stored in variables using unix

Shell Programming and Scripting
1

I want to store the sql query output into a variable

#!/bin/ksh
ORACLE_SID=DB01;
export ORACLE_SID;
export FILE_PATH=/home/asg/Tmp

# Order Checking
echo " removing old files "
rm $FILE_PATH/Malformed_Order.txt
echo " Enter the Malformed Order ....!"
read orders
echo "Regrade check : $orders"
echo ""

v_test=$(sqlplus -s abc/xyz <<END 
set feedback off;
set heading off;

select is_Regrade_order from portal.order where number = '"$orders"';

EXIT;
END)

echo "out put is: $v_test"

------------
The output expecting is

Enter the Malformed Order ....!
PO-DBMBDWG
Regrade check : PO-DBMBDWG

out put is : N

But when i tried executing the above scripting its throwing an error

The functionallity of the script is

  1. value is passed through the command prompt
  2. Passing the command line arguement to the sql query input
  3. The sql query result has to be stored in a variable

Can some one suggest and modify the script

2

Try changing this to not have the double quotes:

select is_Regrade_order from portal.order where number = '"$orders"';

to

select is_Regrade_order from portal.order where number = '$orders';
3

Changed to SQL Query to

select A.IS_REGRADE_ORDER from wlportal.orders a where A.TRACKING_NUMBER = '$orders';

Ouput : Throwing an error

4

Try this script as a test.
You may need to add

ORACLE_SID=DB01;
export ORACLE_SID;

to get it to work.

$ cat ./get_oracle_var.ksh
#!/bin/ksh

MYDATE='20091113'
echo "MYDATE=$MYDATE"

SQL_OUTPUT=$(sqlplus -s abc/xyz <<END
SET SHOW OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TERMOUT ON
SET TIME OFF
SET TIMING OFF
SET VERIFY OFF
SET ECHO OFF
SELECT 'True' FROM DUAL WHERE TRUNC(SYSDATE) = TO_DATE('$MYDATE', 'YYYYMMDD');
EXIT;
END)

echo "Output is: $SQL_OUTPUT"
exit 0

$ ./get_oracle_var.ksh
MYDATE=20091113
Output is: True
5

I have executed the below query

cat pol.sh
RACLE_SID=DB01;
export ORACLE_SID;to get it to work.

MYDATE='20091113'
echo "MYDATE=$MYDATE"
SQL_OUTPUT=$(sqlplus -s wlportal/wlportal <<END
SET SHOW OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TERMOUT ON
SET TIME OFF
SET TIMING OFF
SET VERIFY OFF
SET ECHO OFF
SELECT 'True' FROM DUAL WHERE TRUNC(SYSDATE) = TO_DATE('$MYDATE', 'YYYYMMDD');
EXIT;
END)
echo "Output is: $SQL_OUTPUT"
exit 0
 
 
Ouput is 
 
./pol.sh
./pol.sh[2]: to:  not found.
MYDATE=20091113
Output is: SELECT "True" FROM DUAL WHERE TRUNC(SYSDATE) = TO_DATE("$MYDATE", "YYYYMMDD")
                                                                  *
ERROR at line 1:
ORA-00904: "YYYYMMDD": invalid identifier
6

Three things:
1) Put back

#!/bin/ksh

2) You misspelled the ORACLE_SID environment var

RACLE_SID=DB01

should be

ORACLE_SID=DB01

3) Don't use semi-colons!! Semi-colons are not comment characters.
If you want to comment something use '#'.
The system is complaining about

;to

export ORACLE_SID;to get it to work.

should be

export ORACLE_SID #to get it to work.