Perl: perserve case while replacing

According to this stackoverflow topic it's not quite easy to make a replacement preserving case with simple pattern unless we have perl6's 'ii' modifier, but for practical purposes it mentions great solution like the following one:

$ perl -pi -e 's/(S|s)licing/{$1 eq 'S' ? 'Excel' : 'excel'}/ge' *.pas

PL/SQL: Generic function for converting arbitrary cursor to clob (delimited text)

Thanks to a couple of posts (namely TO_CHAR of an Oracle PL/SQL TABLE type and SQL*Plus tips. #2) I was able to develop very convenient and what's more important generic function for converting arbitrary cursor to clob lines or say any result set to clob lines.
Such function will be very useful for unloading results of an arbitrary query to delimited flat files.

function cursor_to_flat( a_cur in sys_refcursor, a_delimiter in varchar2, a_line_break in varchar2 ) 
return ty_clob_tbl pipelined 
as
  v_prev_rn pls_integer := 0;
  v_clob clob;
begin
  FOR cur_val IN (
    SELECT t.rn
      , EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE
    FROM ( select rownum rn, column_value from TABLE ( XMLSEQUENCE ( a_cur ) ) ) t
      , TABLE (XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2
    order by 1
  ) LOOP
    if ( 0 = v_prev_rn ) then
      v_clob := cur_val.value;
    else
      if ( v_prev_rn != cur_val.rn ) then
        v_clob := v_clob || a_line_break;
        pipe row ( v_clob );
        v_clob := cur_val.value;
      else
        v_clob := v_clob || a_delimiter || cur_val.value;
      end if;
    end if;
    v_prev_rn := cur_val.rn;
  END LOOP;
  pipe row ( v_clob );
end;

Unit test code:

select *
from table( 
  pk_utils.cursor_to_flat( 
    cursor( 
      select 1, 2, 3 from dual 
      union all 
      select 4, 5, 6 from dual 
    ) 
  ) 
);

NB! One problem with this approach is that if you have NULLs in your result set, then they will be completely skipped (not wrapped with delimiters). Now I'm trying to solve it.

Edit
The author of SQL*Plus tips. #2 was totally awesome pointing out how to handle NULLs. Resulting function takes query as clob, but one can easily rollout version with sys_refcursor, as dbms_xmlgen.newcontext will accept it.

function query_to_flat( a_query in clob, a_delimiter in varchar2, a_line_break in varchar2 ) 
return ty_clob_tbl pipelined 
as
  v_prev_rn pls_integer := 0;
  v_clob clob;
  v_cur sys_refcursor;
  v_xml clob;
  v_context dbms_xmlgen.ctxtype;
begin
  v_context := dbms_xmlgen.newcontext( a_query );
  dbms_xmlgen.setnullhandling( v_context, dbms_xmlgen.empty_tag );
  v_xml := dbms_xmlgen.getxml( v_context );
  dbms_xmlgen.closecontext( v_context );
  
  for cur_val in ( 
    select row_num
      , col_value
    from xmltable( --<<<ML
'(#ora:view_on_null empty #){
for $a at $i in /ROWSET/ROW 
  , $r in $a/*
    return element ROW{
      element ROW_NUM{$i}
      , element COL_VALUE{$r/text()}
    }
}'
--ML;
      passing xmltype(v_xml)
      columns
        row_num   int
        , col_value varchar2(100)
    )
  ) loop
    if ( 0 = v_prev_rn ) then
      v_clob := cur_val.col_value; 
    else
      if ( v_prev_rn != cur_val.row_num ) then
        v_clob := v_clob || a_line_break;
        pipe row ( v_clob );
        v_clob := cur_val.col_value;
      else
        v_clob := v_clob || a_delimiter || cur_val.col_value;
      end if;
    end if;
    v_prev_rn := cur_val.row_num;
  END LOOP;
  pipe row ( v_clob );
end;

NB!This solution is still has some drawbacks for large dataset as all data is placed into variable, consuming PGA.
I'll try to address this issue in the following post.
Edit
And here's the followup post!

VBA: Run-time error 3001 Arguments Are Of The Wrong Type... when setting ADODB.Command object members

This forum post saved my day.
I was trying to run Oracle stored procedure with output parameters from Excel VBA with the following piece of code:

Dim cmd As Object
Dim resultSet As Object
Set cmd = CreateObject("ADODB.Command")
With cmd
    .CommandText = "PK_AUTH.LOGON"    
    .NamedParameters = True
    .Parameters.Append .CreateParameter("login", adVarChar, adParamInput, 50, login_)
    .Parameters.Append .CreateParameter("pass", adVarChar, adParamInput, 50, pass_)
    .Parameters.Append .CreateParameter("ldb", adVarChar, adParamOutput, 50)
    .Parameters.Append .CreateParameter("pdb", adVarChar, adParamOutput, 50)
    .CommandType = adCmdStoredProc
    .ActiveConnection = GetConn_()
    Set resultSet = .Execute
    ldb_ = .Parameters.Item("ldb")
End With

And I always got Run-time error 3001 'Arguments Are Of The Wrong Type, Are Out Of The Acceptable Range, or are in conflict with one another' upon invocation of

.CommandType = adCmdStoredProc

or

.Parameters.Append .CreateParameter(...)

no matter which statement I placed first.
After fighting for a while I found this post that stated that error is fired because of late binding of library references, so VB simply did not know of adCmdStoredProc and other constants
That meant that this error has nothing to do with ADODB or Ole or, I just said that it does not know the value of constant. Not very informative in fact...
So, I simply added

Const adVarChar As Long = 200
Const adParamInput As Long = &H1
Const adParamOutput As Long = &H2
Const adCmdStoredProc As Long = &H4

to the Sub header and everything worked fine. Constants are defined in c:\Program Files\Common Files\System\ado\adovbs.inc

VBA: Prototype Class than connects to oracle and checks if connection is up. Plus VBA singleton pattern

Here some prototyping code of a class that is able to connect to Oracle through OleDB and to check if the connection is up before doing some application logic. Class name is TUploadHelper.

Private m_conn As Object

Private Function GetConn_() As Object
    If m_conn Is Nothing Then
        Set m_conn = CreateObject("ADODB.Connection")
    End If
    Set GetConn_ = m_conn
End Function

Private Function Connected_() As Boolean
    Dim recordSet As Object
    Dim value As Long
    Dim errCode As Variant
    Dim errMsg As Variant

    If Not PopErrors() Then On Error GoTo L_ERR_HANDLER
    
    value = 0
    Connected_ = False
    
    Set recordSet = CreateObject("ADODB.Recordset")
    
    Set recordSet.ActiveConnection = GetConn_()
    
    recordSet.Open "select 1 as value_ from dual"
    While Not recordSet.EOF
        value = recordSet.Fields(0)
        If 1 = value Then
            Connected_ = True
            GoTo L_CLEANUP
        End If
    Wend
    ' не вернулось ни одной записи
L_ERR_HANDLER:
    errCode = Err.Number
    errMsg = Err.Description
L_CLEANUP:
    On Error GoTo 0
    If 1 = recordSet.State Then
        recordSet.Close
    End If
    Set recordSet = Nothing
End Function


Private Sub Class_Deinitialize()
    If Not m_conn Is Nothing Then
        If 1 = m_conn.State Then
            m_conn.Close
        End If
        Set m_conn = Nothing
    End If
End Sub

Private Function Authorized_() As Boolean
    Authorized_ = False
End Function

Private Sub Connect_()
'    Dim state_ As Variant
    m_conn.Open "Provider=OraOLEDB.Oracle;Data Source=your_server.world;User ID=your_user;Password=your_pass;PLSQLRSet=1;"
'    state_ = m_conn.State
End Sub

Public Sub Ut()
    If Not Connected_ Then
        Connect_
        If Not Connected_ Then
        End If
    End If
End Sub

And here is module code that implements singleton pattern for TUploadHelper object.

'Singleton pattern
Private g_uploadHelper As TUploadHelper
Public Property Get GetUploadHelper() As TUploadHelper
    If g_uploadHelper Is Nothing Then
        Set g_uploadHelper = New TUploadHelper
    End If
    Set GetUploadHelper = g_uploadHelper
End Property

Just to Remeber: using unix scp utility

$ scp foobar.txt your_username@remotehost.edu:/some/remote/directory 

Credits to the original post.

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).