UNIX for Dummies Questions & Answers

#! /bin/ksh
...
# Create a SQL*Plus command processor co-process
sqlplus -s /nolog |&

# Connect to the database
print -p connect username/password@database

# Send SQL or PL/SQL block
print -p "
REM get_parameters()
SET HEAD OFF FEEDBACK OFF VERIFY OFF <- Set whatever formatting options you wish
COLUMN param FOLD_AFTER              <- This will cause all columns named param to be printed as individual rows
  SELECT 'Input_Record_Size:'   || input_record_size_no    param <- All columns are named param
        ,'Output_Record_Size:'  || output_record_size_no   param
        ,'Convert_to_ASCII:'    || convert_to_ascii_fl     param
        ,'Staging_Table:'       || load_table_name         param
        ,'SQLLoad_Control_File:'|| sqlload_control_file    param
    FROM load_parameters
   WHERE system_code  ='${SYSTEM_CD}' <- Pass whatever criteria you need
     AND file_type    ='${FILE_TYPE}'
ORDER BY load_priority
/
SET HEAD ON FEEDBACK ON VERIFY ON
COLUMN param CLEAR
PROMPT sql_complete <- This is very important.  If you don't print an arbitrary message that you can search for, the while loop below will hang while waiting for more information from Oracle
"

# Get responses back from Oracle
while read -p LINE
do
    # Look for our loop termination marker and break out when found
    if print $LINE | grep sql_complete
    then
        break

    # Process any Oracle errors
    elif print $LINE | grep Oracle error message(s)
    then
        print "Oracle error occurred"
        exit 1

    # Start setting variables based on return values
    elif print $LINE | grep "Input_Record_Size:"
    then
        INPUT_RECORD_SIZE=$(print $LINE | awk -F: '{print $2}')
        continue
    elif print $LINE | grep "Output_Record_Size:"
    then
        OUTPUT_RECORD_SIZE=$(print $LINE | awk -F: '{print $2}')
        continue
    ...more tests...
    fi
done

print "Input record size is: ${INPUT_RECORD_SIZE}"
print "Output record size is: ${OUTPUT_RECORD_SIZE}"
...
# Now a PL/SQL block
print -p "
REM Invoke stored procedure
SET HEAD OFF VERIFY OFF FEEDBACK OFF SERVEROUTPUT ON
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_rc PLS_INTEGER;
BEGIN
    v_rc := please_raise_my_salary (${MY_ID});
    COMMIT;
    dbms_output.put_line('RC=:' || v_rc);
END;
/
SET HEAD ON VERIFY ON FEEDBACK ON SERVEROUTPUT OFF
PROMPT sql_complete
"

while read -p LINE
do
    if print $LINE | grep "sql_complete"
    then
        break
        
    ...same error tests as before...

    # Test for stored procedure return value
    elif print $LINE | grep "RC="
    then
        RETURN_VALUE=$(print $LINE | awk -F= '{print $2}')
        if [ 0 -eq ${RETURN_VALUE} ]
        then
            print "Finally, now I can buy another pair of shoes!"
        else
            print "Maybe I can have them re-soled"
        fi
    fi
done