Notations of Passing Parameters in Oracle Procedure

Notations of Passing Parameters in Oracle Stored Procedure

In this article, I am going to discuss Notations of Passing Parameters in Oracle Stored Procedure with Examples. Please read our previous article IN/OUT Parameters in Oracle Stored Procedure with Examples.

Notations of Passing Parameters in Oracle Procedure

Now, we will discuss the available notations in oracle while passing parameters to stored procedures in oracle. When calling a subprogram, you can write the actual parameters using the following notations:

  1. Positional
  2. Named
  3. Mixed

Before understanding each and every parameter, let’s go ahead and create a table called PRODUCTS where we have columns like PROD_ID, PROD_NAME, and PROD_TYPE. We will be having a constraint with the primary key as PRODUCTS_PK.

CREATE TABLE PRODUCTS
( 
  PROD_ID NUMBER,
  PROD_NAME VARCHAR2(20),
  PROD_TYPE VARCHAR2(20),
  CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID)
);

Let us go ahead and execute the create table statement.

Notations of Passing Parameters in Oracle Stored Procedure with Examples Description automatically generated

So, the table PRODUCTS is created. Now, let us try to create a procedure on the table PRODUCTS. We are having three IN parameters P_PROD_ID, P_PROD_NAME, and P_PROD_TYPE where if we pass these parameters into this procedure then those parameters will be inserted into the table as P_PROD_ID will be PROD_ID, P_PROD_NAME will be PROD_NAME and P_PROD_TYPE will be PROD_TYPE. Let us create the stored procedure by executing the following statements.

CREATE OR REPLACE PROCEDURE ADD_PRODUCTS
(
   P_PROD_ID NUMBER,
   P_PROD_NAME VARCHAR2,
   P_PROD_TYPE VARCHAR2
)
IS
BEGIN
  INSERT INTO PRODUCTS VALUES (P_PROD_ID,P_PROD_NAME,P_PROD_TYPE);
  COMMIT;

  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE ('ERROR IN INSERT ');
  DBMS_OUTPUT.PUT_LINE (SQLCODE);
  DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

The above procedure will help us to insert the data into the table PRODUCTS. This procedure takes values as input and then adds those values into the table PRODUCTS. The exception case will display us error as “ERROR IN INSERT”. Now, let us go ahead and execute the procedure.

Notations of Passing Parameters in Oracle Stored Procedure with Examples Description automatically generated

So, the procedure is compiled successfully. Let us try to differentiate between the notations for passing parameters to the oracle stored procedure. We have three methods for passing parameters. Let us learn each of the methods one by one.

Positional Notation for Passing Parameters in Oracle Stored Procedure:

In order to understand the positional notation of passing parameters to oracle stored procedure, we have to call the procedure as follows.

EXECUTE ADD_PRODUCTS(1,’Bajaj’,’Bike’);

So, from the above execute statement we can see that parameter 1 go to P_PROD_ID, Bajaj will be P_PROD_NAME, and Bike will be P_PROD_TYPE. This is positional. Each parameter will allocate the way we have mentioned in the procedure calling statement. So, mostly positional will determines the position of the parameter and the value. Let us go ahead and execute this statement.

Positional Notation for Passing Parameters in Oracle Stored Procedure

So, the procedure is now executed successfully. Now, let us go ahead and see the table PRODUCTS and verify if the record is inserted or not by executing the following select statement.

SELECT * FROM PRODUCTS;

Positional Notation for Passing Parameters in Oracle Stored Procedure

So, you can see the record has been inserted. Now, let us try to miss a parameter while calling the procedure as follows.

EXECUTE ADD_PRODUCTS(2, ‘SAMSUNG’);

So, if we execute this statement parameter 2 will go to P_PROD_ID and parameter SAMSUNG will go to P_PROD_NAME. We do not have another parameter for P_PROD_TYPE which will result in giving an error. Now, let us try to execute this statement and see how the compiler reacts.

Positional Notation for Passing Parameters in Oracle

You can see we got an error showing the wrong number or types of arguments. So, the procedure is expecting three parameters as input. But we have provided only two parameters. So, here we need to enter all the parameters that are mentioned in the procedures. Now, let us try to execute the same first execute statement and see if we get any errors or not.

EXECUTE ADD_PRODUCTS(1,’Bajaj’,’Bike’);

Positional Notation for Passing Parameters in Oracle

So, this is executed now, but we will be getting an error as record 1 is already present in the table. We will be getting constraint errors. Let’s go ahead and check the DBMS output and see if we get any errors.

Positional Notation for Passing Parameters in Oracle

We got a unique constraint error because we have mentioned the PROD_ID as the primary key constraint. So, this is how the positional parameters work in Oracle with Stored Procedure.

Named Notation for Passing Parameters in Oracle Stored Procedure:

This is very simple and, in this case, while calling the procedure we need to mention the parameter name. So, to execute the ADD_PRODUCTS procedure we need to call the procedure and pass the parameters as follows.

EXECUTE ADD_PRODUCTS (P_PROD_ID=>2, P_PROD_NAME=>’SAMSUNG’, P_PROD_TYPE=>’MOBILE’);

