Explicit Cursors
This is one of 5 important topics.

A cursor can be declared as a variable containing more than one row. The row selected with the cursor should be declared to variable.

A row taken from a cursor needs to be assigned to a variable, here for r_employee in c_employee gets assigned to the cursor’s “employee_id, first_name, last_name” fields.

To retrieve the 3rd record, change the counter variable to 3.

How to write a cursor that can find the number of records in the HR schema’s tables ? If possible, this would be very useful.

How many tables are there in the database ?

drop table emp purge

create table emp as select * from employees;

alter table emp modify (last_name null) ; – this changes the not null constraint to allow null entries in the last_name field

update emp set last_name=null; – sets all the last_name fields in all the emp records to null.


The values in the emp’s last_name field are empty, the same field in employees contains data. Is it possible to copy the values from employee’s last_name to the emp table ?

As the employee_id is unique, it can be used as an id to match.

— If there are 10 million records to be changed, the commit should be placed in a loop. Otherwise, all the changes can be undone and this can increase the size of the database from 4Gb to 20-30Gb. If that amount of storage isn’t available, the database will stop.

A commit after every step would be also be inefficient. It is necessary to take action according to the number of record rows in the table. The cursor should be taken to the Employees table.

As we need someones data, the name table is added to the emp table:

alter table emp drop column name ;

alter table emp add (name varchar2 (100) );

This will be written in a first_name & last_name format.

The 2nd cursor gets the parameters. The first cursor retrieves members that have the specified department_id.

Redo can’t be used in situations where a truncate : rollback isn’t achievable.

select count(*) from emp ;

This can’t work directly (DDL,create drop,alter truncate) and requires dynamic sql.

This won’t work because table and column names can’t be sent as parameters, however values can.

Example: select first_name, last_name from emp where employee_id = 45 ; <== Here only 45 can be sent as a parameter. Send parameters everywhere using execute immediate.

The previous method would achieve the same results. Finding the database’s table record numbers Requirements: Table names and record numbers

select table_name , num_rows from user_tables; The same can also be done in the following: analyze table emp compute statistics; This contains the table’s statistics. This is normally contained in the dynamic views and updated using the previous command. The DBMS’ accuracy in making decisions depends on how recently the information has been updated. For the DBMS to have the latest record count it needs to calculate the statistics. This can be done either by the DBMS or the user. This is done automatically every hour in 10g, 9i didn’t do this at all. An example of the effect of having a discrepancy in not calculating the latest statistics vs. having them is: When the DBMS still acts based on information of a record count of 100 records and performs a full scan, whereas the latest number of records is actually 1 million; if the DBMS knows that the latest record count is 1 million, it would use the index which would take 30 seconds instead of 10 minutes. There are 2 ways to analyze all the tables: 1- analyze table emp compute statistics; analyze table employees compute statistics; analyze table dept compute statistics; … Using this method a script file, that has all the tables saved, is run select ‘analyze table ‘ || table_name|| ‘ compute statistics;’ from user_tables; When run, the previous line carries out a test and saves the result as a delimited txt. To determine whether the statistics are current (have been updated), use the following line select table_name , num_rows from user_tables; To test, use select count(*) The second way to analyse all the tables: Using execute immediate (with dynamic sql), automatically collect the statistics of connected schemas.

One cursor retrieve num_rows, and another cursor retrieves count(*) while running a side by side comparison using pl/sql. WHILE select trunc(dbms_random.value(1,50)) from dual;

he RPAD (Right Padding) function can structure the output into a more tabular layout.

A space ‘ ‘ or symbols such as * can be inserted after the 25th character.

Use the following codeblock to output (toad can cause the output to appear disarranged)

Using ‘for’ makes this task a lot easier. The naming convention for cursors is to start them with a c_.

If it isnt possible to count the amount of records entered with ‘insert into’ using:


During the insert update process, the user who first updates is the one who locks the row to maintain read consistency. This lock remains in effect until a commit and also prevents a rollback.

Once the 1st cursor has been committed the 2nd cursor automatically gets updated.

A Deadlock can be considered a double lock, i.e. different to a lock.


If while in one session User A tries to update a record that User B has locked and if, at the same time, in another session User B tries to update a record that User A has locked, the result would be a deadlock.

The DBMS outputs a deadlock error with the first session and waits for the first session to commit/rollback.

This way only the salary column gets locked. Use this when you dont want to update before running a cursor.

t’s better to use a ‘for’ loop when including Cursor Parameters. This prevents endless loops and open cursors. Open cursors can negatively impact the DBMS’ performance, has the same impact as the cursor being run in the system.

RAM usage is proportional to the amount of cursors that remain open.

This section shows that it isnt necessary to declare the cursor.

If an error occurs after running a proc and the exception catches the error, other functions and procedure won’t be affected.