Shell Programming and Scripting

1)
sqlplus -s $ORA_CONN << EOF!
           SPOOL ${rep_name}report_${rep_part}_${sysdate1}.txt;
        SELECT * FROM customer WHERE col1=2001
           AND (  col2='sumthing')
        UNION
        SELECT * FROM customer WHERE col1=2001
        AND (condition);
        Spool off;
exit
EOF!
;;
2)
sqlplus -s $ORA_CONN << EOF!
           SPOOL ${rep_name}report_${rep_part}_${sysdate1}.txt;
        SELECT * FROM customer WHERE col1=2002
           AND (  col2='sumthing')
        UNION
        SELECT * FROM customer WHERE col1=2002
        AND (condition);
        Spool off;
exit
EOF!
;;

./script_name.ksh 1
./script_name.ksh 2
...
./script_name.ksh 25

#!/bin/ksh
./script_name.ksh 1 &
./script_name.ksh 2 &
...
./script_name.ksh 25 &

SQL*Plus: Release 10.2.0.2.0 - Production
reprompting on error.
 
Runs the specified SQL*Plus script from a web server (URL) or the
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
-M "<options>" Sets automatic HTML markup of output. The options
local file system (filename.ext) with specified parameters that
have the form:
will be assigned to substitution variables in the script.
Usage 1: sqlplus -H | -V
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
+ [ 0 -ne 0 ]
+ echo export report_run_date=07/12/2010
-H Displays the SQL*Plus version and the
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
usage help.
When SQL*Plus starts, and after CONNECT commands, the site profile
-R <level> Sets restricted mode to disable SQL*Plus commands
-V Displays the SQL*Plus version.
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
Usage 2: sqlplus [ [<option>] [<logon>] [<start>] ]
disables all user commands interacting with the
(e.g. login.sql in the working directory) are run. The files may
<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]
file system.
contain SQL*Plus commands.
-S Sets silent mode which suppresses the display of
 
-C <version> Sets the compatibility of affected commands to the
Refer to the SQL*Plus User's Guide and Reference for more information.
version specified by <version>. The version has
the SQL*Plus banner, prompts, and echoing of
the form "x.y[.z]". For example, -C 10.2.0
commands.
-L Attempts to log on just once, instead of
<logon> is: (<username>[/<password>][@<connect_identifier>] | /)
reprompting on error.
[AS SYSDBA | AS SYSOPER] | /NOLOG
-M "<options>" Sets automatic HTML markup of output. The options
have the form:
Specifies the database account username, password and connect
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
identifier for the database connection. Without a connect
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
identifier, SQL*Plus connects to the default database.
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
The AS SYSDBA and AS SYSOPER options are database administration
be 1, 2 or 3. The most restrictive is -R 3 which
privileges.
disables all user commands interacting with the
+ [ 0 -ne 0 ]
file system.
The /NOLOG option starts SQL*Plus without connecting to a
database.
-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]
commands.
 
Runs the specified SQL*Plus script from a web server (URL) or the
<logon> is: (<username>[/<password>][@<connect_identifier>] | /)
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.
[AS SYSDBA | AS SYSOPER] | /NOLOG
 
When SQL*Plus starts, and after CONNECT commands, the site profile
Specifies the database account username, password and connect
 
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
+ 0<<
identifier for the database connection. Without a connect
(e.g. login.sql in the working directory) are run. The files may
identifier, SQL*Plus connects to the default database.
contain SQL*Plus commands.
 
Refer to the SQL*Plus User's Guide and Reference for more information.
The AS SYSDBA and AS SYSOPER options are database administration
privileges.

 
for i in select id from customer
do
./script_name.ksh $i &
done

 
sqlplus -s $ORA_CONN <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
spool abc.txt
BEGIN
   FOR i IN (SELECT ID FROM customer)
   LOOP
      DBMS_OUTPUT.put_line ( 'value ' || i.ID);
           END LOOP;
END;
/
spool off
EOF

sqlplus -s $ORA_CONN <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
spool abc.txt
BEGIN
   FOR i IN (SELECT ID FROM customer)
   LOOP
      DBMS_OUTPUT.put_line ( 'value ' || i.ID);
   END LOOP;
END;
/
spool off
EOF

#!/bin/ksh

    # First list out Column ids in abc.txt file
sqlplus -s $ORA_CONN <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
spool abc.txt
BEGIN
   FOR i IN (SELECT ID FROM customer)
   LOOP
      DBMS_OUTPUT.put_line ( 'value ' || i.ID);
   END LOOP;
END;
/
spool off
EOF

    # Now for each data in abc.txt, execute second query in background
for myID in `cat abc.txt | xargs`
do
sqlplus -s $ORA_CONN <<EOF!
        SPOOL ${rep_name}report_${rep_part}_${sysdate1}.txt;
        SELECT * FROM customer WHERE col1="$myID"
           AND (  col2='sumthing')
        UNION
        SELECT * FROM customer WHERE col1="$myID"
        AND (condition);
        Spool off;
exit
EOF! &
done