Wednesday, September 12, 2018

Avoid select min/max with Oracle

I hate select max clauses with Oracle. My company used to write subqueries which such expressions. It's slow and unreadable. Conditions in subquery with upper table create deep dependencies and awfull links between table. It doesn't allow to reuse code.

That's why I investigate on a performant solution to avoid select max/min. A solution is "dense_rank first". It will help you to retreive only one line per group. It's difficult to explain. So, I made a sample on SQL Fiddle.

Schema is :

CREATE TABLE test (student, year, status, program) AS
SELECT 'john', 2010, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2011, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2012, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2013, 'failed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2014, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'clara', 2011, 'failed', 'mstf' FROM DUAL
UNION ALL SELECT 'clara', 2012, 'passed', 'msmmd' FROM DUAL;

Sample request is :

SELECT
max(rownum) keep (dense_rank first order by student) AS id,
student,
max(year) year,
max(status) status,
max(program) program
FROM test
GROUP BY student

And the result is :

ID STUDENT YEAR STATUS PROGRAM
7 clara 2012 passed mstf
5 john 2014 passed bba

You can try it by yourself :

http://sqlfiddle.com/#!4/64c7ce/3/0


Enjoy!


Wednesday, February 07, 2018

Download Oracle JDK from command line

Found on the web. You can adapt the url with one grabbed from Oracle JDK download page. What I love here is the fact is send Oracle licence agreement threw the header.


wget --no-cookies \
--no-check-certificate \
--header "Cookie: oraclelicense=accept-securebackup-cookie" \
http://download.oracle.com/otn-pub/java/jdk/8u161-b12/2f38c3b165be4555a1fa6e98c45e0808/jdk-8u161-linux-i586.tar.gz \
-O jdk-8-linux-x64.tar.gz


Enjoy!

Tuesday, November 28, 2017

Read a text file in only one line of Java code

Since Java 7, you can do this with this wonderfull line :

new String(Files.readAllBytes(Paths.get(this.getClass().getResource("/file.txt").toURI())))

That makes Java powerfull and I love that.

Monday, October 30, 2017

Oracle 12c column alias length limit workaround

Oracle, in older version, has some limitations on column alias name length. You cannot exceed 30 characters. On my project, I use Hibernate to access database and everythings work when Hibernate plays in SQL. But, my colleague who tried to grab some data from a PL/SQL script had the following error :

ORA-00972 : Identifier is too long

Humm...  F***! So, three solutions to solve this issue :

  • ask your DBA to migrate to Oracle 12cR2  which allows column alias names up to 128 characters
  • alter your database and change your application to fit this new shorter name
  • find a working workaround !!!!

Here is this workaround : the idea is to make a virtual column which contains the same data but with a shorter alias name.  Example : 

alter table MYTABLE add NEW_COL date generated always AS (MY_COL_WITH_VERY_LONG_NAME - 0) virtual;

The value in the virtual column must be calculated. The trick is to make a dummy operation (like value-0). 

Whooo... that's cool. So cool that I wanted to make other operation. I added a generated column which returns the delta in days between to dates : 

alter table MYTABLE add DELTA int generated always AS (START_DATE - END_DATE) virtual;

I love it!

Wednesday, July 05, 2017

Kill Linux process that consumes all memory

Found on SatckOverFlow. I love it :

ps -eo pid --no-headers --sort=-%mem | head -1 | xargs kill -9
With:
  • ps -eo pid --no-headers --sort=-%mem: display the process ids of all running processes, sorted by memory usage
  • head -1: only keep the first line (process using the most memory)
  • xargs kill -9: kill the process

Wednesday, April 19, 2017

Install VMware Horizon Client (VMware View) on Ubuntu



If like me, you had issues with VMware horizon client package on your Ubuntu desktop, here are my tips.

Step 1 : download the installation package

https://my.vmware.com/en/web/vmware/info/slug/desktop_end_user_computing/vmware_horizon_clients/4_0#linux

Step 2 : make it executable and run it

chmod +x VMware-Horizon-Client-*******.x64.bundle
sudo ./VMware-Horizon-Client-*******.x64.bundle

This will install files but it's not runnable because you need some libs.

Step 3 : find and fix libs not found

To view needed libs, run : ldd /usr/lib/vmware/view/bin/vmware-view

