2.10 SQL*Plus with Korn ShellThere are three topics covered in this section.
To embed SQL*Plus within Korn Shell, use Korn Shell Here-Documents. This Korn shell language feature allows "input redirection" to specify "in-stream text." The syntax uses the symbol "<<," which is followed by any character string ”our example uses "EOF". Following "<< EOF" is the input-stream text consisting of SQL*Plus command file. The following shows just a Korn Shell script that prints the first two arguments. #!/bin/ksh # KSH script to echo to parameters. echo echo # end of script Korn shell precedes positional parameters with a "$" while SQL*Plus uses "&". We can embed a SQL*Plus script within a Korn Shell script and run the Korn Shell script, passing arguments on the command line that are then passed to the SQL*Plus script. The following Korn shell script accepts three arguments: username, password, and a string used to match column names . The column_name has scope within the SQL*Plus script. #!/bin/ksh # KSH script filename: script_01.ksh username= password= column_match= sqlplus s ${username}/${password} << EOF SELECT table_name, column_name FROM user_tab_columns WHERE column_name LIKE UPPER ('${column_match}'); exit EOF # end of script
SQL*Plus scripts can be encapsulated into distinct Korn Shell functions. The following table is a description of the Korn Shell functions used in the script.
############################################### # # FUNCTION: the_tablenames_are # # ---------------------------------------------- function the_tablenames_are { sqlplus -s $username/$password << EOF set feedback off set pagesize 0 set echo off select table_name from USER_TABLES; exit; EOF } ############################################### # # FUNCTION: gen_table_report # # ---------------------------------------------- function gen_table_report { sqlplus -s $username/$password << EOF set feedback off set pagesize 0 set echo off SELECT table_name, column_name FROM USER_TAB_COLUMNS WHERE table_name = ''; exit; EOF } ############################################### # # MAIN program code # # ---------------------------------------------- username= password= the_table_names_are while read tn do gen_table_report $tn done # ############################################### SQL*Plus can "host out" to an operating system command using the SQL*Plus host command. Within a SQL*Plus script, you can include the SQL*Plus HOST command followed by any host command. The following is a revision of the aforementioned KSH script ”this is just the SQL*Plus portion. This generates a spool file and immediately opens the spool file in an editor window. SPOOL output SELECT table_name, column_name FROM user_tab_columns WHERE column_name LIKE UPPER ('${column_match}'); SPOOL off HOST vi output.lst When you run the aforementioned script, the output immediately pops up for viewing. The same can be done in Windows using: HOST NOTEPAD OUTPUT.LST. |