PARAMETER:
Generally when we execute any query or an argument the parameter acts as a placeholder for the values of a
variable and return those values during the execution of stored procedure.
Example: Assume a simple query:
UPDATE Cust SET Cust_name = :Cust_name, Add = :Add WHERE Cust_id = :Cust_id;
Now, writing the code to set the parameter for the above query:
OracleCommand myCommand1;
...
myCommand1.CommandText = "UPDATE Cust SET Cust_name = :Cust_name, " +
"Add = :Add WHERE Cust_id = :Cust_id";
myCommand1.Parameters.Add("Cust_name", "ABC");
myCommand1.Parameters.Add("Add", "A11, GHZ");
myCommand1.Parameters.Add("Cust_id", 1111);
PARAMETER FILES:
Parameter file is a file that holds initialization parameter list along with the individual parameter values.
Oracle provides two types of parameter files:
1) PFILE also known as INIT.ORA
2) SPFILE
Both the files are by default located at:-
$ORACLE_HOME/dbs or %ORACLE_HOME%\database.
TYPES OF PARAMETER:
In oracle there are two types of parameter:
a) Formal Parameter
b) Actual Parameter
A) Formal Parameters
The parameters declared in the definition of procedure are called as formal parameters.
They receive the values sent while calling the procedure.
B) Actual Parameters
The values given within parentheses while calling the procedure are called as actual
Parameters.
MODES OF PARAMETER:
We can pass parameters to procedures and functions in three ways:
1) IN Mode
Likewise we pass parameter in programming language similarly, we can pass values to the stored
procedure using these parameters. IN parameter is read only parameter and the value of the IN
type parameter cannot be changed inside the procedure, only we can assign the value here.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name (
param_name1 IN datatype, param_name12 IN datatype ... )
2) OUT Mode
To send the output from a function or a procedure we use OUT parameter. While executing the
stored procedure the values to the OUT parameter cannot be passed, only we can assign the
values to the out parameter here.
Syntax:
CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)
3) INOUT Mode
It is same as IN and OUT modes when put together. It can get the values from calling program.
Here, we can pass the values in to a procedure and get the output values from the procedure.
Syntax:
CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)