So, from the above statement, you can see that the parameters are given with parameter names. So, the PROD_ID is 2, PROD_NAME is SAMSUNG and PROD_TYPE is MOBILE. We have to initialize the value by mentioning the “=>” symbol in order to determine the parameter values. This sign is for name notation.

Even we can also change the order of parameters and call the procedure which will also execute the stored procedure successfully. This is because we are mentioning the parameter names and hence, we should not be worried about the parameter positions. Let us try to execute the stored procedure as follows.

Named Notation for Passing Parameters in Oracle Stored Procedure

So, the procedure is now executed successfully. Let us try to check the table products by executing the following select statement.

SELECT * FROM PRODUCTS;

Named Notation for Passing Parameters in Oracle Stored Procedure

So, the record is inserted now. Let us try to change the order and call the procedure with other values and see if this trick works or not.

EXECUTE ADD_PRODUCTS (P_PROD_NAME=>’Pen’, P_PROD_ID=>3, P_PROD_TYPE=>’Stationary’);

So, now we have changed the order, and let us run the procedure.

Named Notation for Passing Parameters in Oracle

So, the procedure is now executed successfully. We need to check the table products in order to make sure the records are inserted or not by executing the following select statement.

SELECT * FROM PRODUCTS;

Named Notation for Passing Parameters in Oracle

So, the records are inserted.

Mixed Notation for Passing Parameters in Oracle Stored Procedure:

This is very simple. We can mix both positional and named notations and use them here. Let us try to understand Mixed Notation with an example. Please call the stored procedure as follows which uses Mixed Notation for Passing the Stored Procedure Parameters.

EXECUTE ADD_PRODUCTS (4, PROD_TYPE=>’Software’, PROD_NAME=>’Windows 10′);

So, from the above procedure call statement, you can see that there are 3 parameters where one parameter is positional and the other two parameters are named parameters. From the procedure, we can see the first position is for PROD_ID, so the value 4 is for PROD_ID and the other two parameters PROD_TYPE is mentioned as ‘Software’ and PROD_NAME is Windows 10. Let us try to execute this statement.

Mixed Notation for Passing Parameters in Oracle Stored Procedure

So, the procedure is successfully executed. Let us try to check the table products by executing the following select statement.

SELECT * FROM PRODUCTS;

Mixed Notation for Passing Parameters in Oracle

So, the record is also inserted in the table PRODUCTS. These are the three methods that are mentioned as notations. Mixed is rarely used and the named and position notations are the one that is used frequently. Named notations are the most professional notations that are used in most companies.

Default Values for Parameters in Oracle Stored Procedure

Now, let us try to understand the default option in the parameters. We have two ways to describe the default value by mentioning “Default Value” or “:=”. Let us try to recreate the procedure by making a few changes and see how this works.

CREATE OR REPLACE PROCEDURE ADD_PRODUCTS
(
   P_PROD_ID NUMBER, 
   P_PROD_NAME VARCHAR2:='UNKNOWN', 
   P_PROD_TYPE VARCHAR2 DEFAULT 'UNKNOWN'
)
IS
BEGIN
  INSERT INTO PRODUCTS VALUES (P_PROD_ID, P_PROD_NAME, P_PROD_TYPE);
  DBMS_OUTPUT.PUT_LINE (P_PROD_ID||' '||P_PROD_NAME||' INSERTED ' );
  COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE ('ERROR IN INSERT '||P_PROD_ID||' '||P_PROD_NAME);
  DBMS_OUTPUT.PUT_LINE (SQLCODE);
  DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

From the above SQL statement, you can see that the procedure is named ADD_PRODUCTS, and then the parameters PROD_ID does not have the default value and the P_PROD_NAME has the default value which is mentioned as ‘Unknown’ and the P_PROD_TYPE also has the default mentioned as ‘Unknown’.

So, the parameters P_PROD_NAME and P_PROD_TYPE will be parameters with the Default option. The exception part is left as same. Now, let us try to execute the procedure.

Default Values for Parameters in Oracle Stored Procedure

So, the procedure is compiled. Let us try to call the procedure by mentioning only PROD_ID. Here we do not get any error because we have already mentioned the default parameter value, so if we give only one value then we do not get any error.

EXECUTE ADD_PRODUCTS (10);

Default Values for Parameters in Oracle Stored Procedure

So, the procedure is now successfully executed. We have given only the PROD_ID as the parameter and the Oracle takes the other parameters P_PROD_NAME and P_PROD_TYPE as the Default value and will display unknown. Let us go ahead and check the table PRODUCTS by executing the following Select Statement.

SELECT * FROM PRODUCTS;

Default Values for Parameters in Oracle Stored Procedure

You can see a new record is added but the PROD_NAME and PROD_TYPE are Unknown. So, this is how we mention the Default value in the Oracle Stored Procedure. This helps us to save time without all the values for the parameters in the table.

In the next article, I am going to discuss Exception Handing in Oracle Stored Procedure with Examples. Here, in this article, I try to explain Notations of Passing Parameters in Oracle Stored Procedures with Examples. I hope you enjoy this Notations of Passing Parameters in Oracle Stored Procedures article.

Leave a Reply

Your email address will not be published. Required fields are marked *