Cursor for update

Cursor for update

What is Cursor for update?

In most of the cases, the row fetched by the cursor query is updated inside the cursor loop. When an update occurs, the updated row is locked by Oracle. When the ‘FOR UPDATE’ clause is given in the cursor, oracle locks the rows that satisfy the where condition during opening of the cursor. Instead of the where clause in the update statement, the ‘CURRENT OF’ clause can be used. The following example illustrates that.

See the example below:

CREATE OR REPLACE PROCEDURE UPDATE_OPEN_AMOUNT IS
CURSOR cur_loanGrant IS
SELECT * FROM T_LOANGRANT WHERE status = 'O' FOR UPDATE; -- Declaring the cursor and associating the query that returns more than one row.
rec_LoanGrant t_loangrant%rowtype;
v_total_repay t_loangrant.disb_amount%type;
v_open_amt t_loangrant.open_amt%Type

BEGIN
OPEN cur_loanGrant; -- Open the cursor
Loop
Fetch cur_loanGrant INTO rec_loangrant; -- Get the cursor in the record variable.
EXIT WHEN cur_loanGrant%NOTFOUND; -- Exit the loop when no more rows are found.

-- Get the sum of repaid amount for each loan.
SELECT SUM(REPAY_AMOUNT) INTO v_total_repay WHERE loan_nbr = rec_loanGrant.Loan_nbr;

-- Calculate Open amount:
v_open_amt := rec_loangrant.disb_amount - v_total_repay;

-- update the t_loangrant.open_Amt
UPDATE T_LOANGRANT SET OPEN_AMT = v_open_amt WHERE CURRENT OF cur_loanGrant;

-- If The loan is fully closed then set the Status to 'C'
IF v_open_amt = 0 THEN
UPDATE T_LOANGRANT SET STATUS = 'C' WHERE CURRENT OF cur_loanGrant;
END IF;

END LOOP;

IF cur_loanGrant%ISOPEN THEN
CLOSE cur_loanGrant;
END IF; -- Close the cursor.

END UPDATE_OPEN_AMOUNT;
/

Cursor for loop

Cursor for loop

What is Cursor for loop?

In most of the explicit cursors, the programmer tends to open a cursor, fetch it and loop through it and finally close the cursor. A cursor FOR loop could be used instead of these operations. A cursor FOR Loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed.

An example script is given below:

CREATE OR REPLACE PROCEDURE UPDATE_OPEN_AMOUNT IS
/* This stored procedure uses cursor for loop to update the OPEN_AMT in the T_LOANGRANT table. */
CURSOR cur_loanGrant IS
SELECT * FROM T_LOANGRANT WHERE status = 'O'; -- Declaring the cursor and associating the query that returns more than one
row

rec_LoanGrant t_loangrant%rowtype;
V_total_repay t_loangrant.disb_amount%type;
v_open_amt t_loangrant.open_amt%Type

BEGIN

FOR rec_LoanGrant IN cur_loanGrant LOOP
/* Need not declare rec_LoanGrant explicitly as it would be done automatically.
No Open and Fetch statements. Just the loop will do the work.
*/

-- Get the sum of repaid amount for each loan.
SELECT SUM(REPAY_AMOUNT) INTO V_total_repay WHERE loan_nbr = rec_loanGrant.Loan_nbr;

-- Calculate Open amount:
v_open_amt := rec_loangrant.disb_amount - v_total_repay;

-- update the t_loangrant.open_Amt
UPDATE T_LOANGRANT SET OPEN_AMT = v_open_amt WHERE LOAN_NBR = rec_loanGrant.loan_nbr;

-- If The loan is fully closed then set the Status to 'C'
IF v_open_amt = 0 THEN
UPDATE T_LOANGRANT SET STATUS = 'C' WHERE LOAN_NBR = rec_loanGrant.loan_nbr;
END IF;

END LOOP;

IF cur_loanGrant%ISOPEN THEN
CLOSE cur_loanGrant;
END IF; -- Close the cursor.

END UPDATE_OPEN_AMOUNT;
/

REF CURSORS

REF CURSORS

Since Oracle 7.3 REF CURSORS have been available which allow recordsets to be returned from stored procedures, functions and packages. Lets say that we have the bank transaction tables like this, transaction_2005, transaction_2006 that stores the transactions of the respective years. (Both the tables have identical columns). Now the input to the procedure would be the year, and based on the year, we have to process the records of that table. In this case, we might want to use a cursor and the catch is that the table name to be used in the select statement of the cursor is not known until runtime. In this case, the way the cursor is declared, opened and fetched are also different as shown in the example below.

CREATE OR REPLACE PROCEDURE Process_transaction (p_year number) AS

Type RefC is REF CURSOR;

Cur_Transaction RefC;
TransactionStmt Varchar2(1000);
Transaction_fetch varchar2(1000);
Transaction_record Transaction_2006%RowType;
Year_no char(4);
l_return number;

BEGIN
Year_no := to_char(p_year) ;
TransactionStmt := 'SELECT * FROM transaction_'|| Year_no ;

Cur_Transaction := (RefC) dbms_sql.open_cursor; -- Opening the cursor.
dbms_sql.parse(Cur_Transaction, TransactionStmt, 1); -- Parsing the cursor for the select statement
l_return := dbms_sql.execute_and_fetch(Cur_Transaction);
Loop
Fetch Cur_Transaction into Transaction_record;
Exit when Cur_Transaction %NOTFOUND;
---

--- Do the processing with the data in the cursor.

---

end loop;
END:

FLASHBACK in Oracle 10g database (FLASHBACK TABLE)

FLASHBACK in Oracle 10g database (FLASHBACK TABLE)

