Solved

SQL script basic question

Posted on 2002-07-01
1
263 Views
Last Modified: 2012-08-13
Just learning SQL scripts and want to know what command is used to call other scripts, or to do a compile.  In the following script, what is calling other scripts?

Thanks.



    1  #!/bin/ksh
     2  #############################################################################
     3  # Name: mig_event_update
     4  #
     5  # Description: Add/update migration event status.
     6  #
     7  # Parms:    migration request key
     8  #           object name
     9  #           event status
    10  #           event message
    11  #############################################################################
    12  # Retrieve parms
    13  #############################################################################
    14  rqst_key=$1
    15  obj_name=$2
    16  status=$3
    17  if [ $# -eq 4 ]
    18  then
    19    event_msg=$4
    20  else
    21    event_msg=" "
    22  fi
    23  #############################################################################
    24  # Initialize
    25  #############################################################################
    26  SCRIPT_ID="10"
    27  MIG_LOG_HDR="+$MIG_PROCESS_ID.$rqst_key.$rqst_lvl.$SCRIPT_ID"
    28  UPDATE_OUT=$MIG_PATH/mig_event_update.out.$$
    29  RC=0
    30  #############################################################################
    31  # Function: check_spool_file
    32  # 1) Verify that spool file was created.  (If not, the sqlplus call
    33  #    was not successful.)
    34  # 2) Delete blank lines and SQL connection prompts that are spooled as
    35  #    part of the results of the sqlplus call. (NOTE: If sqlplus prompts
    36  #    change, the ex commands below must be changed accordingly.)
    37  # 3) Check the spool file for errors.
    38  #############################################################################
    39  check_spool_file()

    39  check_spool_file()
    40  {
    41  SPOOL_FILE=$1
    42
    43  if [ ! -s $SPOOL_FILE ]
    44  then
    45    return -2
    46  fi
    47
    48  ex - $SPOOL_FILE <<- end-of-script
    49    g/SQL>/d
    50    g/PL\/SQL/d
    51    g/^[      ]*$/d
    52    wq
    53  end-of-script
    54
    55  if [ `grep -c "ERROR:" $SPOOL_FILE` -ne 0 ]
    56  then
    57    return -1
    58  fi
    59
    60  return 0
    61  }
    62  #############################################################################
    63  # Function: log_spool_file
    64  # Copy a spool file to the migration log.
    65  #############################################################################
    66  log_spool_file()
    67  {
    68  SQL_FILE=$1
    69  SPOOL_FILE=$2
    70
    71  while read line
    72  do
    73    log_msg "$SQL_FILE" "$line"
    74  done < $SPOOL_FILE
    75
    76  return
    77  }
    78  #############################################################################
    79  # Function:  log_msg
    80  # Write a message to the migration log
    81  #############################################################################
    82  log_msg()
    83  {
    84  if [ $# -eq 2 ]
    85  then
    86    echo `date +%T`."$MIG_LOG_HDR.$1: $2" >> $MIG_LOG
    87  else
    88    echo `date +%T`."$MIG_LOG_HDR: $1" >> $MIG_LOG
    89  fi
    90
    91  return
    92  }
    93  #############################################################################
    94  # MAIN
    95  #############################################################################
    96  . $TOOLS_PATH/envdictut1
    97  sqlplus dicttools/dicttools <<- end_sql
    98    spool $UPDATE_OUT
    99    @$SQL_PATH/upd_mig_event_status $rqst_key "$obj_name" $status "$event_msg"
   100    spool off
   101    exit
   102  end_sql
   103
   104  check_spool_file $UPDATE_OUT
   105  if [ $? -ne 0 ]
   106  then
   107    log_msg "Update of migration event status for module $obj_name failed"
   108    log_spool_file "update_mig_event_status" $UPDATE_OUT
   109    RC=-1
   110  fi
   111
   112  rm -f $UPDATE_OUT
   113
   114  exit $RC

0
Comment
Question by:Vingamel
1 Comment
 
LVL 1

Accepted Solution

by:
gallo47 earned 20 total points
ID: 7123303
Just FYI, that is a Unix shell script which is calling sqlplus which is an Oracle utility. Might be better off posting this in Oracle.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question