20/12/2016

Facebook and Airbnb login bug, now you are someone else

Well, everyone nowadays is running around trying to spot and fix bugs for money and I just stumble upon someone else's information for free.

This is an unexpected Christmas gift given it happened close to this huge fail from VISA, which makes it possible to guess full credit card details in a frighteningly fast amount of time. So read that article first, and then image what could someone do if they had all your personal info, including partial credit card data.

10/12/2016

[Oracle] Initialise DB statistics to improve performance

A couple days ago we were analysing a customer issue concerning DB performance on simple and already optimised queries (indexes + hints).

Somehow, the customer had bad performance even after calculating statistics for tables and indexes. Turns out that everything was good except for the fact that Oracle can't imagine future statistics without some help; if a table is only filled after some time that the applications run, it is important to gather statistics again after the first initialisation.

It is also possible however to provide fake statistics immediately after the objects are created or modified so that the DB already has an idea of how the objects will look like and choose different query execution plans than the ones it would use for freshly created objects.

That's what the DBMS_STATS package and its EXPORT__STATS and IMPORT__STATS procedures are for.

[Oracle] List uploaded Java resources

After extending functionality on an Oracle DB with Java resources, it is possible to list the available ones and their status with a query on the user_objects table:

SELECT
  object_name
 ,object_type
 ,status
 ,timestamp
FROM
  user_objects
WHERE
  (    object_name NOT LIKE 'SYS_%' 
   AND object_name NOT LIKE 'CREATE$%' 
   AND object_name NOT LIKE 'JAVA$%' 
   AND object_name NOT LIKE 'LOADLOB%'
  ) 
AND object_type LIKE 'JAVA%'
ORDER BY
  object_type
 ,object_name
;

08/10/2016

[Oracle] SQL SELECT cast to table

In Oracle it is possible to cast one type to another simply by using the CAST keyword.

This also applies to collections, meaning that the result of a SELECT can be casted to varray or nested table; in this case it is necessary to add the MULTISET keyword and make sure that all elements in the collection have a valid match.

Eg suppose you have a type:

CREATE TYPE int_list AS TABLE OF NUMBER(9);

You can run a query that selects some integers and cast the result set to int_list:

SELECT
  CAST(
    MULTISET(
      SELECT t.int_value
      FROM myTable t
    )
   AS int_list)
FROM dual;


[Sheet music] Geometry Dash

Geometry Dash is an ok mobile game with awesome soundtrack, here are some music sheets:

- Cycles
- Dry out
- Back on track
- Theory of everything
- Polargeist
- Jumper
- Can't let go
- Time machine

[Sheet music] Monkey Island

Here are some Music sheets from Monkey Island:

- Main theme
- The Scumm Bar
- A pirate I was meant to be (Monkey Island 3)

23/09/2016

[PL/SQL] existing state of package has been invalidated error

After compiling Oracle packages, it is possible to get a "existing state of packages has been discarded" or "existing state of package has been invalidated" error.

The cause is usually a global variable or constant declared in the package or package body that is re-initialized after the compilation action. The error is thrown to avoid having programs read the wrong state (variable/constant) of the package.

The easiest way to solve this issue is to simply disconnect the session and reconnect.

21/07/2016

[Sheet Music] Garry Schyman - Trip the light

Download it here

The original version sent by none others than the actual Garry!

[TOAD] Oracle explain plan error looping chain of synonyms - fix

