plsql/wrap.sh

   1 #!/bin/bash
   2
   3 RL_HISTORY_FILE=${HOME}/.mal-history
   4 SKIP_INIT="${SKIP_INIT:-}"
   5
   6 ORACLE_LOGON=${ORACLE_LOGON:-system/oracle}
   7 SQLPLUS="sqlplus -S ${ORACLE_LOGON}"
   8
   9 FILE_PID=
  10 cleanup() {
  11     trap - TERM QUIT INT EXIT
  12     #echo cleanup: ${FILE_PID}
  13     [ "${FILE_PID}" ] && kill ${FILE_PID}
  14 }
  15 trap "cleanup" TERM QUIT INT EXIT
  16
  17
  18 # Load the SQL code
  19 if [ -z "${SKIP_INIT}" ]; then
  20     out=$(echo "" | ${SQLPLUS} @$1)
  21     if echo "${out}" | grep -vs "^No errors.$" \
  22         | grep -si error >/dev/null; then
  23     #if echo "${out}"  | grep -si error >/dev/null; then
  24         echo "${out}"
  25         exit 1
  26     fi
  27 fi
  28
  29 # open I/O streams
  30 echo -e "BEGIN io.open(0); io.open(1); END;\n/" \
  31     | ${SQLPLUS} >/dev/null
  32
  33 # Stream from table to stdout
  34 (
  35 while true; do
  36     out="$(echo "SELECT io.read(1) FROM dual;" \
  37         | ${SQLPLUS} 2>/dev/null)" || break
  38     #echo "out: [${out}] (${#out})"
  39     echo "${out}"
  40 done
  41 ) &
  42 STDOUT_PID=$!
  43
  44 # Perform readline input into stream table when requested
  45 (
  46 [ -r ${RL_HISTORY_FILE} ] && history -r ${RL_HISTORY_FILE}
  47 while true; do
  48     prompt=$(echo "SELECT io.wait_rl_prompt(0) FROM dual;" \
  49         | ${SQLPLUS} 2>/dev/null) || break
  50     # Prompt is returned single-quoted because sqlplus trims trailing
  51     # whitespace. Remove the single quotes from the beginning and end:
  52     prompt=${prompt%\'}
  53     prompt=${prompt#\'}
  54     #echo "prompt: [${prompt}]"
  55
  56     IFS= read -u 0 -r -e -p "${prompt}" line || break
  57     if [ "${line}" ]; then
  58         history -s -- "${line}"        # add to history
  59         history -a ${RL_HISTORY_FILE}  # save history to file
  60     fi
  61
  62     # Escape (double) single quotes per SQL norm
  63     line=${line//\'/\'\'}
  64     #echo "line: [${line}]"
  65     ( echo -n "BEGIN io.writeline('${line}', 0); END;";
  66       echo -en "\n/" ) \
  67         | ${SQLPLUS} >/dev/null || break
  68 done
  69 echo -e "BEGIN io.close(0); END;\n/" \
  70     | ${SQLPLUS} > /dev/null
  71 ) <&0 >&1 &
  72
  73
  74 # File read if requested
  75 (
  76 while true; do
  77     files="$(echo "SELECT path FROM file_io WHERE in_or_out = 'in';" \
  78         | ${SQLPLUS} 2>/dev/null \
  79         | grep -v "^no rows selected")" || break
  80     for f in ${files}; do
  81         if [ ! -r ${f} ]; then
  82             echo "UPDATE file_io SET error = 'Cannot read ''${f}''' WHERE path = '${f}' AND in_or_out = 'in';" \
  83                 | ${SQLPLUS} >/dev/null
  84             continue;
  85         fi
  86         IFS= read -rd '' content < "${f}"
  87         # sqlplus limits lines to 2499 characters so split the update
  88         # into chunks of the file ORed together over multiple lines
  89         query="UPDATE file_io SET data = TO_CLOB('')"
  90         while [ -n "${content}" ]; do
  91             chunk="${content:0:2000}"
  92             content="${content:${#chunk}}"
  93             chunk="${chunk//\'/\'\'}"
  94             chunk="${chunk//$'\n'/\\n}"
  95             query="${query}"$'\n'"    || TO_CLOB('${chunk}')"
  96         done
  97         query="${query}"$'\n'" WHERE path = '${f}' AND in_or_out = 'in';"
  98         echo "${query}" | ${SQLPLUS} > /dev/null
  99         #echo "file read: ${f}: ${?}"
 100     done
 101     sleep 1
 102 done
 103 ) &
 104 FILE_PID=$!
 105
 106 res=0
 107 shift
 108 if [ $# -gt 0 ]; then
 109     # If there are command line arguments then run a command and exit
 110     args=$(for a in "$@"; do echo -n "\"$a\" "; done)
 111     echo -e "SELECT mal.MAIN('(${args})') FROM dual;" \
 112         | ${SQLPLUS} > /dev/null
 113     res=$?
 114 else
 115     # Start main loop in the background
 116     echo "SELECT mal.MAIN() FROM dual;" \
 117         | ${SQLPLUS} > /dev/null
 118     res=$?
 119 fi
 120 # Wait for output to flush
 121 wait ${STDOUT_PID}
 122 exit ${res}