Uniqualize rows of any table using ora_te

One of the cool ways to use my small template engine for Oracle (ora_te).

procedure uniqualize_rows_no_commit( a_table_name in varchar2 ) as 
  v_clob clob;
  v_te_uniq ty_te;
begin
  v_te_uniq := ty_te.compile_named( q'#
  delete from {$table_name} 
  where rowid in ( 
    select rowid 
    from (
      select rowid
        , row_number() over ( partition by {%for cur:1|join(',')%}{$column_name}{%endfor%} order by null ) rn
      from {$table_name}
    ) 
    where 1 < rn
  )
#'
  );  
  
  select pk_te.substitute( 
    v_te_uniq
    , ty_m( ty_p('table_name', a_table_name ) )
    , cursor( select ty_m( ty_p( 'column_name', column_name ) ) from user_tab_columns where table_name like upper( a_table_name ) ) 
  )
  into v_clob
  from dual;
--  pk_utils.eat( pk_utils.dbms_output_clob( v_clob ) );

  execute immediate v_clob;
end;

Having hard time positioning pictures in Excel cell with vba

Last two days I had a hard time trying to figure out why my vba macro failed to position pictures in cells.
My goal was to add pictures of products loaded from external JPG files (previously resized to 64×64) to each row, so user can easily distinguish the product she is working with just by glancing.
To emmbed pictures into the file I used Shapes.AddPicture method. To properly position it within the cell I passed Left and Top paramters.

Set shp = targetSheet.Shapes.AddPicture( _
                    fileName:=picPath, LinkToFile:=False, SaveWithDocument:=True _
                    , Left:=picLeft, Top:=picTop, Width:=-1, Height:=-1)

By passing -1 to Width and Height parameters the picture preserved it’s original dimensions.

But no matter what parameters I passed to AddPicture method each next product was placed with a little offset from the top of the target cell. And each next product it was misplaced a little bit further to the bottom.
I was almost out of ideas when I understood that the zoom of the target sheet was set to 80, as it’s more was required for this report.
After setting it to 100 in vba macro before inserting pictures and then resetting it back to 80 I was able to properly position images within cells.
So here’s a little chunk of code that inserts pictures for me.

appWorkbooks.OpenText fileName:=m_picsDataFilePath, TextQualifier:=xlNone, DataType:=xlDelimited, Tab:=True, _
    FieldInfo:=Array( _
        Array(1, xlGeneralFormat_), Array(2, xlGeneralFormat_), Array(3, xlGeneralFormat_) _
    )
Set picsWorkbook = appWorkbooks(GetFName(m_picsDataFilePath))

targetSheet.Activate
ActiveWindow.Zoom = 100

plog ("Insert pictures")
picColumnIdx = m_targetDataMapper.GetIdx("V41")
Set workRange = targetSheet.Cells(1, picColumnIdx)
workRange.EntireColumn.NumberFormat = "0"
picLeft = workRange.Left + 2

Set workRange = Nothing

Set sourceSheet = picsWorkbook.Sheets(1)

rowIdx = templateRow + 1
maxRowIdx = templateRow + sourceRowCount
picRowIdx = 2

Do While (rowIdx <= maxRowIdx) ' And rowIdx < 100
    picPath = sourceSheet.Cells(picRowIdx, 1).value
    picTop = 0
    picTop = targetSheet.Cells(rowIdx, picColumnIdx).Top + 2
    ' plog (CStr(picLeft) & " " & CStr(picTop))
    If "" <> picPath Then
        ' picWidth = sourceSheet.Cells(picRowIdx, 2).value
        ' picHeight = sourceSheet.Cells(picRowIdx, 3).value

        Set shp = targetSheet.Shapes.AddPicture( _
            fileName:=picPath, LinkToFile:=False, SaveWithDocument:=True _
            , Left:=picLeft, Top:=picTop, Width:=-1, Height:=-1)
        shp.Placement = xlMoveAndSize
        Set shp = Nothing
    Else
        Set shp = targetSheet.Shapes.AddShape(msoShapeRectangle, _
            picLeft, picTop, 65, 68)
        
        With shp.Fill
            .ForeColor.RGB = RGB(255, 255, 255)
            .BackColor.RGB = RGB(255, 255, 255)
        End With
        shp.Line.Visible = msoFalse
        shp.Placement = xlMoveAndSize
        Set shp = Nothing
    End If
    picRowIdx = picRowIdx + 1
    rowIdx = rowIdx + 1
Loop
picsWorkbook.Close SaveChanges:=False
Set picsWorkbook = Nothing
ActiveWindow.Zoom = 80

Note the addition of the white rectangular shape for each missing picture to fully cover the cell. It solves the problem of stacking pictures when somebody filters products.
I was able to solve this problem, but I wonder how many other quirks does Excel have.

Solving AttributeError: ‘SFTP’ object has no attribute ‘proc’

Seems that great SFTP plugin for Sublime text 2 has some trouble downloading empty folder from server with resulting errors in log file:

2016-09-03 20:37:33
Traceback (most recent call last):
  File "./sftp/threads.py", line 16, in run_with_except_hook
  File "./sftp/threads.py", line 119, in handler
  File "./sftp/commands.py", line 355, in run
  File "./sftp/commands.py", line 510, in do_operation
  File "./sftp/file_transfer.py", line 47, in handler
  File "./sftp/file_transfer.py", line 519, in listr
  File "./sftp/sftp_transport.py", line 322, in close
AttributeError: 'SFTP' object has no attribute 'proc'

To bypass it just add some file to your folder and then download it via SFTP.

Installing xrdb on Cent OS 6.4

I have been struggling to install xrdb on Cent OS 6.4
That’s what helped.

Add http_caching=packages to /etc/yum.conf
yum clean all
rm -fr /var/cache/yum/*
wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
rpm -ivh epel-release-latest-6.noarch.rpm
sudo yum upgrade ca-certificates --disablerepo=epel
yum install xrdp
yum install tigervnc
yum install tigervnc-server
chkconfig --levels 35 xrdp on
service xrdp start

Many thanks to the Internet ^)