TOAD is a powerful client for Oracle DB, although I have to admit SQLDeveloper is not that bad either (and it's also free).

Sometimes while trying to get an explain plan, the error "ORA-01775 - looping chain of synonyms" might occur.

The fix is quite easy: from the options menu go to the Oracle - General section and find the Table field. The value there should be PLAN_TABLE rather than TOAD_PLAN_TABLE.

Save settings, restart TOAD and you're set

04/06/2016

[Ubuntu] fix plymouthd splash load boot error

We sometimes struggle to keep up with all the things that are constantly evolving, so I like how Ubuntu still gives us reassurances that some important features are cemented in its core, such as the plymouthd boot splash error: "plymouthd could not load boot splash: could not access needed shared library".

Just update the kernel and you might be the lucky winner that gets stuck at boot.

The fix is easy and fast enough though. The immediate thing you can try is to edit the boot options when GRUB presents you the list of boot choices:
  1. select the Ubuntu line corresponding to the kernel you want to boot and press the e key
  2. find the line where the ro quiet splash parameters are set (unless you have already changed them in the past), but then you know which line I'm talking about anyway ;)
  3. remove the splash parameter
  4. add at the end of the line: gfxpayload=keep
  5. press F10 to boot

23/05/2016

[Linux] Connect Android phone for file transfer

One of the things I like about Android, is the ease with which you can connect your device to any computer and start transferring files right away. Recent versions of the OS however are using MTP to perform the exchanges.

On some Linux distros, this means you need to install additional packages to keep using your phone as you prefer:

mtp-tools mtpfs

After installation, the phone should be accessible via your file manager

[LXDE] LUbuntu desktop composition enable key shortcuts

The openbox window manager bundled with LUbuntu is powerful enough to provide the same functionality as similar applications, but it's unfortunately delivered with a barebone configuration.

One functionality I'm missing it's the ability to drag, resize and snap windows to desktop sides. Luckily I found code pieces to enable such functionality, albeit not via mouse controls but key combinations; to add it, simply edit ~/.config/openbox/lubuntu-rc.xml and add under the line
 <chainQuitKey>C-g</chainQuitKey>  

this code:
 <keybind key="W-Left">    # HalfLeftScreen  
  <action name="UnmaximizeFull"/>  
  <action name="MoveResizeTo">  
   <x>0</x>  
   <y>0</y>  
   <height>100%</height>  
   <width>50%</width>  
  </action>  
 </keybind>  
 <keybind key="W-Right">    # HalfRightScreen  
  <action name="UnmaximizeFull"/>  
  <action name="MoveResizeTo">  
   <x>-0</x>  
   <y>0</y>  
   <height>100%</height>  
   <width>50%</width>  
  </action>  
 </keybind>  

Then just do an openbox --restart

You can now use the Windows key (Super) and the right or left arrow to snap the currently active window to the right or left side of the screen, at 50% width

Note: apparently openbox is reserving some space for the toolbar on the bottom of the screen, so even declaring 100% height will NOT fill all available space after resizing.

10/04/2016

[xterm] Edit appearance and store preferences

It's possible to edit the xterm console appearance and permanently store the changes by creating or editing the ~/.Xresources file. You can find the full list of settings on the man page, here are some useful ones:

XTerm*Background: white
XTerm*Foreground: black
XTerm*geometry: 140x45

The code above will set the default window size to 140x45 and display black characters on white background.

To apply the settings to all future xterm consoles, even after a reboot, just run once:

xrdb -merge ~/.Xresources

and finally restart xterm

[Kodi] Crash on exit bug fix

I was trying out Kodi, specifically the Kodibuntu version they provide, aka: Kodi installed on a quite barebone LUbuntu, and I must say I'm really impressed. Outputting 5.1 audio over SPDIF and watching HD movies is made very easy and works out of the box.

There is however one interesting bug that comes in various forms and basically causes Kodi to crash or freeze everytime you try to quit it.

The fix is very simple as the issue seems to be caused by the Version check addon; disabling it is sufficient to solve the problem.

09/04/2016

[LXDE] LUbuntu change clock format

Again, out of all the nice to have stuff people left out of LXDE, an easy way to edit the clock date and time format is missing.

Luckily, the tinkering required is minimal. Right click on the clock and select "Digital Clock" Settings, then enter the desired format in the Clock Format section.

For example:

%a %d %b %R

would result in:

[3 letters day name] [day number] [3 letters month name] [hours:minutes in 24 hours format]

eg:

Sat 09 Apr 16:17

[LXDE] LUbuntu change keyboard shortcuts

LXDE is extremely lightweight, maybe a bit too much, in the sense that they left out some user-friendly portions such as an easy graphical way to edit keyboard shortcuts.

You can still do it with a bit of tinkering, by editing the ~//.config/openbox/lxde-rc.xml file.

Remember that this syntax applies:

C = CTRL key
S = SHIFT key
A = ALT key
W = Windows key

[LXDE] LUbuntu auto num lock on boot

If you experience issues with the keyboard num lock not being automatically activated at boot or after lock screen wakeup in LXDE, try this:

- install numlockx:

sudo apt-get install numlockx

- edit /etc/xdg/lubuntu/lxdm/lxdm.conf and find numlock line then remove the comment and enable it:

numlock=1

- edit /etc/lightdm/lightdm.conf and add this line:

greeter-setup-script=/usr/bin/numlockx on




[LXDE] Lock screen in LUbuntu via shortcut

LUbuntu users may encounter a bug that prevents them from locking the screen either from command line or keyboard shortcut.

In my case, solving it was quite easy:

First install lxlock and light-locker, you might already have them:

sudo apt-get install lxlock light-locker

Then edit ~//.config/openbox/lubuntu-rc.xml and modify or add the lockscreen section:

  <!-- Lock the screen on W + L-->  
  <keybind key="W-L">  
   <action name="Execute">  
    <command>lxlock</command>  
   </action>  
 </keybind>  


Finally restart openbox:

openbox --restart

05/04/2016

[Avaloq] Define task parameters to be used only in the reports

When defining a task in Avaloq, it's possible to use the pseudo keyword to mark parameters that should not be passed to the underlying task, but should still be accessible from the REP DTM sources.

This is useful if their purpose is to enhance the report functionality but they do not provide meaningful input for the task procedure itself.

They are delcared as:

i_[param_name]  pseudo  [param_type] 

eg:

i_my_param  pseudo  number

And can then be accessed in the REP DTM like any other task parameter:

task_exec.param("my_param").nr_val


31/03/2016

[Oracle] Search in sources

In Oracle it's possible to search for text in source files with a query on the all_source object. Of course the resulting data will only include objects that are accessible by your current user.

For example, to search in sources with owner myOwner that include the myString string you can run:

SELECT *
FROM all_source
WHERE owner = 'myOwner'
AND LOWER(text) like '%myString%' --case insensitive search


19/03/2016

[Avaloq] Get task parameter value in datamart and use it in layout

When creating a new report in Avaloq, you will typically create/edit a datamart (REP DTM source) and a layout (eg: REP SCREEN scource).

If you wish to use a task parameter to enhance the functionality offered by your report, first store the value in a variable inside the DTM (it will be at TOP level):

 [Report 2.0]  
   
 report some_task.some_dtm  
   
  datamart  
   on init  
    --get the parameter passed to the task  
    my_task_param   some_datatype   assign   [task_exec.param("my_task_param").nr_val(1)] --remember to check the DDIC for more appropriate methods other than nr_val. Also pay attention to the sequence number!  
   
   connect some_ddic as dm  
        
      ...  


then access it from your layout with:

top.my_task_param

[PLSQL] Split string by delimiter and store it in VARRAY

Here is some sample Oracle PL/SQL code to split a string using a specific delimiter and storing the result pieces in a VARRAY.

 DECLARE  
   
 TYPE t_varchar2_varray IS TABLE OF VARCHAR2(4000);  
 list_val t_varchar2_varray := t_varchar2_varray();  
 l_explode_at PLS_INTEGER;  
   
 l_string VARCHAR2(4000) := your_string;  
 l_separator VARCHAR2(1) := your_separator;  
   
 BEGIN  
   
 l_explode_at := INSTR (l_string, l_separator); -- if the separator is not in the string, it returns 0   
   
 IF l_explode_at != 0 THEN   
        -- split all the values in the list and store them in our temp variable   
      LOOP   
      EXIT WHEN l_string IS NULL; -- keep going as long as there are values in the list   
        
       l_explode_at := INSTR (l_string, l_separator);   
       list_val.EXTEND;   
         
       -- if we have other values after this one   
       IF l_explode_at !=0 THEN   
         
            -- store it and keep going   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, l_explode_at - 1)); --get the current value   
            l_string := SUBSTR (l_string, l_explode_at + 1); -- and move on the the next one   
              
       -- if there are no more values after me   
       ELSE   
            -- store the last one and quit   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, LENGTH(l_string)));   
            l_string := NULL;   
       END IF;   
         
      END LOOP;   
        
 END IF;  
   
 END;  


