Skip to content

Instantly share code, notes, and snippets.

View VikramVasudevan's full-sized avatar

Vikram Vasudevan VikramVasudevan

View GitHub Profile
@VikramVasudevan
VikramVasudevan / sample.js
Created April 14, 2022 04:47
Sample Javascript Functions
function helloWorld(){ return ['Hello World','How are you?'] }
function generateNumbers(){ return [ { 'random_number' : Math.random(), 'some_message':'How are you?'} ]; }
@VikramVasudevan
VikramVasudevan / oracle-rowscn-demo-part4.sql
Created April 26, 2020 04:02
Oracle ROWSCN Demo - Part 4
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
VikramVasudevan / oracle-rowscn-demo-part3.sql
Created April 26, 2020 04:01
Oracle ROWSCN Demo - Part 3
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
VikramVasudevan / oracle-rowscn-demo-part2.sql
Created April 26, 2020 04:01
Oracle ROWSCN Demo - Part 2
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
VikramVasudevan / oracle-rowscn-demo-part1.sql
Created April 26, 2020 04:00
Oracle ROWSCN Demo - Part 1
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
VikramVasudevan / oracle-rowscn-demo-full.sql
Created April 26, 2020 03:59
Demo of ora_rowscn for CDC
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
)
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 '
SELECT REPLACE (
REPLACE (trigger_sql,
'{INSERT_SET_CLAUSE}',
insert_column_list || ';'),
'{UPDATE_SET_CLAUSE}',
update_column_list || ';')
trigger_sql
FROM table_agg_stg
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,
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),