Vikram Vasudevan VikramVasudevan
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function helloWorld(){ return ['Hello World','How are you?'] } | |
| function generateNumbers(){ return [ { 'random_number' : Math.random(), 'some_message':'How are you?'} ]; } |
VikramVasudevan
/ oracle-rowscn-demo-part4.sql
Created
April 26, 2020 04:02
Oracle ROWSCN Demo - Part 4
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT 'Inserting 3 rows into table...' STATUS_MSG FROM DUAL; | |
| INSERT INTO T (x,y) VALUES(-1,'SOMENEWENTRY'); | |
| INSERT INTO T (x,y) VALUES(-2,'SOMENEWENTRY2'); | |
| INSERT INTO T (x,y) VALUES(-3,'SOMENEWENTRY3'); | |
| COMMIT; | |
| SELECT 'Fetching changed data in table ...' STATUS_MSG FROM DUAL; | |
| SELECT t.* | |
| -- dbms_rowid.rowid_block_number (ROWID) blockno, | |
| -- SCN_TO_TIMESTAMP (t.ORA_ROWSCN), |
VikramVasudevan
/ oracle-rowscn-demo-part3.sql
Created
April 26, 2020 04:01
Oracle ROWSCN Demo - Part 3
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT 'Updating 1 record in table ...' STATUS_MSG FROM DUAL; | |
| UPDATE t | |
| SET y = 'DUAL123' | |
| WHERE x = 1; | |
| COMMIT; | |
| SELECT 'Fetching changed data in table ...' STATUS_MSG FROM DUAL; | |
| SELECT t.* | |
| -- dbms_rowid.rowid_block_number (ROWID) blockno, |
VikramVasudevan
/ oracle-rowscn-demo-part2.sql
Created
April 26, 2020 04:01
Oracle ROWSCN Demo - Part 2
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| var max_row_scn number; | |
| SELECT 'Resetting max scn ...' STATUS_MSG FROM DUAL; | |
| BEGIN | |
| SELECT MAX(ORA_ROWSCN) max_row INTO :max_row_scn FROM T; | |
| END; | |
| / | |
| print max_row_scn |
VikramVasudevan
/ oracle-rowscn-demo-part1.sql
Created
April 26, 2020 04:00
Oracle ROWSCN Demo - Part 1
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| set echo off time on timing off linesize 9999 feedback off heading off | |
| -- The following statement might fail if table t does not exist. | |
| drop table t; | |
| /* Create Table wiht ROWDEPENDENCIES enabled */ | |
| CREATE TABLE t | |
| ( | |
| x, | |
| y |
VikramVasudevan
/ oracle-rowscn-demo-full.sql
Created
April 26, 2020 03:59
Demo of ora_rowscn for CDC
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| set echo off time on timing off linesize 9999 feedback off heading off | |
| drop table t; | |
| /* Create Table wiht ROWDEPENDENCIES enabled */ | |
| CREATE TABLE t | |
| ( | |
| x, | |
| y | |
| ) |
VikramVasudevan
/ oracle-audit-trigger-full.sql
Created
January 1, 2020 07:44
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH prm_input | |
| AS (SELECT table_name, | |
| 'CREATE OR REPLACE TRIGGER "YOUR_DATA_OWNER"."TRG' | |
| || table_name | |
| || '"' | |
| || ' AFTER INSERT OR UPDATE OR DELETE ' | |
| || ' ON ' | |
| || table_name | |
| || ' FOR EACH ROW ' | |
| || ' DECLARE ' |
VikramVasudevan
/ oracle-audit-trigger-004.sql
Created
January 1, 2020 07:43
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT REPLACE ( | |
| REPLACE (trigger_sql, | |
| '{INSERT_SET_CLAUSE}', | |
| insert_column_list || ';'), | |
| '{UPDATE_SET_CLAUSE}', | |
| update_column_list || ';') | |
| trigger_sql | |
| FROM table_agg_stg |
VikramVasudevan
/ oracle-audit-trigger-003.sql
Created
January 1, 2020 07:43
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| table_agg_stg | |
| AS (SELECT trigger_sql, | |
| table_name, | |
| listagg ( | |
| 'audtab.' || column_name || ':= :NEW.' || column_name, | |
| '; ') | |
| WITHIN GROUP (ORDER BY column_id) | |
| insert_column_list, | |
| listagg ( | |
| 'audtab.' || column_name || ':= :OLD.' || column_name, |
VikramVasudevan
/ oracle-audit-trigger-002.sql
Created
January 1, 2020 07:41
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| table_columns_stg AS (SELECT c.table_name, | |
| t.trigger_sql, | |
| c.column_name, | |
| column_id | |
| FROM prm_input t, user_tab_columns c | |
| WHERE t.table_name = c.table_name), |
NewerOlder