PL-SQL
Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL
What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running...
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 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.
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.
In this section, we are going to write a simple program for printing "Hello World" using "Anonymous block".
BEGIN dbms_output.put_line (‘Hello World..'); END; /Output:
Hello World...
Code Explanation:
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.
Here we are going to print the "Hello World" using the variables.
DECLARE text VARCHAR2(25); BEGIN text:= ‘Hello World’; dbms_output.put_line (text); END: /Output:
Hello World
Code Explanation:
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.
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
BEGIN --single line comment dbms output.put line (' Hello World ’); /*Multi line commenting begins Multi line commenting ends */ END; /Output:
Hello World
Code Explanation:
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.
What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running...
In this tutorial, we are going to learn how to use SQL in PL/SQL. SQL is the actual component that...
$20.20 $9.99 for today 4.5 (108 ratings) Key Highlights of PL/SQL Tutorial PDF 188+ pages eBook...
Download PDF 1) What is PL SQL ? PL SQL is a procedural language which has interactive SQL, as well as...
What is While Loop? WHILE loop statement works similar to the Basic loop statement except the EXIT...
What are TCL Statements in PL/SQL? TCL stands for Transaction Control Statements. It will either save...