2.9 Command Line Arguments


You can use positional parameters with the symbols: &1, &2, &3, and so forth. Here is an example of a SQL script that takes two parameters. The intent here is for these arguments to be passed on the command line; first, the source code of the script (a total of five lines including an initial comment line), then the session that runs it.

 
  --  filename MY_QUERY.sql   my_query.sql line  1  set verify off SELECT * FROM students WHERE student_name = '&1' AND college_major = '&2';   my_query.sql line  5   SQL>  @my_query John Business   Run with 2 parameters    STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS   ---------- ------- -------------------- ------   A101       John    Biology              Degree   A102       Mary    Math/Science         Degree   SQL>  
  • The command SET VERIFY OFF is a SQL*Plus session command that merely suppresses the "before" and "after" substitution values. You get "cleaner" output with verify off.

  • The parameters are exact substitutions. If the parameter expression you pass needs to be in quotes within the SELECT statement, then embed the substitution parameter (&1 and &2) within single quotes.

The arguments we passed on the command line are "John" and "Business." These are in "Initial Caps." String comparisons within SQL are case sensitive; that is, "Business" is not the same as "BUSINESS." We can get around this. We can relieve the end user from being concerned with case by using SQL functions: INITCAPS(), which converts a string to initial caps, UPPER() and LOWER().

The parameters &1 and &2, embedded in single quotes, can further be embedded as arguments to an Oracle SQL function. The following is a scenario that places a command line argument within single quotes and further, within an Oracle SQL function, UPPER().

The following example is a parameter-driven data dictionary query. Data dictionary views, such as the data dictionary view USER_TAB_COLUMNS, store values in all upper case. You want to know all table and column names that contain a column named STATUS, or possibly with a name similar to STATUS. The following SQL statement is a starting point.

 
 SELECT table_name, column_name FROM user_tab_columns WHERE column_name LIKE '%STATUS%'; 

This SQL query can be generalized into a parameter-driven script. To do this, first make the column name a command line argument. Secondly, alleviate the need for the user to type in upper case. Use the built-in function upper case.

 
  --  filename QUERY_V1.sql   query_v1.sql line  1  set verify off SELECT table_name, column_name FROM   user_tab_columns WHERE  column_name LIKE UPPER('%&1%');   query_v1.sql line 5   

The aforementioned script, QUERY_V1.SQL, allows a user to look for any table and column with a column name like "STATUS." The user can type "Status," "status," or "STatus."

 
  SQL>  @query_v1 status 

The symbol notation (&1, &2, etc.) is used for positional parameters passed on the command line. You can insert named parameters and be prompted for their value. This script performs the same query.

 
  --  filename QUERY_V2.sql   query_v2.sql line  1  set verify off SELECT table_name, column_name FROM   user_tab_columns WHERE  column_name LIKE UPPER('%&column_name%');   query_v2.sql line 6   

Execution of the aforementioned script by typing the script name only prompts you for "column_name" parameter value. Following the prompt, we enter "status."

 
  SQL>  @query_v2  Enter value for column_name:  status  TABLE_NAME                    COLUMN_NAME   ----------------------------- -----------------------------  STUDENTS                      STATUS 

What if we have positional parameters, such as &1 and &2, and do not pass values on the command line? In that case we are prompted with a string that requests the input value for the argument names "1," "2," and so forth. The following is the result of running the first query without passing command line arguments. We are prompted for the parameter value.

 
  SQL>  @query_v1  Enter value for 1:  status  TABLE_NAME                 COLUMN_NAME   -------------------------- ------------------------------   STUDENTS                   STATUS  

You can provide your own prompt. The following, QUERY_V3, includes a SQL*Plus ACCEPT statement that specifically asks for the value of the "column_name" parameter.

 
  --  filename QUERY_V3.sql   query_v3.sql line 1   set verify off accept column_name prompt 'Enter a column name: ' SELECT table_name, column_name FROM   user_tab_columns WHERE  column_name LIKE UPPER('%&column_name%');   query_v3.sql line 6    SQL>  @query_v3  Enter a column name:  status  TABLE_NAME               COLUMN_NAME   ------------------------ -----------   STUDENTS                 STATUS  

