Remains of the Day: An old but overall great post on using and customizing Oracle SqlDeveloper

The best trick learned is to changed default syntax highlighting to more readable one. Read an article here peoplesofttipster.com/sql-developer.

Syntax reminder: Get number of DML operations from GoldenGate replicat report file

This little piece of a bash code lets you obtain number of DML operations from Oracle GoldenGate report file:

#!/bin/sh
dml_cnt=`perl -p -e 's/\s{2,}/\t/g' /opt/oracle/ggate/dirrpt/R1.rpt | awk -F "\t" '($3 ~/(inserts|updates|deletes):/){dml_count += $4} END {print dml_count}'`

Custom listagg function using cursor to bypass ORA-01489

When using build-in listagg function one should always consider that resulting string must have a length less or equal to 4000 characters. If does exceed then an error 'SQL Error: ORA-01489: result of string concatenation is too long' is thrown.
Here's a package implementing a custom aggregate function that bypasses this restriction.

create or replace PACKAGE "PK_UTILS" AS

type ref_cur is ref cursor;

function LISTAGG_CLOB( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob;

END PK_UTILS;
/
create or replace PACKAGE BODY "PK_UTILS" AS

function listagg_clob( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob
as
  v_single_value clob;
  v_result clob; 
begin
  fetch a_cur into v_single_value;
  if ( a_cur%NOTFOUND ) then
    goto FIN;
  end if;
  v_result := v_single_value;
  loop
    fetch a_cur into v_single_value;
    exit when a_cur%NOTFOUND;
    v_result := v_result || a_delimiter || v_single_value;
  end loop;
<<FIN>>
  return v_result;
end;

END PK_UTILS;
/

And here is a small user test emulating 'within group (order by ...)' functionality.

with prepared as (
  select 1 a, 1 b from dual 
  union all
  select 2 a, 2 b from dual 
  union all
  select 3 a, 1 b from dual 
  union all
  select 4 a, 2 b from dual 
)
select q.b
  , pk_utils.listagg_clob( cursor( select a from prepared where b = q.b order by a desc ) , ',' ) 
from ( 
  select b 
  from prepared
  group by b 
) q
;/

         B LISTAGG_
---------- ------------------
         1 3,1               
         2 4,2               

Moving Oracle GoldenGate Director repository to different server

Just moved Oracle GoldenGate Director 11.1.1.0 oracle repository database from side by side instance to different server using DBMS_DATAPUMP and was wondering how to change weblogic domain configs to point them to the new destination.
The solution is to change GG_Dir/domain/config/jdbc/ggds-datasource-jdbc.xml. Have changed the following part:

 <jdbc-driver-params>
    <url>jdbc:oracle:thin:@//my.server:1521/service_name</url>
    <driver-name>oracle.jdbc.OracleDriver</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>ggs_dir</value>
      </property>
      <property>
        <name>portNumber</name>
        <value>1521</value>
      </property>
    </properties>
    <password-encrypted>{AES}YoUrPaS5w0rD</password-encrypted>
  </jdbc-driver-params>

Just to remember: various C++ tips

1. The assignment operator of a derived class must explicitly perform the assignment of it's base class

Derived& Derived::operator =(const Derived& aRhs){
  if( this != &aRhs ){
    Base::operator=(aRhs);
  }
  return *this;
}

2. Virtual destructor is vital for every polymorphic class because it's called when applying operator delete to the pointer of a base type pointing to the object of the derived type.
3. Never pass auto_ptr as reference.
4. Whenever a const auto_ptr is passed or returned as an argument, any attempt to assign a new object results in a compile-time error. With respect to constness, a const auto_prt behaves like a constant pointer (T* const p) not like a pointer that refers to constant (const T* p).
5. auto_ptr as memeber: implement the copy constructor and operator=. By default they transfer ownership.
6. Use const auto_ptr if member is to refer to the same object through all the lifetime.
7. To properly wrap class into Loki::SingletonHolder make the following members private:

• default constructor
• copy constructor
• operator=
• destructor
• operator&

8. If any constructor for a class is declared then the compiler won't generate the default one.

GoldenGate: Enable supplemental logging for tables upon their recreation

Assuming that minimal supplemental logging is enabled at database level with

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

include following instructions to your table creation script to enable GoldenGate capture all changes from the fresh start of your tables life:
If the table has a primary key:

ALTER TABLE xxx ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

If the table doesn’t have a primary key and have a unique index:

ALTER TABLE xxx ADD SUPPLEMENTAL LOG GROUP (first unique index columns) ALWAYS;

If the table doesn’t have a primary key or a unique index or if it has a manipulation or a filter defined on columns that are not part of the PK:

ALTER TABLE xxx ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Taken from this forum

Just to remember: starting firefox profile manager

To start firefox profile manager one should:
1. Shut down all firefox instances

2. >firefox.exe -p

To run firefox with created profile

>firefox.exe" -p backdoor -no-remote