Oracle Fine-Grained Auditing (Oracle FGA)

Today my first time using Oracle Fine-Grained Auditing. Why do I need to use Oracle Fine-Grained Auditing? This is because there is a table that is accessed by many procedures, triggers and packages. So that in case of deletion, I can not track from which the “DELETE” order came from. I’ve tried using a trigger [...]

Cancel Oracle Trigger to Execute The Commands

Hi, I want to cancel the trigger of a table that does not work if the logic condition is not met. So as if I want the trigger to rollback (for example = cancel delete in before-delete trigger). At first, the trigger like this : CREATE OR REPLACE TRIGGER MIS.TRIG01_TABLE_A BEFORE DELETE ON PANDAZEN.TABLE_A REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO PANDAZEN.TABLE_B SELECT [...]

Bizarreness / Oddity of START WITH … CONNECT BY in Oracle SQL

Usually, the “start with .. connect by” clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts). For example, see this data hierarchical : 000 001 100 110 111 112 002 200 250 004 999 then the sql scripts like this : create table tmp_test_connectby [...]

How to exit from a procedure in Oracle

To exit from function is easy, just put “RETURN” CREATE OR REPLACE FUNCTION MIS.FC_GET_ACCRUAL_COM(VAGR IN VARCHAR2) RETURN NUMBER IS VNUM NUMBER; BEGIN SELECT AGR INTO VNUM FROM TAGR WHERE AGR = ‘AA’; IF VNUM = 0 THEN RETURN 1; ELSE RETURN 2; END IF; RETURN VNUM; EXCEPTION WHEN OTHERS THEN RETURN 0; END; / To [...]

Delete duplicate rows or one of the double records

Many a time we face cases where we have to delete one of double record. So what can we do? Here an example of it, SQL> select * from customer; LAST_NAME FIRST_NAME NATION —————————————– XHI WILLIAM INDONESIA BRUNI CARLA FRANCE THERON KEIRA ENGLAND PITT DAVID USA PITT DAVID USA To get the double record, SELECT [...]

Move to another tablespace

1. To move a TABLE TO another TABLESPACE, issue the following command: ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace>; 2. To move an INDEX, use the following command: ALTER INDEX <index_name> REBUILD TABLESPACE <new_tablespace>; 3. To move the LOB when moving the table, use the following: ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace> LOB (lob_item) STORE AS [...]