PL /SQL Installation

In this topic, we will understand the environment setup and installation of PL/SQL. To work with PL / SQL, there should be a Oracle databae installed on the system, and there are three methods to have a database. We can choose any one of them as per our system requirements.

? 1) Database with the Virtual Box option.

? 2) Database by installing into your computer

? 3) Using Oracle Live SQL on the internet.

Database with Virtual box

This option is for those who want to have a database and a SQL developer window without installing the actual database. Because it may difficult to install and work with that installed databse.

To work with the virtual box, we have to download two basic files:

Oracle virtual box software
Virtual image

First, we have to install the Virtual box and then import the virtual image into the virtual box software. By doing so, we will have a working database and an already configured SQL developer software.

Sometimes it becomes mandatory to use the virtual box as the Oracle database is not supported on macOS, and also if you have low internet connectivity.

To work with virtual box here are some requirements:

Required free disk space	17 GB
Operating system	Windows, Linux, Mac OS
Database configuration	N / A
SQL developer configuration	N / A
Computer performance	High
Included database version	11 g , 12 c (optional)
Required downloads	Oracle virtual box, virtual image
Needs internet access	Only when downloading

Installing And Setting Up Environment for Virtual Box on Windows 10

To install a Virtual box in our system, we must have a free space of 17 GB and 2 GB RAM so that all the virtual box functions can work properly. To install it, follow the below steps:

STEP – 1 ) To install the virtual box, we have to download the same from our web browser. You can easily find Virtual box VM on oracle's website that's too without any cost as it is open-source software and very easy to download.

STEP – 2 ) download your desired virtual box VM as per your operating system and your system requirement. Once downloaded, you will find an installation wizard that will easily install your virtual machine and then press next.

STEP – 3 ) Now select your preferred file with minimum allocation memory of 17 GB for easy installation. Now select the features you want to install in your system, and press next.

STEP – 4 ) You will find a window with a warning sign that will ask to modify your networking options for your database. Click on the Yes tab, and your installation will begin.

STEP – 5 ) Once the installation begins, you have to wait for the installation to complete. If you find any pop-up window asking administrative approval, click yes, and your installation will continue. Once the installation gets completed, you will find a finish button, press the finish option, and install the virtual box VM completely.

STEP – 6 ) Once your installation is complete, select the Virtual box option, and your virtual box VM manager will open.

STEP – 7 ) As we have finally installed the virtual box, the manager will be proceeding to the second step; downloading the virtual disc image. You can find your virtual image through your browser. You have to search "download oracle pre-built virtual machine" and select the oracle official website's link.

STEP – 8 ) Once you reach the oracle's official site, you have to find Database App Development VM, and click on the download option from all the provided databases. You will reach the requirements sections from where you can download the file, but before downloading, you have to accept the user agreement license by signing up the terms and conditions, and accepting the terms, your downloading will take place.

STEP – 9 ) once a virtual image is downloaded, open your Virtual box and through the file selection, click import appliances.

STEP – 10 ) Select the virtual image file to be imported and press next.

STEP – 11 ) Configure the appliance settings according to your system recommendation, or you can leave them to default and press import.

STEP – 12 ) After a complete importing, click on the start button, and your virtual image will open. Once loading completes, you will have your operating system with a pre-installed oracle database.

STEP – 13 ) Now, we have a newly set up Oracle operating system on our virtual machine and a pre-configured SQL database where we can run our codes.

Database by installing into your Computer

In this option, we can install the database directly to our system, and later we can create a database connection of SQL developer.

To work with installed database here are some requirements:

Required free disk space	10 GB
Operating system	Windows, Linux
Database configuration	REQUIRED
SQL developer configuration	REQUIRED
Computer performance	Normal
Included database version	12 c
Required downloads	Oracle database
Needs internet access	Only when downloading

Database by installing into your computer and setting up the environment

Before installing lets first understand different types of databases:

Express database edition – simplest database version, easy to download, and you can easily work with MYSQL and PL / SQL. But can not support tuning queries as it is a lightweight database and contains many restrictions. It will work slowly as compared to others, but works great if working as a test subject
Standard edition database – more capable and comprehensive compared to Express version, and you can confidently work with MYSQL and PL / SQL, including tuning the queries
Enterprise edition database – most capable database edition and is also the full feature database, and these are usually found in companies.

In this option, we will be installing the enterprise edition database directly to our system, and later we will be creating a database connection of SQL developer.

STEP – 1 ) To install the Oracle database, we have to download the same from our web browser. You can easily find the Oracle database on oracle's website without any cost as it is open-source software and very easy to download.

STEP – 2 ) Once you reach the oracle's official site, from all the provided databases, you have to find a Database according to your Operating system and click on the download option. You will reach the requirements sections from where you can download the file, but before downloading, you have to accept the user agreement license by signing up the terms and conditions and after accepting the terms, your downloading will take place.

STEP – 3 ) after downloading it, you will have an archived database file. The first step before installation, we will have to extract the file on our system and save them in preferred drive. Now open the extracted file and select setup, and Oracle windows installer will open, now you have to set up the configurations accordingly.

STEP – 4 ) Now, we have to set up configuration options; therefore, we will select a single instance database as we have to use the database for our personal use, and this option will provide us full flexibility.

STEP – 5 ) Now, we have to define the system class as we are using it on our system and for our personal use; therefore, we will select desktop-class.

STEP – 6 ) Now we have to define the Oracle home user as we are working on windows so that we will use windows built-in account.

STEP – 6 ) lastly, we have to define all the typical installation paths that we can leave to default. We must set an easy to remember the password so that it will be easy for us to remember in the future use of oracle database.