Results is something like :
linux-vdso.so.1 =>  (0x00007ffdb45e6000)
libatk-1.0.so.0 => /usr/lib/x86_64-linux-gnu/libatk-1.0.so.0 (0x00007fcb631a6000)
libgdk_pixbuf-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgdk_pixbuf-2.0.so.0 (0x00007fcb62f84000)
libgtk-x11-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgtk-x11-2.0.so.0 (0x00007fcb62938000)
libgdk-x11-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgdk-x11-2.0.so.0 (0x00007fcb62683000)
libgio-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgio-2.0.so.0 (0x00007fcb622ee000)
libgthread-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgthread-2.0.so.0 (0x00007fcb620ea000)
libpixman-1.so.0 => /usr/lib/x86_64-linux-gnu/libpixman-1.so.0 (0x00007fcb61e42000)
libpng12.so.0 => /lib/x86_64-linux-gnu/libpng12.so.0 (0x00007fcb61c1d000)
libXss.so.1 => /usr/lib/x86_64-linux-gnu/libXss.so.1 (0x00007fcb61a19000)
libudev.so.0 => /lib/x86_64-linux-gnu/libudev.so.0 (0x00007fcb619f8000)
libglib-2.0.so.0 => /lib/x86_64-linux-gnu/libglib-2.0.so.0 (0x00007fcb616e5000)
libgobject-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgobject-2.0.so.0 (0x00007fcb61490000)


For some not found libs, you should fix this with a symbolic link such as :

sudo ln -s /lib/x86_64-linux-gnu/libudev.so.1.6.5 /lib/x86_64-linux-gnu/libudev.so.0
sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /lib/x86_64-linux-gnu/libcrypto.so.1.0.2
sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /lib/x86_64-linux-gnu/libcrypto.so.1.0.2

For libpng12.so.0, I fixed it by installing an extra package :

wget http://mirrors.kernel.org/ubuntu/pool/main/libp/libpng/libpng12-0_1.2.54-1ubuntu1_amd64.deb && sudo dpkg --install libpng12-0_1.2.54-1ubuntu1_amd64.deb 

Then, you should run and enjoy vmware-view :)



Monday, January 23, 2017

Declare secured connector on Tomcat for https connections

To access your tomcat threw https, you have to declare a secured connector. There are two parts to do that :


  1. modify your server.xml with the new connector configuration
  2. generate a java keystore the connector will refer to

Step 1 : Modify your serveur.xml like this


<Connector
           protocol="org.apache.coyote.http11.Http11NioProtocol"
           port="${ssl.port}" maxThreads="200"
           scheme="https" secure="true" SSLEnabled="true"
           keystoreFile="${java.home}/lib/security/tomcat_java.keystore" keystorePass="changeit"
           clientAuth="false" sslProtocol="TLSv1.1" ciphers="TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256,
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,
TLS_ECDHE_RSA_WITH_RC4_128_SHA,
TLS_RSA_WITH_AES_128_CBC_SHA256,
TLS_RSA_WITH_AES_128_CBC_SHA,
TLS_RSA_WITH_AES_256_CBC_SHA256,
TLS_RSA_WITH_AES_256_CBC_SHA,
SSL_RSA_WITH_RC4_128_SHA" />

Step 2 : generate the keystore which contains the certificate used to secure connections

To generate your keystore, you need openssl and keytool (%JAVA_HOME%/jre/bin/keytool). 

If you use an existing certificate in PEM format (cer or pem files) , you will need to convert it to PKCS#12 format (p12 file). To acheive that, you need :
  • your private key which was used to generate the certificate
  • your certificate
  • the root certificate form your Certificate Authority (Verisign, GoDaddy, Symantec, etc...)

Convert it with :

openssl pkcs12 -export -in [your_certificate].cer -inkey [your private key].key -out result-certificate.p12 -name tomcat -CAfile [Veridign certificate].cer -caname root

Then generate your keystore (NOTE -->  'tomcat' alias is important) :

keytool -importkeystore -deststorepass changeit -destkeypass changeit -destkeystore tomcat_java.keystore -srckeystore result-certificate.p12 -srcstoretype PKCS12 -srcstorepass changeit -alias tomcat

Restart Tomcat and check logs to see if connector is started.


PS : many thanks to John Willis. His post (http://www.johnwillis.com/2015/07/tomcat-errsslversionorciphermismatch.html) really helped me.


Acces Apache Tomcat on port 80 or 443 from Linux

Default Linux security rules don't allow to access network ports < 1024 to non rooted processes. A bad idea would be to start your Tomcat as ROOT. Very bad idea!!! But these's another solution. You can start your Tomcat with a port > 1024 and then redirect requests from standard ports (such as HTTP/80  or HTPS/443) to ports > 1024.

Let's imagine you bound Tomcat https connector to 8301. We'll add a rule to iptables like this :

Prequisites : switch to root (su -)

iptables -A PREROUTING -t nat -i eth1 -p tcp --dport 443 -j REDIRECT --to-port 8301
/etc/init.d/iptables save
/etc/init.d/iptables restart

Then, just check if rule is active with iptables -L

target     prot opt source               destination         
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:https 

Now, you can access your tomcat threw a classic https url :)

Avoid select min/max with Oracle

I hate select max clauses with Oracle. My company used to write subqueries which such expressions. It's slow and unreadable. Conditions ...