Additionally, if you want to remove "empty" values (eg: tabs or spaces) from the string, you can add this code initially:

l_string := regexp_replace(l_string, '[[:space:]]*','');

[SQL] Oracle count elements in nested table

When working with collections in Oracle, you might need to count the number of elements they contain.

In the case of Nested Tables, you can either COUNT(*) after you unnest them:

SELECT COUNT(nt.*)
FROM myTable t, TABLE(t.nested_table) nt

or use the far simpler CARDINALITY function (returns NULL in case of empty list):

SELECT NVL(CARDINALITY(t.nested_table), 0)
FROM myTable t

In case of VARRAYs instead, you must still COUNT the elements but if you're working in PL/SQL though, you can go with:

myVarray.COUNT

28/02/2016

[VBA] Excel open popup from macro

As usual when dealing with business users, you'll find yourself tinkering with Excel files which could include macros.

As usual when business users try their hands at this newfangled tech stuff, they will end up putting in a minuscule yet showstopping mistake you need to spot.

Although the VBA debugger is actually pretty good, sometimes the error is just stemming from a wrong value being inserted in a cell. In this case, it's just faster to simply display the value(s) in a popup window and then searhing for the offending cell in the spreadsheet.

The MsgBox function alongside with the CStr one will become your new best friends here:

MsgBox ("I am putting this value " & CStr(your_value) & " but maybe it is wrong")

Where & is used to concatenate strings and your_value can be a variable or an expression within the macro you're debugging.