Names within a script, such as "&column_name," make the script more readable. If you use positional parameters because you often run the script with command line arguments, you can still make the script more readable by assigning the positional parameters to named parameters. This achieves the objective: command line arguments and formal parameter names like "&column_name." You can use both when you DEFINE a formal, more meaningful parameter name with the value of a positional parameter. The following script, uses "column_name" within the body, but the script immediately assigns to this variable the value of the first positional parameter.

 
  --  filename QUERY_V4.sql   query_v4.sql line 1   set verify off define column_name=&1 SELECT table_name, column_name FROM   user_tab_columns WHERE  column_name LIKE UPPER('%&column_name%'); 

SQL*Plus parameters can be passed as arguments to a PL/SQL procedure. The following procedure has two parameters. The first parameter is a string; the second is a number.

 
 PROCEDURE my_procedure (v_param_1 VARCHAR2,                         v_parm_2 NUMBER) 

We also have a script that accepts two parameters: the first is a string, the second a number. From within the body of the SQL*Plus script, we can invoke our procedure, passing the values from the command line. Keep in mind that we are passing a literal expressing to our procedure, which means that if we pass STATUS (using no quotes) and 4 on the command line, this is the same as invoking the stored procedure with

 
 my_procedure (STATUS, 4) 

This is not valid. For STATUS to be a literal string, it must be in single quotes. That is, we want the following expansion:

 
 my_procedure ('STATUS', 4) 

We must code the following syntax within the SQL*Plus script:

 
 'parameter_name' 

The SQL*Plus call to the PL/SQL procedure is:

 
 my_procedure ('  parameter_name  ', etc) 

When passing parameters to where a string is expected, always further embed the parameter within single quotes. Below is a SQL*Plus script that embeds the first command line argument within single quotes. A stored procedure is invoked from within a SQL*Plus script with the SQL*Plus EXECUTE command.

 
  --  filename QUERY_V5.sql   query_v5.sql line 1   set verify off define column_name=&1 define other_parameter=&2  . . . SQL*Plus statements  execute my_procedure('&column_name', &second_parameter) . . . SQL*Plus statements 

We can execute the SQL*Plus script with the following:

 
 SQL> @query_5 status 120 

The SQL*Plus script now invokes the stored procedure with the proper parameters.

 
 my_procedure ('status', 120) 

The following topics will be covered.

  • SET SERVEROUTPUT ON

  • DBMS_OUTPUT

  • EXECUTE

SET SERVEROUTPUT ON is a SQL*Plus command that is required just once per SQL*Plus session. If you do not type this on the SQL*Plus command line, you will not see output from DBMS_OUTPUT.

From SQL*Plus you can execute any procedure, function, or package program by typing EXECUTE and the program name.

The DBMS_OUTPUT package has several procedures, but the procedure you most frequently use to print data, numbers , or text is PUT_LINE. To illustrate , the following is a SQL*Plus script that selects data from the STUDENTS table. This script returns students that have a particular major or have a particular status.

 
  --  filename QUERY_V6.sql   query_v6.sql line 1   set verify off set serveroutput on set feedback off define p_major=&1 define p_status=&2 execute dbms_output.put_line('Parm 1=''&p_major'); execute dbms_output.put_line('Parm 2=''&p_status'); SELECT * from students WHERE college_major=('&p_major') OR status=('&p_status'); 

Passing as parameters, "Math" for the college major and "Degree" for a student status:

 
  SQL>  @query_v6 Math Degree  Parm 1=Math   Parm 2=Degree   STUDENT_ID STUDENT_NA COLLEGE_MAJOR        STATUS   ---------- ---------- -------------------- ------   A101       John       Biology              Degree   A102       Mary       Math/Science         Degree  
  • You will notice a SQL*Plus command, SET FEEDBACK OFF. This is a SQL*Plus command that suppresses SQL*Plus messages.

  • The calls to DBMS_OUTPUT.PUT_LINE() use the concatenation operator "". This is the operator to perform string concatenation.



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