Lesson 1

You have already seen some examples of parameters in previous modules in this course. This module examines PL/SQL Stored Object parameters in detail.

By the end of this module, you will be able to:

1. Describe the uses of parameters in SQL*Plus and PL/SQL
2. Use parameters when starting a SQL*Plus script
3. Describe the ways in which parameters are used in PL/SQL
4. Place a parameter in a procedure
5. Use parameters and the RETURN command appropriately
6. Identify correct syntax for using parameters with cursors

In the realm of PL/SQL programming, the usage and importance of stored object parameters can vary significantly depending on the specific requirements and context of the database applications being developed. However, some parameters are commonly utilized across a wide range of PL/SQL stored objects, such as procedures, functions, packages, and triggers. Here are five such parameters that are frequently used, albeit the relevance may vary based on individual use cases:

1. IN Parameters: These are the most common type of parameters. They are used to pass values to the stored object from the calling environment. The stored object can use these values, but any changes made to these parameters do not affect the actual values in the calling environment.
2. OUT Parameters: These allow the stored object to pass values back to the calling environment. Unlike IN parameters, any changes made to OUT parameters within the stored object are reflected in the calling environment, making them suitable for returning data from the procedure or function.
3. IN OUT Parameters: Combining the characteristics of both IN and OUT parameters, IN OUT parameters allow a stored object to receive values, process them, and then return the results back to the calling environment. This dual functionality makes them versatile for various applications.
4. Default Parameters: These parameters allow default values to be set, which means they are optional when the stored object is called. If a value for a default parameter is not provided during the call, the procedure or function uses the default value specified in its declaration.
5. Cursor Parameters: In more complex PL/SQL applications, cursor parameters are used to pass cursor variables to and from stored procedures and functions. This is particularly useful in scenarios where multiple rows of data need to be processed or when dynamic SQL is involved.

It's important to note that the selection and use of these parameters should be guided by the specific requirements of the database operation and the best practices of PL/SQL programming. The effectiveness of any parameter depends on how appropriately it is used in the context of the stored object's logic and the overall database design.

SQL*Plus is a tool used to enter SQL commands and display the output. It is provided with every Oracle installation, whether on Windows, Unix, or Linux. It is a command line interface and supports editing, user input, and report formatting. In 11g, SQL*Plus for Windows (sqlplusw.exe) is no longer part of the client or database install. The command line version (sqlplus.exe) is still available. You can use an older version of SQL*Plus for Windows to connect to an 11g database, but some functionality may not be supported. SQL Developer, covered in Chapter 2, is a GUI interface that is shipped with 11g and should be considered the replacement for SQL*Plus for Windows. To start SQL*Plus, simply type sqlplus at the command prompt or after starting a DOS command session in Windows. Under normal circumstances, SQL*Plus prompts you for a username and corresponding password. If you are able to provide a valid username/password combination, the SQL> prompt appears on your screen to indicate that you have successfully established a session. You can also start SQL*Plus with the username and password at the command line. In this case, if the username/password are valid, the SQL> prompt will appear. If not, you will be asked to enter a valid username and password.
The next lesson shows how to place a parameter in a procedure.