Hooray! Moved to keencod.in

Keen /c/o/d/e blog moves from Blogger to separate hosting.
Get ready for new tips, bits and awesome project releases. Check at keencod.in!

Just to Remember: Using GoldenGate defgen utility

./defgen paramfile ./dirprm/defgen.prm

Credits to Oracle DBA blog.

Remote Desktop: Map local drive as network drive to speed up access

A handy feature of MS Remote Desktop is it's ability to access local computer's filesystem.
But the round robin between remote and local is very slow when you access it through 'My Computer' then 'C on LocalMachine' and so on. For me it opens next folder for about 10 seconds or more.
To greatly increase local filesystem access speed (up to multiple times) one should map local filesystem as network drive, say map \\tsclient\D\ as X:.
And don't forget to check 'Reconnect on logon' ;)

PL/SQL: Procedure to copy package with another name

Here's simple procedure build upon custom listagg implementation (see Custom listagg function using cursor to bypass ORA-01489) that creates a copy of existing package with a new name.
It comes very handy when you have several versions of package (say production and development) and constantly switch between them.
It simply obtains package definition and body and then recreates them with a new name.

procedure cp_package( a_owner in varchar2, a_name in varchar2, a_new_name in varchar2 ) as 
  v_head clob;
  v_body clob;
begin
  select regexp_replace( 
    pk_utils.listagg_clob( 
        cursor (
          select text
          from all_source
          where name like upper( a_name ) 
            and owner like upper( a_owner )
            and type like 'PACKAGE'
          order by line
        )
        , ''
      )
      , a_name
      , a_new_name
      , 1
      , 0
      , 'imn'
    )
    , regexp_replace( 
    pk_utils.listagg_clob( 
        cursor (
          select text
          from all_source
          where name like upper( a_name ) 
            and owner like upper( a_owner )
            and type like 'PACKAGE BODY'
          order by line
        )
        , ''
      )
      , a_name
      , a_new_name
      , 1
      , 0
      , 'imn'
    )
  into v_head
    , v_body
  from dual
  ;

  execute immediate 'create or replace ' || v_head;  
  execute immediate 'create or replace ' || v_body;  
  
  dbms_output.put_line( 'Successfully copied package ' || upper( a_name ) || ' to ' || upper( a_new_name ) );
end;

Separate keyboard: Moving toward a dream

I've been dreaming about separate keyboard since I first read someone saying that using it could double typing speed. Think it could be the author of great touch typing courses Vladimir Shahidjanyan who said that. This idea sparked in my head as I always was too lazy and hated to lose time when I could save it. So after a while I bought myself Kinesis Freestyle 2! But as a heavy emacs user I failed to adopt to it. It has very long spacebar and thereby makes it hard to use emacs' cursor movement commands. One should constantly shift from touchtyping default position ('asdf' and 'jkl;') to reach Control and Meta keys with a thumb.

From the day I failed to use Freestyle I decided to build my custom mechanical separate keyboard as some great people on Deskthority do.

Having limited access to desired components and knowing little bout how to put them all together, building custom separate mechanical keyboard still remains a dream for me.

But having no progress with this project irritated me. So I decided to hack two ordinary membrane keyboards into separate one. First design I thought of was to just to cut body of two keyboards in a half and then just to bend or roll the underlying circuit thereby freeing some space in between two parts. Next I planned to plug in both keyboards and just to start using them.

While searching for keyboards with small spacebar I studied how keyboards work. Especially how keyboard controller works with a key matrix

It's not very hard in theory, but what has totally upset me was the possibility of race conditions.

I thought that it will totally break my first design idea, cause say pressing a key on the left-hand keyboard could come after the press on a right-hand though it was hit first.

So I came to the hard decision to decipher which matrix row or column does each controller's output drive. Then I planned to solder pins of both matrices to one controller thus eliminating race conditions. It was possible, but needed a some preparation and actually paused the progress

