Just to remeber: Replacing something with new line in Emacs

C-M-5 <type something> <enter> C-q C-j

Credits to the original post.

Oracle GoldenGate: Run SQLEXEC within MAP clause only once, accessing @GETENV parameters

As of version 11.2.3.0 of Oracle GoldenGate there is a restriction for standalone SQLEXEC statements, such statements can't access parameters normally accessed with @GETENV function.

To bypass this restriction we should make SQLEXEC within MAP clause to run only once per every replicat invocation.

This can be achieved using global temporary table as target and filtering upon result of @GETENV( "STATS", "TABLE",.. ) function.

So the setup is the following:

At first create global temporary table

create global temporary table gg.tmp_gg_dummy ( id number( 14, 0 ) ) on commit delete rows;  
alter table gg.tmp_gg_dummy add constraint tmp_gg_dummy_pk primary key ( id );  

This table will be the target table for multiple source tables. As it is created as GTT, we don't have to manually managed inserted records.

Then add the following as the last mapping into your replicat parameter file

map m.*, target gg.tmp_gg_dummy, handlecollisions, colmap ( id = @GETENV ("RECORD", "FILERBA") )   
sqlexec( id set_first_seq_no1, spname pk_replication_accounting.set_first_seq_no,
params(a_fileseqno = @GETENV( "GGFILEHEADER", "FILESEQNO" ) ), afterfilter ),   
filter (@getenv( "STATS", "TABLE", "GG.TMP_GG_DUMMY", "DML" ) = 0), insertallrecords;   

If not added last, this mapping will cause resolution errors.

Let's examine, how it works.

Mapping is resolved using *, so every source table in "M" scheme will match and every operation upon source table will be replicated to gg.tmp_gg_dummy.

INSERTALLRECORDS instructs to transform every operation into INSERT.

HANDLECOLLISIONS prevents PK errors.

As column mapping we need arbitrary value, so we use relative byte address of a record.

The filter will match only for the first DML operation as after replicating one record @getenv( "STATS", "TABLE", "GG.TMP_GG_DUMMY", "DML" ) will return values greater than zero.

And finally we instruct GG to run stored procedure only if the filtering was successful with AFTERFILTER keyword.

Inside SQLEXEC we successfully obtain FILESEQNO value.

Looking at the report file we clearly see that stored procedure was executed only once.

From Table M.EVENTTAIL to GG.EVENTTAIL
       #                   inserts:         26  
       #                   updates:         13  
       #                   deletes:         0  
       #                  discards:         0  
From Table M.EVENTTAIL to GG.TMP_GG_DUMMY:  
       #                   inserts:         0  
       #                   updates:         1  
       #                   deletes:         0  
       #                  discards:         0  
  Stored procedure set_first_seq_no1:  
         attempts:         1  
       successful:         1  

Edit
Seems that I have missed SQLEXEC optional parameter that instructs GG to execute stored procedure only once per invocation: it's EXEC ONCE. Blame on me, but that proves that GG is flexible enough making it possible for one to repeat this functionality with filter clause.
There are more options in addition to ONCE: MAP, TRANSACTION and SOURCEROW so it's worth checking the reference.
Edit 2

More considerate look at EXEC ONCE and it's description Executes the procedure or query once during the course of the Oracle GoldenGate run, upon the first invocation of the associated MAP statement. The results remain valid for as long as the process remains running. made me understand that one should create "* to temp table" mapping anyway, plus if you don't want to populate temp table with useless records during invocation, you should use filter with DML stats, or unconditionally ignore all records for that map statement (that's a viable option).

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

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

Remains of the Day: Add 'Open command window here' context menu to any file

Command prompt here PowerToy for Windows adds 'Open command prompt here' context menu for every folder.
Use this registry file to add 'Open command prompt here' to any file context menu:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\*\shell\Open command prompt here]

[HKEY_CLASSES_ROOT\*\shell\Open command prompt here\command]
@="cmd"

Converting a VirtualBox VM to a VMWare Workstation 7.0.1 VM

