Oracle SQL

Oracle SQL – Oracle DBMS is a relational database management system developed by Oracle Corporation.

Oracle is the most popular of SQL implementations; it was historically the first commercial SQL implementation and the first implementation to be compatible with the SQL’93 standard.

Oracle supports a number of different platforms including Windows, Linux, Max OS X and Sun Solaris.

The SQL procedural extension developed by Oracle is called PL/SQL (Procedural Language/Structured Query Language) and is based on the syntax of the Ada and Pascal languages. The third key language used in Oracle DBMS on par with SQL and PL/SQL is Java.

PL/SQL supports program blocks (in the form of functions, procedures and packages that are stored in the database in compiled form and can be executed later, or anonymous blocks that cannot be compiled and are used only with the form of scripts). PL/SQL supports a variety of data types for storing numbers, rows and dates, computational flow control operators (including conditional transitions and loops) and three container (collection) types – variable length arrays, associative arrays and nested tables.

A number of tools can be used for database administration and application development for Oracle DBMS. Examples of software provided by Oracle Corporation are:

• SQL*Plus is a command-line interface tool designed to execute SQL and PL/SQL commands interactively or from a script; widely used as the default installation interface.
• iSQL*Plus is a tool available from a web browser to execute SQL commands.
• Oracle SQL Developer – IDE for SQL development.
• Oracle Forms – IDE for developing applications that interact with the database; widely used for creating data input systems and graphical interfaces for the database.
• Oracle Reports – IDE for creating reports based on data stored in the database.
• Oracle JDeveloper is an IDE that allows software development in SQL, PL/SQL and Java.

Examples of third-party software are:

• TOAD – Windows IDE, created by Quest Software, supporting Oracle and a number of other DBMS.
• PL/SQL Developer is an IDE created by Allround Automations.

Examples:

Hello, World!
Example for Oracle versions 10g SQL, Oracle 11g SQL
The ‘Hello, World!’ string is selected from the built-in dual table used for queries that do not require access to these tables.

select 'Hello, World!'
from dual;

Factorial:
Example for Oracle versions 10g SQL, Oracle 11g SQL
SQL does not support cycles, recursions or custom functions. This example illustrates a possible workaround using a bypass:

• pseudo table level to create pseudo tables t1 and t2 containing numbers from 1 to 16,
• the aggregate function sum, which allows you to sum the elements of a set without explicitly using the cycle,
• and mathematical functions ln and exp, allowing to replace the work (necessary for calculating the factorial) with the sum (provided by SQL).

Line “0! = 1” will not be included in the result set of strings, because an attempt to calculate ln(0) leads to an exception.

select t2.n || '! = ' || round(exp(sum(ln(t1.n)))).
from
( select level n
from dual
connect by level <= 16) t1,
( select level n
from dual
connect by level <= 16) t2
where t1.n<=t2.n
group by t2.n
order by t2.n

Fibonacci numbers:
Example for Oracle versions 10g SQL, Oracle 11g SQL

SQL does not support cycles or recursions, and concatenation of fields from different rows in a table or query is not a standard aggregate function. This example uses:

• Bine formula and mathematical functions ROUND, POWER and SQRT to calculate the n-th Fibonacci number;
• pseudo table level to create a pseudo table t1 containing numbers from 1 to 16;
• built-in SYS_CONNECT_BY_PATH function for ordered concatenation of received numbers.

SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(fib||', ', '/')), '/', '||'...' fiblist.
FROM (
SELECT n, fib, ROW_NUMBER()
OVER (ORDER BY n) r
FROM (select n, round((power((1+sqrt(5))*0.5, n)-power((1-sqrt(5))*0.5, n))/sqrt(5)) fib
from (select level n
from dual
connect by level <= 16) t1) t2
)
START WITH r=1
CONNECT BY PRIOR r = r-1;

What do you need SQL Developer for?

Oracle SQL Developer is a free graphical database management and application development environment in the SQL and PL/SQL programming languages, developed specifically for Oracle Database.

