PL/SQL First Program: Hello World Example

Details

In this tutorial, we will introduce SQL* Plus and learn how to connect it to the database.

After connection, we are also going to see how to write our first program "Hello World" in PL/SQL.

In this tutorial - you will learn.

What is SQL* Plus?

SQL* Plus is an interactive and batch query tool that is installed with every Oracle installation. It can be found at Start > Programs > Oracle-OraHomeName > Application Development > SQL Plus. Alternatively, you can also download it from the Oracle Technology Network (OTN)

It has a command line user interface, Windows GUI, and web-based user interface.

It allows the user to connect to the database and execute PL/SQL commands.

Connecting to Database

In this section, we are going to learn how to connect to SQL* Plus in Windows GUI. When we open SQL* Plus, it will prompt for the connection details as shown below.

Connection Details:

  • Username: <user name of the database>
  • Password: <password for that user>
  • Host String: <host details along with the port number and SID of the database>

PL SQL First Program: Hello World

  • After the successful connection, the SQL plus will appear as shown below

PL SQL First Program: Hello World

  • We need to execute "set serveroutput on" if we need to see the output of the code.
  • Now we are ready to work with the SQL* Plus tool.

How to write a simple program using PL/SQL

In this section, we are going to write a simple program for printing "Hello World" using "Anonymous block".

PL SQL First Program: Hello World 

BEGIN
dbms_output.put_line (‘Hello World..');
END;
/
Output: 
Hello World...

Code Explanation:

  • Code line 2: Prints the message "Hello World. . ."
  • The below screenshot explains how to enter the code in SQL* Plus.

Note: A block should be always followed by '/' which sends the information to the compiler about the end of the block. Till the compiler encounters '/', it will not consider the block is completed, and it will not execute it.

PL SQL First Program: Hello World

Declaring and usage of variables in the program

Here we are going to print the "Hello World" using the variables.

PL SQL First Program: Hello World 

DECLARE
text VARCHAR2(25);
BEGIN
text:= ‘Hello World’;
dbms_output.put_line (text);
END:
/
Output: 
Hello World

Code Explanation:

  • Code line 2: Declaring a variable "text" of a VARCHAR2 type with size 25
  • Code line 4: Assigning the value "Hello World" to the variable "text".
  • Code line 5: Printing the value of the variable "text".

Comments in PL/SQL

Commenting code simply instructs the compiler to ignore that particular code from executing.

Comment can be used in the program to increase the readability of the program. In PL/SQL codes can be commented in two ways.

  • Using '--' in the beginning of the line to comment that particular line.
  • Using '/*…….*/' we can use multiple lines. The symbol '/*' marks the starting of the comment and the symbol '*/' marks the end of the comment. The code between these two symbols will be treated as comments by the compiler.

Example: In this example, we are going to print 'Hello World' and we are also going to see how the commented lines behave in the code

PL SQL First Program: Hello World 

BEGIN
--single line comment
dbms output.put line (' Hello World ’);
/*Multi line commenting begins
Multi line commenting ends */
END;
/
Output: 
Hello World

Code Explanation:

  • Code line 2: Single line comment and compiler ignored this line from execution.
  • Code line 3: Printing the value "Hello World."
  • Code line 4: Multiline commenting starts with '/*'
  • Code line 5: Multiline commenting ends with '*/'

Summary

In this tutorial, you have learned about SQL* Plus and Connection establishment to SQL* Plus. You have also learned about how to write the simple program and how to use a variable in them. In our upcoming chapters, we will learn more about different functionalities that can be implemented in the PL SQL program.

 

YOU MIGHT LIKE: