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

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.