To successfully convert a VirtualBox (latest version) vm to VMWare Workstation 7.0.1 vm one should do the following:
1. In VirtualBox export vm as ovf virtual appliance without manifest file (don't forget to check 'ovf version 0.9').
2. Ensure that VMWare will understand newly generated ovf file. To do it:
2.1 Edit ovf file so 'rasd' elements are sorted in the following order:

  <Item>
    <rasd:Caption>Some caption</rasd:Caption>
    <rasd:Description>Some description</rasd:Description>
    <rasd:InstanceId>0</rasd:InstanceId>
    <rasd:ResourceType>1</rasd:ResourceType>
    <rasd:ResourceSubType>2</rasd:ResourceSubType>
  </Item>

2.2 Ensure that rasd:Parent elements precede rasd:AdressOnParent elements.
2.3 Change the extension of original vmdk disk files to something like '~vmdk'.
2.4 Check that conversion is possible using VMWare OVF Tool with the following command:

"C:\Program Files\VMware\VMware OVF Tool\ovftool.exe" --noDisks "ODD - Source.ovf" "ODD - Source.vmx"

You should see the following output:

Opening OVF source: ODD - Source.ovf
Opening VMX target: ODD - Source.vmx
Writing VMX file: ODD - Source.vmx
Transfer Completed
Warning:
 - No manifest file found.
 - No manifest entry found for: 'ODD - Source-disk1.vmdk'.
 - No manifest entry found for: 'ODD - Source-disk2.vmdk'.
Completed successfully

If not, the tool will report error lines. So correct errors and rerun.
2.5 Delete vmdk files generated by ovftool and change back the extension of original files.

Pitiful but OVF Tool alone cannot properly convert ovf to vmx. Disk partitions will remain read only as made by VirtualBox export process.
3. So the last thing to do is to use VMWare Converter 4.0.1 (can be downloaded following links in this post) to convert ovf to vmx changing disks types to 2 GB Split pre-allocated.

On database link naming

Name dblinks as a site it refers.
For example name it 'subdomain.somesite.com' and then create synonyms to objects on the site:

create synonym some_table for some_table@subdomain.somesite.com

Doing so you help other developers to decipher the site particular database link refers to without consulting all_db_links system view.

Perl script to extract and present hex colors found in arbitrary text

The following script searches STDIN for 3 or 6 symbol hex values and prints a html structure that represents those values as colors on page.

#!/usr/bin/perl -w
use Set::Scalar;

$uniqueColors = Set::Scalar->new;
while( defined( $_ = <STDIN> ) ){
 while ( $_ =~/(#[a-fA-F0-9]{1,6})/g ){
  $uniqueColors->insert( $1 )
 }
}
print <<HEADER;
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
   "http://www.w3.org/TR/html4/strict.dtd">
<HTML>
   <HEAD>
      <TITLE>Found HEX colors</TITLE>
   </HEAD>
   <BODY>
HEADER
while ( defined( my $color = $uniqueColors->each ) ){
 print "<p style=\"background: " . $color . "\">" . $color . "</p>\n"
}
print "</BODY>
</HTML>"

Used this one to parse color-theme-tangotango.el emacs color theme file.
To view example output hit "read more".

Interesting remarks on using standard libraries

While searching for implementation of Sets in Perl bumped into these interesting arguments for using standard libraries on StackOverflow.

Use one of the many Set modules on CPAN. Judging from your example, Set::Light or Set::Scalar seem appropriate.

---

I can defend this advice with the usual arguments pro CPAN (disregarding possible synergy effects).

1. How can we know that look-up is all that is needed, both now and in the future? Experience teaches that even the simplest programs expand and sprawl. Using a module would anticipate that.
2. An API is much nicer for maintenance, or people who need to read and understand the code in general, than an ad-hoc implementation as it allows to think about partial problems at different levels of abstraction.
3. Related to that, if it turns out that the overhead is undesirable, it is easy to go from a module to a simple by removing indirections or paring data structures and source code. But on the other hand, if one would need more features, it is moderately more difficult to achieve the other way around.
4. CPAN modules are already tested and to some extent thoroughly debugged, perhaps also the API underwent improvement steps over the time, whereas with ad-hoc, programmers usually implement the first design that comes to mind.

Rarely it turns out that picking a module at the beginning is the wrong choice.

Oracle SQL selects to get familiar with table naming standard and to find junk tables in scheme

Run following selects to discover prefix and suffix hierarchies and to get count of particular prefix/suffix occurrences. That will help you become familiar with naming standard and to find suspicious prefixes/suffixes that belong to tables that probably store junk data.

Highlight empty tables in PowerDesigner model with VB Script

The wonderful thing about PowerDesigner automation is that you can actually connect to database from VB Script to get some crucial information that is unavailable (or was not captured) at a reverse engineering phase.
The following script queries Oracle ALL_TABLES system view to find out if table in the model stores any data. Then those tables that are empty are highlighted with thick maroon frame.

Working with PowerDesigner models through VB interface

PowerDesigner has great automation abilities. The easiest way to work with a model programmatically is to use PowerDesigner's embedded Visual Basic script interface and to run scripts through 'Edit/Run script' window.
Here's an example script that uses given color to highlight tables that have comments or that have at least one column with a comment:

Dim model
Set model = ActiveModel
For each table in model.Tables
   doPaint = false
   tableComment = Trim( table.Comment )
   if ( "" <> tableComment ) then
      doPaint = true
   end if

   if ( not doPaint ) then
      For each column in table.Columns
         comment = Trim( column.Comment )
         if ( "" <> comment ) then
            doPaint = true
            Exit For
         end if
      Next   
   end if
   
   if ( doPaint ) then
      For each symbol in table.Symbols
         symbol.BrushStyle = 6 'Gradient 
         symbol.GradientFillMode = 64
         symbol.FillColor = RGB( 252, 178, 104 ) 
         symbol.GradientEndColor = RGB( 255, 255, 255 )
      Next
   end if
Next

More info about PowerDesigner automation at SyBooks and PowerDesigner's 'Metadata Objects' help file.
Tip: when reverse engineering big unfamiliar scheme you can easily select and move apart particular symbols by adjusting their 'Position' property. For example you can select and move tables with particular prefix:

Dim model
Set model = ActiveModel
PREFIX = "hh_"
For each table in model.Tables
   tableName = LCase( Trim( table.Name ) )
   if ( PREFIX = Left( tableName, Len( prefix) ) ) then
      For each symbol in table.Symbols
         symbol.Position = NewPoint( 0, 0 )
      Next
   end if
Next

After desired symbols are moved to the (0,0) position they can be selected and arranged with 'Symbol->Auto layout command'.
This simple solution of how to distill big scheme came to me after I had reversed database with 2K of tables with a few foreign key constants defined. I faced a brick wall of tables arranged by their column count. All semantics were encoded in table names so I just had to set apart tables with equal prefixes. I've searched for a function to add symbols to selection but with no success. So I decided to move desired symbols to the center of workspace and then to select them with single mouse movement. That's when 'Auto layout' command came in very handy.

An AHK script to select particular tables for reverse engineering in PowerDesigner

The following script helps to select tables (optionally any objects) in the 'Database Reverse Engineering' window of PowerDesigner 15.3 that are stored in txt file.
First one should unmark all objects in the list view, then run the script, hit F5, and after the selection process ends (that can be noticed by stopped blinking of the 'Database Reverse Engineering' window) hit SPACE to mark only selected objects.
Here's the resulting list:

Script uses Acc Library to get the names of items in the ListView and to select them. It should be run under one of the latest releases of AutoHotkey_L.
In my particular case file containing list of objects is called 'tables_to_reverse.txt' (see the script).

Reverse engineering Oracle 11gR2 scheme with Sybase PowerDesigner 12.5 on Windows 7 x64

The following setup was made to perform reverse engineering of Oracle 11gR2 scheme into physical diagram using Sybase PowerDesigner 12.5 on Windows 7 x64.

ConnectionType: JDBC
DBMS type: Oracle
User name: <your scheme name>
Password: <your password>
JDBC driver class: oracle.jdbc.driver.OracleDriver
JDBC connection URL: jdbc:oracle:thin:@//<your host>:<your port>/<your service name>
JDBC driver jar files: c:\Oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar

Plus had to install 32-bit Java and add edit path variable in batch file to make PowerDesigner use appropriate VM:

set path=C:\Program Files (x86)\Java\jre7\bin\client\;%path%
cd "c:\Program Files (x86)\Sybase\PowerDesigner 12\"
pdshell12.exe

Many thanks to Devtype blog and Jan Bartos' answer on google groups.

Two window browser setup to bypass corporate firewall using iOS hotspot

I've just changed a department and was surprised by more strict corporate rules applied to my windows account.
More blogs and sites became totally unreachable because of the corporate firewall.
So to continue productive work I rolled out the following setup:
1. Second LAN connection through iPhone 3gs' wired hotspot established.
2. Second palemoon (a firefox fork) profile created (named backdoor).
3. Second palemoon profile was assigned a different theme (and a different icon by AutoHotkey script that will be covered in the following posts).
4. A shortcut was made that binds second palemoon instance (with 'backdoor' profile) to specific LAN interface using a ForceBindIP utility:

C:\Windows\SysWOW64\ForceBindIP.exe 127.0.0.1 "C:\Program Files (x86)\Pale Moon\palemoon.exe" dummy -p backdoor -no-remote

The dummy argument is used because of the feature of the ForceBind utitlity: it does not pass the first given parameter to the program specified.
Given IP should be replaced with actual ip-address of the second LAN interface.
Now I can surf through unblocked sites through a corporate LAN and access restricted ones through an iPhone hotspot.

Integrating SyntaxHighlighter into this blog (line wrapping enabled)

Finally integrated current version of SyntaxHighlighter into this blog.
Had to include following into the head section of the blogger template:

    <!-- Include required JS files -->
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'/>
     
    <!--
        At least one brush, here we choose JS. You need to include a brush for every
        language you want to highlight
    -->
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushBash.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJscript.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPython.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPerl.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushDelphi.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCss.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCpp.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPlain.js' type='text/javascript'/>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>
     
    <!-- Include *at least* the core style and default theme -->
    <link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/>
    <link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css' rel='stylesheet' type='text/css'/>
    
    <style type='text/css'>
    /* syntaxhighlighter */
    .syntaxhighlighter .line {
            white-space: pre-wrap !important; /* make code wrap */
    }
    </style>
    <style type='text/css'>
    /* syntaxhighlighter */
    .syntaxhighlighter {
      overflow-y: hidden !important;
    }
    </style>
    
    <script src='http://code.jquery.com/jquery-1.5.2.min.js' type='text/javascript'/>
 <script type='text/javascript'>
//<![CDATA[
$(function(){
// Line wrap back
  var shLineWrap = function(){
    $('.syntaxhighlighter').each(function(){
    // Fetch
    var $sh = $(this),
        $gutter = $sh.find('td.gutter'),
        $code = $sh.find('td.code');
    // Cycle through lines
    $gutter.children('.line').each(function(i){
      // Fetch
      var $gutterLine = $(this),
          $codeLine = $code.find('.line:nth-child('+(i+1)+')');
      // Fetch height
      var height = $codeLine.height()||0;
      if ( !height ) {
        height = 'auto';
      }
      else {
        height = height += 'px';
      }
      // Copy height over
      $gutterLine.height(height+' !important');
      console.debug($gutterLine.height(), height, $gutterLine.text(), $codeLine);
    });
  });
};
 
// Line wrap back when syntax highlighter has done it's stuff
var shLineWrapWhenReady = function(){
  if ( $('.syntaxhighlighter').length === 0 ) {
    setTimeout(shLineWrapWhenReady,800);
  }
  else {
    shLineWrap();
  }
};
 
// Fire
shLineWrapWhenReady();
});
//]]></script>
    <script type='text/javascript'>
      SyntaxHighlighter.defaults['gutter'] = false;
      SyntaxHighlighter.config.bloggerMode = true;
      SyntaxHighlighter.all();
    </script>

Done it with a great help from My Tech Notes and Undume Press.
Had to turn gutter off, cause line wrapping malforms line numbering.
More to do: add visual sign of line wrapping, conditional wrapping, etc.

Thing to remember

Since rows can migrate from location-to-location when they are updated ROWID should never be stored an never be counted on to be the same in any database.

Oneliner to wrap long line into 80 characters

cat /dev/clipboard | perl -e 'use Text::Wrap;$Text::Wrap::columns = 160;while (<>){print wrap("","",$_);}' > /dev/clipboard

Had to set $Text::Wrap::columns to 160 to correctly wrap cyrillic characters under Cygwin 7.1.

Useful onliner to make testing in Oracle SQL*Developer a little bit easier

This little script helps to test subroutines using SQL*Developer Run or Debug command.  Just copy code from "Run PL/SQL" window and run the following onliner in a terminal. It uncomments legacy dbms_ouput code and extends capacity of varchar2 variables from 200 to 32767 chars. Now paste clipboard contents back into SQL Worksheet and run.

cat /dev/clipboard | perl -p -e 's/\/\* Legacy output://g; s/\*\///g; s/VARCHAR2\(200\)/VARCHAR2\( 32767 char \)/g;s/^\s+:.*$//g' > /dev/clipboard