The whole project would be on hold now If I have not came to David Kadavy's post on using split keyboard and to the comment from Ryan Buie Blakeley who said he recently started using TWO keyboards at the same time.

So my first design was actually possible!

All I had to do is to check for race conditions.

Lucky I had two rare ThinkPad Travel UltraNav Keyboards both tenkeyless and having small spacebars. So the whole setup for checking for race conditions looks like on the photo.

Took it in my girlfriends photo studio but retouched by myself. Don't be too harsh on me not having perfect photoshop skills :)

And you know what? I'm typing this post on this two keyboards and it feels right. No race conditions, and you get accustomed to it in a few minutes (considering you can touch type).

Another nice feature of this setup is that having touchpad and point stick on both of keyboards makes it possible to use both hands to control mouse poiter. I move it with the right touchpad while pressing left ultranav mouse key.

The next thing to try is to implement the first design by actually cutting the body of two keyboards, lucky now I have two HP keyboards with Japanese layout meaning they have really small spacebars.

For those who does not have any special requirements for keyboard layout like having small spacebar, you can try Kinesis Freestyle or any other split ergonomic keyboard (read Davids post on why one should consider trying).

Hope to try the cut-the-half design in a couple of months, so stay in touch!

Edit Somebody already implemented my design idea with cutting body and bending circuit underneath a keyboard. See the project on Instructables. That's great!

Plus from another Instructables project I've learned that Goldtouch V2 Keyboard can easily be hacked into truly separate keyboard. I like it's layout and spacebar size, so it seams soon I'm gonna buy two pieces ^) one for office and one for home.

SQLDeveloper: Handy user report to generate column names and data types for arbitrary query

I've already covered the tough question on converting arbitrary query to clob delimited text (Take 1 and Take 2). The resulting function used a code snippet that produced a list of column names and their datatypes in it's intestines. Based on that snippet here's a SQLDeveloper user reports that produces a list of column names and their datatypes for arbitrary query. A result of a report is ready to be used in create (temporary) table statement or insert statement (for target columns) and in many other ways.
Here's the text of a report:

/* http://stackoverflow.com/questions/6544922/column-names-in-an-empty-oracle-ref-cursor */
DECLARE
  v_ref_cur SYS_REFCURSOR;
  v_cur_handle NUMBER;
  v_count NUMBER;
  v_desc_tab dbms_sql.desc_tab;
  v_statement clob := :a_statement;
  v_print_data_type pls_integer := :a_print_data_type;
  DELIMITER constant varchar2( 5 char ) := chr( 13 ) || chr( 10 ) || chr( 9 ) || ', ';
  PROCEDURE print_desc_tab( a_desc_tab IN sys.dbms_sql.desc_tab, a_print_data_type in pls_integer ) as
    v_data_type VARCHAR2(30);
    v_delimiter varchar2( 30 char ) := '';  
  BEGIN
    dbms_output.put_line( '<pre>' );
    FOR i IN 1 .. a_desc_tab.count LOOP
      SELECT DECODE( to_char( a_desc_tab( i ).col_type ), 1, 'VARCHAR2', 2, 'NUMBER', 12, 'DATE' )
      INTO v_data_type
      FROM dual
      ;
      dbms_output.put( v_delimiter || a_desc_tab( i ).col_name );
      if ( 1 = a_print_data_type ) then
        dbms_output.put( ' ' || v_data_type);  
        case a_desc_tab( i ).col_type
          when 1 then
            dbms_output.put( '(' || to_char( a_desc_tab( i ).col_max_len ) || ' char)' );  
          when 2 then
            if ( 0 != a_desc_tab( i ).col_precision ) then
              dbms_output.put( 
                '(' || to_char( a_desc_tab( i ).col_precision ) || ', ' || to_char( a_desc_tab( i ).col_scale ) || ')'  
              );  
            end if;
          else
          
            null;
        end case;
      end if;
      v_delimiter := DELIMITER;
    END LOOP;
    dbms_output.new_line;
    dbms_output.put_line( '</pre>' );
  END print_desc_tab;
  