This environment is written in the Java programming language and it works on all platforms that have Java SE.

SQL Developer, allows you to view database objects, run various SQL instructions, create and edit database objects, import and export data, as well as create all kinds of reports.

Oracle SQL Developer in addition to Oracle Database can connect to other databases, such as Microsoft SQL Server, MySQL and others, but this requires special plug-ins, although the ability to connect to a database Access (mdb file) is by default.

At the time of writing, Oracle SQL Developer 19.2.1 is available, so it is this version that we will install.

Download Oracle SQL Developer

Since this is an Oracle product, it can be downloaded from the official website of the company, a page is currently available – www.oracle.com/tools/downloads/sqldev-v192-downloads.html

After we get to the page, we agree to the license agreement by selecting the Accept License Agreement switch, then select the platform that we are going to install on, I want to install on Windows 7 x32, so choose:

Windows 32/64-bit – Installation Notes Download 226 M

Click Download, then, just like when downloading Oracle Database Express Edition, you need to specify Oracle credentials if you have them, and if not, you need to create an Oracle account accordingly (click “Create account“). After that, the file sqldeveloper.zip with the size of almost 226 megabytes will be downloaded (this archive can be unzipped by the program 7-zip for example).

Oracle SQL Developer Installation

As mentioned, SQL Developer requires Java SE to run the environment, so you must have a Java Development Kit (JDK) on your computer, which is developed by Oracle, a free Java developer kit that includes a standard compiler, Java class libraries and a JRE runtime.

If you don’t have the JDK package installed and you chose the same file as me Windows 32/64-bit – Installation Notes, we still need to install the JDK, because this package is not included in this build, if we chose, for example, Windows 64-bit platform – zip file includes the JDK 14, then as you can see from the name, the JDK package is included in the SQL Developer distribution itself. (And if you already have JDK installed, you can go straight to “Run SQL Developer“).

So we first need to download and install the JDK, you can also download it from the official website. For example, I will download and install version 14 of JDK. The 14th version of the JDK is currently available on the following page:

www.oracle.com/java/technologies/javase-downloads.html

Launch of SQL Developer

After unpacking the archive sqldeveloper.zip and installing JDK, go to the unpacked directory, open the folder sqldeveloper and run the program sqldeveloper.exe.

And at the first launch SQL Developer will ask you to specify the path to the JDK set, and if you have not changed the path when installing the JDK, the program will set it itself, we will press “OK“, and if you have changed the path, you will need to specify it.

Launch of SQL Developer

And now we will be able to watch as we open the program SQL Developer.

we open the program SQL Developer

After which it will open accordingly, and we will see the home page

home page

Configure the connection to the server and database

Since we have already installed Oracle Database Express Edition in this material, we will connect to this server accordingly.

To do this, click on the plus “New Connection”

New Connection

After that you will see the Connection Name window, you will enter your Connection Name, Username and Password accordingly. If Oracle Database is installed on the same computer, we leave Localhost, port 1521, SID in the Hostname field, i.e. the name of the database, in case of Express Edition it is XE. (If you remember I said that the sql developer can be configured to work with the Access mdb database, go to the Access tab to do so). After entering it, I advise you to press Test first and if you get the answer in the status line “Success“, i.e. Status: Success.

Status: Success

That means it’s okay, we can press “Connect”.

In case you received the following error in response:

Status : Failure -Test failed: ORA-00604: error occurred at recursive SQL level 1 ORA-12705:
Cannot access NLS data files or invalid environment specified

What this means is that due to the language settings of the Windows operating system, you can’t connect, but it can be fixed if in the configuration file, it is located on the following path

sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

add two lines at the end

AddVMOption -Duser.language=en
AddVMOption -Duser.region=us

then restart SQL Developer

After connecting to the database, you will see the name of your connection in the list of connections

name of your connection in the list of connections

If you open the connection, we will see all types of objects in the database.

all types of objects in the database

Now you can write queries, develop functions and procedures in PL/SQL. Good luck!

Oracle SQL Tutorial – Downloading Oracle Database and Installing SQL Developer