#!/bin/ksh

# ------------------------------------------------------------------------------
# The first line (#!/bin/ksh) tells the computer to use the
# KornShell to run this script.  NOTE:  It must be the very
# first line of the script.  If other comments or commands
# are placed above this line, the script may not function
# properly.


#        script named ::  listJumpkits.sh
#             version ::  1

#  script designed by ::  a k i k o   b a l d r i d g e
#         modified by ::  t h e   t r o u b l e s h o o t i n g   n i n j a

#                date ::  September, 2006

# For contract 1383, the U.S. Army in Iraq, the customer needed a list of
# all civil records.

# NOTES: 

# ----------------------------------------------------------
# Check the username.  Since this script uses SQL*Plus,
# only oracle will be allowed to run this script.


  if [[ "$USER" != "oracle" ]]
    then
      echo ""
      echo "\tYou are currently logged in as "$USER"."
      echo "\tPlease log in as oracle."
      echo ""
      exit 0
  fi

# ----------------------------------------------------------
# Start the SQL*Plus program.


  sqlplus -s ads/printrak << EOF


------------------------------------------------------------
-- Set the number of lines SQL*Plus prints before inserting
-- a page break.  The maximum number of lines allowed is
-- 50,000.


  set pagesize 0;
  set heading off;
  set feedback off;

------------------------------------------------------------
-- Set the total number of characters SQL*Plus displays on
-- one line before beginning a new line.


  set linesize 255;

------------------------------------------------------------
-- Display timing statistics on each SQL command or PL/SQL
-- block run.


  set timing off;

------------------------------------------------------------
-- Display the output of DBMS_OUTPUT.PUT_LINE.  Set the
-- buffer size to 1,000,000 bytes (maximum).


  set serveroutput on size 1000000;

------------------------------------------------------------
-- Set the width for displaying CLOB values.


  set long 3000;

------------------------------------------------------------
-- Change the default display of the external_id column from
-- 255 characters to 15 characters.


  column external_id format a48;

----------------------------------------------------------
-- SQL*Plus Query begins here.

select distinct external_id from ads_element
 ,              ads_external_id
 where          ads_element.parent_id = ads_external_id.element_id
 and		external_id like '%LSMS%'
 and            ext_id_type = 2;

----------------------------------------------------------
-- Exit SQL*Plus.

  quit
~
~