BEGIN
  OPEN v_ref_cur FOR v_statement;

  v_cur_handle := dbms_sql.to_cursor_number( v_ref_cur );
  dbms_sql.describe_columns( v_cur_handle, v_count, v_desc_tab );
  
  print_desc_tab( v_desc_tab, v_print_data_type );
  dbms_sql.close_cursor( v_cur_handle );
END;

And here's the link for user report (hosted on Google drive). Once downloaded it can be imported into SQLDeveloper.

Delphi: Most useful keyboard shortcuts

Here are most useful keyboard shortcuts for Delphi IDE, that I extracted from this wiki (some of these I did not know before).

Shortcut	Description
Tab	In Object Inspector activates incremental search for properties. Press again Tab to move focus to property value
Ctr+E	Incremental search (search is an undoable action)
Ctrl+Shift+I	Indent the current selected block
Ctrl+K I
Ctrl+Shift+U	Unindent the current selected block
Ctrl+K U
Alt+[	Go to matching paranthesis
Alt+]
Alt+Left	Browse backward (hotlink history)
Alt+Right	Browse forward (hotlink history)
Alt+Up	Browse to symbol under editor cursor (invoke a hotlink and add it to the hotlink history)
Ctrl+F5	Add watch
Alt+G	Go to line number in editor
Ctrl+Space	Invoke code completition
Ctrl+Shift+Space	Invoce code parameter hints
Ctrl+Enter	Open file at cursor
Ctrl+Shift+Enter	Find all references
Ctrl+Shift+Up	Navigate to method implementation/declaration
Ctrl+Shift+Down
Ctrl+Alt+P	Activate the Tool Palette in filtering mode (start typing, press Enter to drop component)
Ctrl + /	Comment line/selected block
Ctrl + Shift + T	Add TODO
F11	Invoke Object Inspector window
Shift+Alt+F11	Invoke Structure window
Ctrl+Alt+B	Invoke Breakpoints window

By the way, I made this cool looking table with emacs Org mode ;-)

VBA: These blog posts cover hard topics on working with array formulas in Excel

Many thanks to two great blogs RAD Excel and Daily Dose of Excel for covering hard topic on working with array formulas and setting long array formulas for the Range objects in Excel.

WP: How to change WPs default domain

This WordPress Codex page fully covers the topic.

WP: Redirect back to custom page outside of wp directory after comment

Finally added product reviews for A-Rada.com. Used existing WP installation and it's comment system to show reviews on product page (will cover the topic on showing WP content on custom pages outside of it's default directory in following posts).
The problem rose with the default WP "after comment" redirect. By default after user posts it's valuable review he is redirected to default page view (say a-rada.com/blog/?page_id=32), but what we wanted is to redirect him back to appropriate product page.
With an info from wonderful Wordpress.org Support pages and with the simple code in functions.php the desired behavior was achieved.

add_filter( 'comment_post_redirect', 'my_comment_post_redirect' );
function my_comment_post_redirect( $location ){
 if (preg_match("/page_id=([0-9]*)/", $location, $match)) {
  $pageID = $match[1] + 0;
  $page_title = get_the_title( $pageID ); 

  return "http://" . $_SERVER['HTTP_HOST'] . "/product.php?" . $page_title . "#reviews";
 }
 return $location;
}

As you may see, all pages are bound to concrete products, while all posts are actually blog posts, but as I said that's another story (short one) :)

Batch files: easy way of iterating over arguments

Here's the ease way of iterating over argument list passed to batch files (got it from stackoverlow topic). Used it to invoke ImageMagic's commands that involve their own elaborate interpretation of % operator.

rem im.bat
@echo off
setlocal enableDelayedExpansion
set argCount=0
for %%x in (%*) do set /A argCount+=1
set /a arg=0
for /l %%x in (1, 1, %argCount%) do (
 set /a arg=!arg!+1
 call im_impl.bat %%!arg!
)
endlocal