This article explain FLASHBACK TABLE capabilities of the oracle 10g database.

Flashback table can be of 2 types.

1. Flashback or recover a dropped table with FLASHBACK DROP
2. Flashback a table to a time in the past.

Note 1: Only FLASHBACK DATABASE requires flashback to be 'ON' since only FLASHBACK DATABASE uses flashback logs in the flash_recovery_area.

All other forms of FLASHBACK use the recycle bin and undo_tablespace and time upto which flashback is possible depends on the

initializtion parameter db_flashback_retention_target.

Db_flashback_retention_target=1440 (24 hours).

Important info: You cannot 'flashback table to before drop' a table which has been created in the SYSTEM tablespace. The table is sent to the recyclebin only if it existed in some other tablespace other than SYSTEM tablespace and that tablespace must be locally managed.

When you drop a table, the objects are temporarily placed in a 'recycle bin' and still belong to the owner. The space used by recycle bin objects is never reclaimed unless there is space pressure. The space associated with the dropped object is not immediately reclaimable although it appears in the DBA_FREE_SPACE view.

Query the dba_recyclebin view as SYS or just recyclebin as the user for information about the recycle bin.

Flashback drop allows you to recover a dropped table.

Example.
Connect arjun/arjun

Create table tempp (col_1 number(10)) tablespace users;

Insert into tempp values (10);

1 row created.

SQL> drop table tempp;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEMPP BIN$AI2Gp/QiZsPgQAw5yUImEA==$0 TABLE 2005-09-12:10:49:16

SQL> flashback table tempp to before drop;

Flashback complete.

SQL> select * from tempp;

COL_1
----------
10

In case the table is created in the system tablespace an error is thrown if you try to flashback drop :

SQL> show user

USER is "SYS"..Default tablespace SYSTEM

SQL> create table test (col_1 number(10));

Table created.

SQL> drop table test;

Table dropped.

SQL> select * from dba_recyclebin;

no rows selected

SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

QUERYING DROPPED TABLES

Dropped tables can be queried from the recycle bin. No DML or DDL operations are allowed on the table.

SQL> drop table tempp;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEMPP BIN$AI9AwvFRdf7gQAw5yUIsGA==$0 TABLE 2005-09-12:13:15:22

While querying the recycle bin, make sure the system generated table name is enclosed in double quotes.

SQL> select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0;
select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> select * from 'BIN$AI9AwvFRdf7gQAw5yUIsGA==$0';
select * from 'BIN$AI9AwvFRdf7gQAw5yUIsGA==$0'
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from "BIN$AI9AwvFRdf7gQAw5yUIsGA==$0";

COL_1
----------
10

You cannot run any DML or DDL on dropped tables.

SQL> Insert into tempp values(20);
Insert into tempp values(20)
*
ERROR at line 1:
ORA-00942: table or view does not exist

FLASHBACK TABLE TO A TIME IN THE PAST.

Firstly enable row movement for the table.In this example the table name is TEST.

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;

TIME : 08:00:00

SQL> select * from test;

SALARY
----------
5000

TIME :08:00:01

SQL> update test set salary =6000;

1 row updated.

SQL> select * from test;

SALARY
----------
6000

SQL> commit;

Commit complete.

Now flashback table to time 08:00:00

SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( '2005-09-13 08:00:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> SELECT * FROM TEST;

SALARY
----------
5000

Steps for Creating EXTERNAL TABLE in 10g

Steps for Creating EXTERNAL TABLE in 10g

Step1:

Create DIRECTORY and Grant necessary privileges to user

CREATE DIRECTORY JAGAT_DIR AS \'C:\\JAGAT\';
GRANT READ ON DIRECTORY BALA_DIR TO PUBLIC;
GRANT READ ON DIRECTORY BALA_DIR TO PUBLIC;

Step2

Create a .txt file(data.txt) in \'C:\\JAGAT\' Directory

7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30

Step 3

Now create the table using below command.

CREATE TABLE ext_tab (
empno CHAR(4),
ename CHAR(20),
job CHAR(20),
deptno CHAR(2))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY JAGAT_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE \'bad_%a_%p.bad\'
LOGFILE \'log_%a_%p.log\'
FIELDS TERMINATED BY \',\'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(empno, ename, job, deptno))
LOCATION (\'DATA.txt\')
)
PARALLEL
REJECT LIMIT 0
NOMONITORING;

Database Trigger for Capturing all DDL Commands and Storing into a TABLE

Database Trigger for Capturing all DDL Commands and Storing into a TABLE

Step 1:
First create a table:

CREATE TABLE ddl_log (
OS_USER VARCHAR2(30),
user_name VARCHAR2(30),
ddl_date DATE,
ddl_type VARCHAR2(30),
object_type VARCHAR2(18),
owner VARCHAR2(30),
object_name VARCHAR2(128),
statement varchar2(4000));

Step 2:

Then Create the following trigger, it will automatically capture the statement and then will insert into above table..

CREATE OR REPLACE TRIGGER DDL_AUDITING
AFTER DDL ON DATABASE
DECLARE
sql_text ora_name_list_t;
v_stmt long;
n number;
BEGIN
n := ora_sql_txt(sql_text);
FOR i IN 1..n
LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;
INSERT INTO ddl_log
(os_user,
user_name, ddl_date, ddl_type,
object_type, owner,
object_name,STATEMENT)
VALUES
(sys_context(\'USERENV\',\'OS_USER\'),ora_login_user, SYSDATE, ora_sysevent,
ora_dict_obj_type, ora_dict_obj_owner,
ora_dict_obj_name,v_stmt);
END DDL_AUDITING;