• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

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

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 in 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

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 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 program

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

PL SQL First Program: Hello World

Code Explanation:

  • Code line 2: Declaring a variable "text" of 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"

Commenting Code

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 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

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 learnt about SQL* Plus and Connection establishment to SQL* Plus. You have also learnt 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:
PL-SQL

PL/SQL Data Types

In oracle, each value or constant is assigned with a data type. Any data type is associated with the...

PL-SQL

Packages in PL/SQL

PL/SQL package is nothing but a logical grouping of a related subprogram (procedure/function) into a...