rem im_impl.bat
rem ImageMagic commands
convert %1_%%d.jpg[1-3] -resize 170x130 -set filename:f %%t_also.jpg %%[filename:f]
convert %1_%%d.jpg[1-4] -resize x109 -crop 124x109+3+0 +append %1_4small.jpg

Now it's possible to run im_impl.bat multiple times with different arguments just calling im.bat:

>im.bat clippy_small chandler_mojito

HTML5: Useful posts on responsive design viewport meta tag particularry

Here will be the bunch of useful posts and tutorials on developing responsive web pages.
Is the viewport meta tag really necessary?
HTML5 Boilerplate Doc

UX idea: Advanced highlighting and formatting for code editors and IDEs

It's 2014 but we still lack intellectual tools that could help us to produce high quality code.

As Steve McConnell mentions in his book 'Code Complete' naming standards are vital for any software project.

It's good for you if you have established one and if you adhere to a good naming scheme, even better if whole of your company does, but everyday developer's tasks include an amount of drilling down through code written by others. In the best case that includes looking through code libraries and legacy systems' code, in the worst case it's looking at the code written by your team member (and swearing through teeth cause he refuses to adhere to naming and formatting standards).

When you touch other's code you are on a foe's territory. Foreign naming standards rarely resemble yours so you first have to understand it and second you have to psychologically admit it (that's not always as easy as it seems).

That's where our overpowered CPUs can become handy. IDEs and code editors could know what semantic element is represented with each construct and so they could highlight it with typeface, font weight and color. An extreme example: ordinal type variables could be highlighted differently from class instances and so on. Such advanced highlighting could greatly help in understanding others' code.

There's some advance in the desired direction (see some themes for Sublime, that make use of advanced on the fly parsing), but there's still much to implement!

Another idea is to use CPU power to implement online formatting of code constructs according to your defined rules. This could be done for properly structured and compiled (or checked if we deal with interpreted language) pieces of code, so every team could look at the library code through their own "formatting lens". It seems not so convenient from original authors' point of view, as they will get bug reports with unfamiliar formatting, but they could use their own lens to reformat code back to their likes.

That's not new ideas, they are old ones (see Jeff Raskin's 'Interface', for example). Still such intellectual features are implemented at such a slow pace, that it's worth repeating ideas just not to forget them.

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

