How to create PL/SQL stored procedures without parameters in Oracle Database

By Manish Sharma

PL/SQL Stored Procedures without Parameters

While discussing the syntax in the Introduction to PL/SQL stored procedures we learnt that a stored procedure can have zero, one or many parameters. Today in this tutorial we will learn how to create a PL/SQL stored procedure with zero parameters or say without any parameters. Apart from creating a stored procedure in oracle database, we will also learn in this tutorial the multiple ways of calling a stored procedure in a PL/SQL program.

How To Create PL/SQL Stored Procedure without Parameters In Oracle Database

In the following example we will create a very simple procedure. I will try to keep the example as easy as possible so that all the PL/SQL learning enthusiasts out there can understand the process of creating a stored procedure easily.

  var_name VARCHAR2 (30):= 'Manish';
  var_web VARCHAR2 (30) := '';
  DBMS_OUTPUT.PUT_LINE('Whats Up Internet? I am '||var_name||' from '||var_web);
 END Pr_RebellionRider;

In the above example I have created a PL/SQL Stored procedure with the name pr_RebellionRider which has two variables capable of holding strings of VARCHAR2 datatype. In the execution section this PL/SQL procedure has only one DBMS OUTPUT statement which is displaying the strings stored into those variable back to the user in a formatted manner.

For the detailed explanation of the above code please watch video tutorial on my YouTube channelon the same topic.

How to Call PL/SQL Stored Procedures in Oracle Database

After successfully creating and compiling the stored procedure, next you have to call this subroutine. You can do so in multiple ways such as:

  • Call a PL/SQL stored procedure using EXECUTE statement.
  • Call a PL/SQL stored procedure using an Anonymous PL/SQL block.
  • Call a PL/SQL stored procedure using a Named PL/SQL block.

  If in case your subroutine such as stored procedure consists of server side PL/SQL statement then do make sure to set the “Server Output On” to see the result.

Call a PL/SQL stored procedure using EXECUTE statement

The best way to quickly check the output of your stored procedure or test the working of your PL/SQL procedure is to call it using EXECUTE keyword. In order to call a stored procedure using EXECUTE keyword you simply have to write the same keyword followed by the name of the procedure.

 EXECUTE PR_RebellionRider;
Or you can also write the first 4 letters of the EXECUTE keyword followed by the procedure name.

 EXEC PR_RebellionRider;
Both the statements are the same and will do the same work.

Call a PL/SQL stored procedure using an Anonymous PL/SQL block

The second way of calling a procedure is to place a procedure call statement inside the execution section of an anonymous PL/SQL block.

You simply have to write the name of your stored procedure inside the execution section of an anonymous and named PL/SQL block. The compiler will automatically interpret that as a procedure call statement. If your procedure accepts any parameters then you can supply values for parameters here. We will talk in detail about stored procedures with parameters in our next tutorial.

 Suggested Reading: PL/SQL Blocks in Oracle Database;

Try yourself

The third way of calling a stored procedure in Oracle Database is by using named PL/SQL Blocks. This is what you should try yourself.

Write a PL/SQL stored procedure displaying your favorite string and then try calling it using a named PL/SQL Block such as Database Triggers or PL/SQL Function.

You can send me your answers or if you have any query then do write to me via my email or on my social media. You can find all my contact links on the Contact Page of my website.

You can help others in learning by sharing this blog with your friends on your social media or click here to share this blog on your twitter. Thanks & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn