AWSQLP

The AWSQLP program type script runs SQL*Plus scripts.

A program type launches a program type script that supports the program type. In the example below, the SQLP program type script launches from the sql directory. The AWSQLP program type script ships with Applications Manager and can be used as the basis for creating customized program types to meet the needs of your organization.

UNIX Program Type Script

Below is a listing of the UNIX SQL*Plus program type script that ships with Applications Manager. Line numbers have been added for reference purposes only, and do not appear in the actual script.

001| :

| #!/bin/sh

005| # $Header: EXEC-SQLP,v 1.1 96/01/22 09:42:26 soport Exp $

| #

| echo PATH Is

| echo $PATH

| if [ "$DEBUG" = "YES" ]; then

010| env

| set -x

| echo "in $0"

| fi

| if [ -f $program.sql ]; then

015| err=0

| else

| echo "file $program.sql does not exist"

| exit 1

| fi

020| echo sqlp `date`

| echo $ORACLE_PATH

| echo sqlplus -s @$program A$seq_no.lis $argu

| echo "define so_outfile=A$seq_no.lis" >> $par

| echo start $program >> $par

025| logtest=login.$$

| echo "$login

| spool $logtest

| prompt got it

| spool off

030| start $par A$seq_no.lis $argu

| "|sqlplus -s

| err=$?

| if [ -f $logtest ]; then

| rm $logtest

035| else

| echo Could not login to sqlplus

| err=1

| fi

| echo sqlp `date`

040| if [ -f A$seq_no.lis ]; then

| mv A$seq_no.lis $file

| fi

| $AW_HOME/exec/FILESIZE $file $err

| err=$?

045| exit $err

The key elements of the script are described in the table below.

Lines	Description
7-8	Echoes information into the system log (in this case showing the PATH variable).
9-12	Checks if DEBUG is set. (DEBUG is set in BODY.bat.)
14	Checks if Program exists. If not, err=1.
23-24	Sets up output capturing and identifies the program to be run. Line 23 echoes the command line passed to SQL*Plus. Line 24 calls the actual command line.
26-31	Logs into SQL*Plus, runs the program, and captures the output.
43	If the file exists, runs the FILESIZE script. This script registers the output (i.e. creates an entry on the Output Files tab and associates the output listing with a file).
32, 44-45	Captures and passes the return code back to Applications Manager.

Windows Program Type Script

The Windows sqlp program type script that ships with Applications Manager is shown below.

Program Type Script

1 |if Not "%DEBUG%" == "YES" goto notdebug

| set

| echo on

| echo in %0

5 |:notdebug

|if exist %program%.sql goto exists

|echo "output file %program%.sql does not exist"

|set err=1

|goto end_of_it

10|:exists

|echo in %0

|echo plus31 -s @%program% A%seq_no%.lis %argu%

|set err=0

|echo define so_outfile=A%seq_no%.lis >> %par%

15|echo start %program% >> %par%

|echo quit >> %par%

|SET sqlplus=d:\ORANT\BIN\plus80.exe

18|echo %AW_HOME%\c\ntspawn "%sqlplus% -s %db_login%/passwd

@%PAR% A%seq_no%.lis "

19|call %AW_HOME%\c\ntspawn "%sqlplus% -s %LOGIN% @%PAR%

A%seq_no%.lis "

20|if errorlevel 1 set err=1

|if exist %file% del %file%

|if exist A%seq_no%.lis move A%seq_no%.lis %file%

|if not exist %file% echo no output from %module%

|if exist %file% call PRINTEM

25|:end_of_it

Explanation of the Script

The key elements of the script are described in the table below.

Lines	Description
1-5	Checks to see if DEBUG is set. (DEBUG is generally set in BODY.bat.)
6-7	Checks if Program exists. If not, err=1.
11	Writes output to log file.
12-15	Shows command line and arguments, then passes them to the %par%.
17	Sets SQL*Plus execution interpreter.
18-19	Line 18 echoes the commands passed to SQLPlus. Line 19 calls the actual* command line.
20	Checks error log from SQLPlus. If SQLPlus returns an error, set err=1. This ensures that the error is passed back to Applications Manager.
21-22	Deletes any %file%. If output from this current task exists, renames it A%seq_no%.lis%file%. Assigns a unique file name using the Applications Manager %seq_no%.
24	If the file exists, runs the PRINTEM script. This script registers the output (i.e. creates an entry on the Output Files tab and associates output listing with a file).