passing variables in sqlplus scripts

The script snippet you provided is an SQL*Plus script used to interactively accept parameters and execute a stored procedure with those parameters. Here’s a detailed breakdown of each component and how it works:

Explanation of the Script:

1. SET VERIFY OFF

   • This command disables the verification of substitution variables in SQLPlus. When VERIFY is set to OFF, SQLPlus does not display the text of substituted variables, which can make the output cleaner.

2. ACCEPT par1 prompt "ENTER PARAMETER #1: "

   • The ACCEPT command is used to prompt the user for input. In this case, it asks the user to enter a value  par1 and displays the prompt message "ENTER PARAMETER #1: ". The value entered by the user is stored in the variable par1.

3. ACCEPT par2 prompt "ENTER PARAMETER #2: "

   • Similarly, this command prompts the user to enter a value for par2, with the prompt message "ENTER PARAMETER #2: ". The value entered by the user is stored in the variable par2.

4. execute pkg_TEST_VARIABLES.TEST_PASS_VARIABLES ( &&par1, &&par2);

   • This line executes a stored procedure TEST_PASS_VARIABLES from the package pkg_TEST_VARIABLES. The && notation is used to reference the variables par1 and par2 which were previously set by the ACCEPT commands. The double & notation ensures that the variables are resolved at runtime.

Putting It All Together:

When you run this SQL*Plus script, the following sequence of actions occurs:

1. Prompting for Input:

   • The script first prompts you to enter two parameters: par1 and par2.

2. Executing the Procedure:

   • After you enter the parameters, the script executes the procedure TEST_PASS_VARIABLES from the package pkg_TEST_VARIABLES, passing the entered parameters to the procedure.

Example Execution:

Assuming you have a stored procedure defined as follows:

sql

CREATE OR REPLACE PACKAGE pkg_TEST_VARIABLES AS
  PROCEDURE TEST_PASS_VARIABLES(p1 IN VARCHAR2, p2 IN VARCHAR2);
END pkg_TEST_VARIABLES;
/

CREATE OR REPLACE PACKAGE BODY pkg_TEST_VARIABLES AS
  PROCEDURE TEST_PASS_VARIABLES(p1 IN VARCHAR2, p2 IN VARCHAR2) IS
  BEGIN
    -- Procedure logic here
    DBMS_OUTPUT.PUT_LINE('Parameter 1: ' || p1);
    DBMS_OUTPUT.PUT_LINE('Parameter 2: ' || p2);
  END TEST_PASS_VARIABLES;
END pkg_TEST_VARIABLES;
/

Running the script will prompt for input:

ENTER PARAMETER #1: value1
ENTER PARAMETER #2: value2

After providing the values, the procedure will be executed, and you will see the output:

Parameter 1: value1
Parameter 2: value2

Summary:

• SET VERIFY OFF: Clean output by turning off variable verification.
• ACCEPT: Prompts for user input and stores it in variables.
• execute: Executes a stored procedure with the provided parameters.

This script is useful for testing stored procedures interactively or running scripts that require user inputs.