Great design: Go to parrent topic link.

SyBooks got some great design insight for hierarchical documentation. All greatness comes from "Go to parent topic" link placed at the end of the document. It's just easy to click that link when you've read or skimmed the whole topic. "Current location" navigation element placed at the top of the page looses that easiness cause you have to read more and to think even more. With "Go to parent topic" link one has no alternatives and she don't even need to know the name of the parent topic. It always leads one level up.

Check it out:

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