STEP – 7 ) Finally, after re-evaluating all the specifications, select install and database installation will take place.

STEP – 8 ) While installing, wizard will check all the communication channels, configurations, and nodes and then set up your database once your installation is complete.

STEP – 9 ) Now, we will be connecting the HR schema, all you have to do is to copy these transcripts and paste them in the Command prompt of your operating system, and this will implant the HR schema into your installed database.

sqlplus / as sysdba;

alter session set container=orclpdb;

alter pluggable database open;

alter pluggable database orclpdb save state;

alter user hr identified by hr account unlock;

The first command-line sqlplus helps you to connect the database with the sqlplus tool of the oracle database. And further, all the commands will let you create a pluggable database. The last line will let you unlock the hr schema that we will be using in our further course.

STEP – 10 ) As we have downloaded the database and unlocked all the hr schema, we have to install and set up a terminal software for this course. Here we will be using Oracle SQL developer to manage our database and to run PL SQL codes. First, search Oracle SQL developer in your browser and go to the oracle main website www.oracle.com.

STEP – 11 ) Once you land on the main page, go for SQL developer.

STEP – 12 ) Now, find the suitable SQL developer software according to your Operating system. You can choose any version from the provided list.

STEP – 13 ) To download the file, you have to complete the sign-in, and your file will be downloaded in ZIP format. After downloading, all you have to do is to extract the file on your desktop.

STEP – 14 ) once the files are extracted, open the folder and search for SQL developer application and run the same.

STEP – 15 ) After completing all the mentioned steps, now we have to establish a new connection with the database using the name HR. for that, we have to go to the connection menu, right-click the green plus sign, and select the new connection tab.

STEP – 16 ) Now, we have to establish a new connection with the name HR. As we are working with the hr schema, we will be using the database name hr to define the oracle's database type. After that we have to add the user name HR and set the password of our choice. In this case, we are using hr in my password. We have to set the network type to TNS and network allays to the third option. This option is with the same name that we have used while installing the database.

Press test and check for the status if it shows success, we have established a connection with the database and finally press connect.

STEP – 17 ) Once you press connect, you can see the HR connection we have established on the top left corner. Now press right-click on the hr tab and press connect. Your new worksheet will open.

STEP – 18 ) We have to write a SQL query in the worksheet as we have done in the example and execute the same. We have 3 options. Option 1 – you can press the green arrow just above your query to print the output. Option 2 – you can get the output by pressing control + enter key together. Option 3 – we can press the F9 button to see the output.

By all three methods, you can see that we got an employee table in our output.

STEP – 19 ) here, we have defined an employee table and select the employee with employee id 102.

# NOTE: - Instead of using Oracle SQL developer, we can also code the queries in our notepad, notepad+, or any other text editor in our operating system. We can run the command in our SQLPLUS window in the command prompt.

All we have to do is to write our PL / SQL codes in any of text editors and then need to save the file with a .sql extension in home directory (where you have installed the oracle database).
Open the command prompt, get to the same location in your directory and launch SQLPLUS from there.
Now all you have to do is using @ symbol, write the file name that you saved, and press enter in your command prompt to display the result
If you don't intend to create or save any file, check the result.
You have to copy the PL / SQL codes, open the command prompt, open the SQL PLUS window and right-click on the black window from the SQL prompt, and press enters to execute the program.

But this method is not recommended by most developers as this method does not give the programmer the complete ability to resolve errors on the spot or find errors. Therefore, using one of the other options is way better than this procedure.

Using Oracle Live SQL on the internet

In case you face any problem in installing the above two software's or any other requirement problem, then you can easily transit to the third working option, that's the live server of SQL. It needs absolutely no installation and requirements are very low.

To work with live sql on internet here are some requirements:

Required free disk space	N / A
Operating system	ALL
Database configuration	N / A
SQL developer configuration	N / A
Computer performance	Low
Included database version	12 c, 18 c or above
Required downloads	N / A
Needs internet access	required

Follow the below steps to use Oracle live SQL on the internet:

STEP – 1 ) To work on a live SQL Oracle database, we first don't have to download anything, but we have to search live SQL from our web browser. You can easily find Oracle live SQL database on oracle's website, that's too without any cost as it is an open-source software by searching "Oracle live server".

STEP – 2 ) Once you reached the official site of the oracle. You will reach the requirement sections from where you can work on the live SQL database, but before proceeding, you have to accept the user agreement licence by signing up the terms and conditions and after accepting the terms your downloading will take place.

STEP – 3 ) Once verification completes, live SQL will open now you can easily code here without any installation and downloading any form of data.

STEP – 4 ) While working on SQL live server, we don't have HR schema defined or implant in our database; therefore, before writing normal codes, we have to define HR schema for working with HR schema.

STEP – 5 ) Another plus point of using the oracle live server is that it provides us with numerous pre-designed Code libraries to understand and start our coding easily.

STEP – 6 ) We can also save our codes and submit them as scripts and define the visibility types, and add a description to better understand the SQL program by pressing the save option.

Apart from advantages of using sql live server, there are also many disadvantages and restrictions to use the same

You have to know that there are some limitations in Oracle Live SQL, so you cannot do everything in the course using Live SQL. You can do more than 90% of the course using Live SQL.

For example:

· Substitution variable is not allowed

When you try to do this, it will give you an error:

select * from hr. employees
where employee _ id=&no;

Creating users is not allowed
Create tablespace is not allowed.

PL/SQL Installation

Database with Virtual box

Installing And Setting Up Environment for Virtual Box on Windows 10

Database by installing into your Computer

Using Oracle Live SQL on the internet