PL/SQL For Oracle DBA

PL / SQL language

PL / SQL is a procedural language specific to Oracle which constitutes an extension to the SQL language (non-procedural language).

SQL commands can be embedded within PL / SQL code. These commands are called Embedded SQL Commands.

PL / SQL code can be used in program units stored in the database such as procedures, functions, and packages (groups of procedures and functions). These program units are said to be stored.

If these procedures are not stored in the database but incorporated into an application (development tools such as Oracle FORMS DEVELOPER and Oracle REPORT DEVELOPER), they are called application procedures.

PL / SQL code embedded in an application program unit or stored but not named is called an anonymous block. An anonymous block which does not have a name, therefore, cannot be stored in the database.

The advantage of a stored unit of programs is that it can be called by name from your application code and Oracle stores the result of the parse analysis of the unit in the database (persistent storage on disk). Considerable performance gains are generated as the parse output is made available and persistent for future reuse of unit code.

Example of PL / SQL code:

DECLARED
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER: = 0.72;
samp_num CONSTANT NUMBER: = 132;

BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;

the_ratio: = numerator / denominator;

IF the_ratio> lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);

ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;

COMMIT;

EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;

END;

Explanations:

This PL / SQL code declares several variables and constants, then calculates a ratio value (the_ratio) for values ​​(numerator, denominator) selected from a table (result_table).

Very few times, Oracle DBA needs to address about PL/SQL because there are developers available for every company to utilize this PL/SQL for making forms and reports. Understanding PL/SQL is essential for Oracle database administrators. Stay connected with our blog for getting more advance knowledge of Oracle DBA tips and Tutorials.

SQL language for Oracle DBA

SQL language

SQL is a standard non-procedural language for relational database management systems (RDBMS)

Examples of SQL queries:

    SELECT ename FROM emp;
    SELECT last_name, department_id FROM employees;
    DELETE FROM department WHERE department_id IN (10, 30, 70);
    CREATE TABLE articles (article_id VARCHAR2 (15), designation VARCHAR2 (40));
    INSERT INTO articles VALUES (‘imp1’, laser printer ’);
    DROP TABLE articles;
    GRANT SELECT ON department TO scott;
    REVOKE DELETE ON department FROM scott;

The terms SELECT, FROM, DELETE, WHERE, IN, CREATE, TABLE, VARCHAR2, INSERT, INTO, VALUES, DROP, GRANT, TO, REVOKE, ON are words reserved for SQL

Oracle SQL statements are classified into categories:

  DML commands for Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
    DDL commands for Data Definition Language (CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE…).
        DDL commands are automatically validated: they generate an implicit commit.
    Control Commands of:
        Transactions (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION)
        Sessions (ALTER SESSION)
        and System (ALTER SYSTEM)

SQL Recursive commands:

When an DDL command is issued, Oracle automatically issues an LMD command to modify the corresponding information in the data dictionary. This last command is called a recursive SQL command.

An DDL "CREATE TABLE" command, for example, issued by a user to create a table in their own schema, automatically generates another recursive LMD command (INSERT) in the SYS schema: data dictionary tables. In fact, the information on the structure of the newly created table must be inserted and available in the data dictionary.


Stay connected with ORAGEEK for advance Oracle DBA Tips with Dbametrix.

What is Oracle Data Dictionary?

Oracle data dictionary

In a database, all of the objects that belong to a user form what is called a user schema.

The main types of objects are tables, indexes, views, synonyms, sequences, program units stored in the database (procedures, functions, packages, triggers).

Only tables and indexes correspond to segments of data. Other objects only have a definition stored in the data dictionary.

A user schema is created and managed by it, while the data dictionary is created and managed by Oracle.

The data dictionary is a repository which belongs to the SYS super user and which stores the tables and views necessary for the proper functioning of the Oracle database. The DBA_TABLES view and the corresponding synonym USER_TABLES is an example of a table and dictionary view.

For example, if a user USER1 wants to display the structure of a TABLE1 table that he owns, he issues the "DESC TABLE1" command. The result of this command is obtained from the data dictionary.

This is because the TABLE1 table stores data and is owned by user USER1, while the structure of TABLE1 is stored elsewhere, in dictionary tables that are owned by user SYS. This information about the data forming the structure of TABLE1 is called metadata.

Dictionary tables are not directly accessible but dictionary views allow you to query them. In a session of the user USER1, USER_TABLES (synonymous with the DBA_TABLES view) is used to list the structure of all the tables of USER1.

In summary, the data dictionary stores metadata on tables, columns of tables, constraints, indexes, etc.

It can also store:

    user objects such as views, synonyms, sequences, procedures, functions, packages, triggers, etc.
    the names of the users, their privileges and roles… etc.

You can get advance level oracle DBA Tutorials from this source and read some articles from here