Addressing large dataset issue that was mentioned for previously developed cursor to clob conversion functions (see previous post) a new version of a function that converts any query into delimited text was born.
Not so elegant nevertheless it handles nulls (thanks to this StackOverflow topic) and large datasets (that still needs approval).
If you fill able, you can try to convert this procedure into highly optimized pipelined function making use of bulk operations, though I failed due to some bug producing ORA-24374 while working with xml and multisets (maybe I'll cover this bug in the following posts).
So by now the following procedure just inserts clob values into given table

Here are prerequisites:

/* ty_int_to_VCHAR2_row ty_int_to_VCHAR2_tbl type declarations ...*/
create or replace
type TY_INT_TO_VCHAR2_ROW force as object (
 int ingeger
 , vchar varchar2( 4000 char )
 , CONSTRUCTOR FUNCTION TY_INT_TO_VCHAR2_ROW( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW, a_int in pls_integer, a_vchar in varchar2 ) 
  RETURN SELF AS RESULT
);
/

create or replace
type body TY_INT_TO_VCHAR2_ROW as

constructor function TY_INT_TO_VCHAR2_ROW ( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW , a_int in pls_integer, a_vchar in varchar2 ) 
 return self as result
as
begin
 int := a_int;
 vchar := a_vchar;
   return;
end TY_INT_TO_VCHAR2_ROW ;

end;
/

create or replace type TY_INT_TO_VCHAR2_TBL force as table of TY_INT_TO_VCHAR2_ROW;
/

create global temporary table tmp_pk_utils_xml_result_set( 
  row_num integer, name_ varchar2( 30 char ), value_ varchar2( 4000 char )
) on commit preserve rows;
/

And here's the code:

procedure query_to_flat( 
  a_query in clob, a_delimiter in varchar2, a_line_break in varchar2, a_target_table in varchar2 
)
as
  v_prev_rn pls_integer := 0;
  v_clob clob;
  v_cur_desc sys_refcursor;
  v_desc_tab dbms_sql.desc_tab;
  v_count number;
  v_cur_handle NUMBER;
  
  v_cur sys_refcursor;
  
  v_column_names_tbl TY_INT_TO_VCHAR2_TBL := TY_INT_TO_VCHAR2_TBL();
  v_truncate_result pls_integer;
  v_truncate_message varchar2( 32767 char );
begin
$if ( pk_utils_cc.dbg = 1 ) $then
  dbms_output.enable( null );
$end  
  -- read column description into v_desc_tab
  open v_cur_desc for 'select * from ( ' || a_query || ' ) where 1=0';
  
  v_cur_handle := dbms_sql.to_cursor_number( v_cur_desc );
  -- we'll get all columns and their names
  dbms_sql.describe_columns( v_cur_handle, v_count, v_desc_tab );
  
  if v_cur_desc%ISOPEN then
    close v_cur_desc;
  end if;
-- move column descripition data into sql-level table type, so we can use it in queries
  v_column_names_tbl.extend( v_desc_tab.count );
  for v_column_idx in 1 .. v_desc_tab.count loop
    v_column_names_tbl( v_column_idx ) := 
      ty_int_to_vchar2_row( v_column_idx, v_desc_tab( v_column_idx ).col_name )
    ;
  end loop;
  
  open v_cur for a_query;
  
  -- just a truncate that retries if resource is busy, not necessary here, but it's an attitude
  pk_safe_ddl.safe_truncate( 'MY_SCHEMA', 'TMP_PK_UTILS_XML_RESULT_SET' );
  
  -- insert resultset into temporary table, here we'll got only not null columns
  insert into tmp_pk_utils_xml_result_set( row_num, name_, value_ )
  SELECT t.rn /*+ NO_XML_QUERY_REWRITE */
    , t2.COLUMN_VALUE.getrootelement() NAME
    , EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE
  FROM ( select /*+ NO_XML_QUERY_REWRITE */ rownum rn, column_value from TABLE ( XMLSEQUENCE ( v_cur ) ) ) t
    , TABLE ( XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2
  ;
  -- first cross join to get all column names for every row in resulting dataset
  -- then join with temporary table that stores actual values
  for cur_val in (
    select a.row_num
      , a.name_
      , q.value_
    from ( 
      select r.row_num
        , c.int as col_idx
        , c.vchar as name_
      from ( 
        select row_num
        from tmp_pk_utils_xml_result_set
        group by row_num
      ) r
      cross join table( cast( v_column_names_tbl as TY_INT_TO_VCHAR2_TBL ) ) c
    ) a
    left outer join ( 
      select row_num
        , name_
        , value_
      from tmp_pk_utils_xml_result_set
      where name_ is not null
    ) q
      on a.row_num = q.row_num
      and a.name_ = q.name_
    order by a.row_num
      , a.col_idx
  ) loop
    -- some kind of listagg functionality but for potentially long lines (clobs)
    if ( 0 = v_prev_rn ) then
      v_clob := cur_val.value_; 
    else
      if ( v_prev_rn != cur_val.row_num ) then
        v_clob := v_clob || a_line_break;
        execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
        using v_clob
        ;
$if ( pk_utils_cc.dbg = 1 ) $then
        dbms_output.put_line( v_clob );
$end
        v_clob := cur_val.value_;
      else
        v_clob := v_clob || a_delimiter || cur_val.value_;
      end if;
    end if;
    v_prev_rn := cur_val.row_num;
  END LOOP;
  
  execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
  using v_clob
  ;
$if ( pk_utils_cc.dbg = 1 ) $then
  dbms_output.put_line( v_clob );
$end
  if v_cur%ISOPEN then
    close v_cur;
  end if;
  commit;
end;

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.