What is Cursors in Oracle?

What is the Cursors?

A cursor is a pointer to a private SQL memory area allocated (in PGA memory) for processing an SQL statement. The cursor is used to process one by one the records (rows of tables) returned by the SQL statement in question.

Two types of cursors can be distinguished:

    Implicit Cursor: When a user issues an SQL command, Oracle generates a cursor for processing that command. This cursor created and managed by Oracle is called an implicit cursor.
    Explicit cursor: if you want to manage an SQL command within your PL / SQL code, you can explicitly create a cursor to process the rows returned by the command one by one.

Here is an example :

DECLARED
name emp.ename% TYPE;
salary emp.sal% TYPE;

CURSOR C1 IS SELECT ename, NVL (sal, 0) FROM emp;

BEGIN
OPEN C1;

LOOP
FETCH C1 INTO name, salary;
EXIT WHEN C1% NOTFOUND;
DBMS_OUTPUT.PUT_LINE (name || 'earns' || salary || 'dollars');
END LOOP;

CLOSE C1;
END;

Explanations:

CURSOR C1: used to declare a cursor with the name C1
name emp.ename% TYPE means that the variable name is of the same type as the ename column of the emp table (Another predefined type% ROWTYPE can be used to declare a record variable of the same type as the record of a table)
Open C1: open the cursor
Fetch: fetch the current record and load it into the name and salary variables and then advance the pointer to the next record
C1% NOTFOUND: returns TRUE if the cursor points beyond the last record. Other attributes of the cursor can be used:

    % FOUND,
    % ROWCOUNT which returns the number of rows returned by the SQL order of the cursor,
    % ISOPEN which returns true if the cursor is open.

Close C1: used to close the cursor and free the resources.

For advance topics of Oracle DBA tutorials and tips. Stay connected with our following blogs. Dba tips and Dbametrix.

Transaction in Oracle Database

The transactions

Managing transactions is at the heart of database processing. To allow multiple users to access it simultaneously, the DBMS must manage transactions with the minimum of conflicts while ensuring the consistency of the database.

A transaction is a logical unit made up of one or more SQL statements.

DML command 1
DML command 2
................................
................................
................................
DML command n

A transaction begins implicitly with the execution of the first SQL command and ends with a COMMIT to commit or a ROLLBACK to roll back the transaction.

The transaction continues executing SQL commands one at a time until one of the following occurs:

    COMMIT: Transaction encounters an explicit COMMIT statement, all changes made to the database up to this point are committed.

    ROLLBACK: Transaction encounters an explicit ROLLBACK statement, all changes made to the database up to this point are rolled back.

  DML instruction: If an DDL instruction is encountered such as CREATE, DROP, RENAME, or ALTER, Oracle first commits the current DML instructions for the transaction and then executes and commits the DDL instruction in question. This is an implied commit.

    Normal end of the calling program: if the program ends without errors, the transaction is committed implicitly. This is the case when you use the sqlplus tool, then you enter a series of DML commands in interactive mode, then you exit the session normally via the sqlplus "EXIT" command. In this case an implicit commit is applied to your transaction.

    Abnormal program termination: Such as a network cut or a program kill, the transaction is implicitly rolled back.


Learning Oracle DBA is not too short and quick guide. You should need to read several books and Oracle documentation for understanding of depth of each topics. You can get more tips and tutorials for Oracle database administration from our other resources like this or this.

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.