Re: Q: Execute SQL from command line?

The following shell script should solve your problem.
Keep in mind that environment variable $FCP_LOGIN is uid/psw.

#/bin/ksh
SEQ_NUM=`echo "$FCP_LOGIN
  whenever sqlerror exit failure
  whenever oserror exit failure
  set heading off
  set pagesize 0
  set feedback off
  set echo off
  set termout on
  set serveroutput off
  select ltrim(to_char(T.SEQUENCE_NUMBER)) A
  from   XMAF_GEN_INT_TRANSACTIONS T
  where  T.INTERFACE_ID = '$1'
  and    T.STATUS_CODE  = 'OUT_PROCESSED'
  and    T.DATE_RECEIVED = (select max(DATE_RECEIVED)
                            from   XMAF_GEN_INT_TRANSACTIONS TA
                            where  TA.INTERFACE_ID = '$1');
  exit"|sqlplus -s`
echo Sequence number=$SEQ_NUM
VENDORS=`echo "$FCP_LOGIN
  whenever sqlerror exit failure
  whenever oserror exit failure
  set heading off
  set pagesize 0
  set feedback off
  set echo off
  set termout on
  set serveroutput off
  select distinct lower(FFV.FLEX_VALUE)
  from   XMAF_IFO_VDI_H XIV
  ,      XMAF_GEN_INT_PARAMS XIP
  ,      MTL_PARAMETERS MP
  ,      FND_FLEX_VALUES         FFV
  ,      FND_FLEX_VALUE_SETS     FFS
  where  XIV.SEQUENCE_NUMBER = $SEQ_NUM
  and    XIP.INTERFACE_ID = 'VDI'
  and    XIP.PARAMETER_SHORT_NAME = 'ORGAN_ID'
  and    MP.ORGANIZATION_ID = XIP.INTERFACE_PARAMETER_VALUE
  and    FLEX_VALUE_SET_NAME = 'XMAF_VMI_SUPPLIER_CODES'
  and    FFV.FLEX_VALUE_SET_ID = FFS.FLEX_VALUE_SET_ID
  and    FFV.DESCRIPTION = XIV.VENDOR_NUMBER || '-' || MP.ORGANIZATION_CODE;
  exit"|sqlplus -s`
echo Vendors = $VENDORS
for vendor_num in $VENDORS
do
  # Start Non-Y2K Version
  echo "$FCP_LOGIN
  start $XMAF_TOP/sql/XMAF6609.sql $1 $vendor_num $SEQ_NUM $FTP_OUT/pop$vendor_num.$SEQ_NUM
  " | sqlplus -s

  # Start Y2K Version
  echo "$FCP_LOGIN
  start $XMAF_TOP/sql/XMAF6632.sql $1 $vendor_num $SEQ_NUM $FTP_OUT/popY2K$vendor_num.$SEQ_NUM
  " | sqlplus -s
done
# make sure interface completes correctly
touch $APPLCSF/interface/tmp/$1.wrp
# update transaction
echo "update XMAF_GEN_INT_TRANSACTIONS \
      set    STATUS_CODE = 'UNLOADED' \
      ,      DATE_LOADED = sysdate \
      where  INTERFACE_ID = '"$1"'"\
      "and STATUS_CODE = 'OUT_PROCESSED';" > /tmp/$1.tmp
echo "$FCP_LOGIN
     start /tmp/"$1.tmp  | sqlplus -s
rm /tmp/$1.tmp

I hope this will help you.

Vincent Teunissen
 

Lu Gan wrote:

Hi,

Is there a simple way to execute a SQL statement against an Oracle
database from the command line and have the result output to stdout or
to a file? I am expecting something like:

$ sqlplus2 connect-string -e "sql-statement" > /tmp/file.1

Any help will be greatly appreciated!

- Lu