2.10 SQLPlus with Korn Shell


2.10 SQL*Plus with Korn Shell

There are three topics covered in this section.

  • Invoking SQL*Plus from Korn Shell

  • Invoking Korn Shell from SQL*Plus

  • Passing arguments between languages

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 
  • We need to exit from SQL*Plus ”to take us out of SQL*Plus and back to the Korn Shell. Thus we include EXIT, which is the last SQL*Plus statement within this script.

  • The "-s" is a "Silent Mode" option that suppresses superfluous messaging by SQL*Plus.

  • Within the SQL*Plus script, the Korn Shell parameter notation ($) is used.

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.

Korn Shell Function

Description

the_tablenames_are()

This generates a list of table names.

gen_table_report()

For each table name argument, this function displays all column names.

main()

This is where execution starts. This portion of the code pipes the output from the function "the_tablenames_are" into the function "gen_table_report."

 
 ############################################### # # 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.



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net