Thursday, June 12, 2014

Oracle Database - reading a CLOB through sqlplus

If you execute  through sqlplus 'select <clob column>' you will get truncated data. To see the complete data in sqlplus

set buffer <X>

select DBMS_LOB.substr(column, <buffer size>) from table

Note: This is required for sqlplus. JDBC and hibernate handle it directly so this 'select column' should be good enough.

Wednesday, June 11, 2014

SQL equal or not equal and Mr NULL

I created a table with a column


and I added this clause to my query if the user should not see the confidential data


Now the user could not see the confidential records. All good!

But he could not see few other records which were not confidential ?? I checked the table and found records with IS_CONFIDENTIAL values 'Y','N' and NULL and the records with NULL value didn't show up !!

Oracle Database 11g Enterprise Edition Release - 64bit Production

IS_CONFIDENTIAL <> 'Y' does not mean it to return rows with NULL value !!

This is what I did (though there could be multiple solutions and may be better :))


Or I should have changed my query to AND ( IS_CONFIDENTIAL = 'N' OR IS_CONFIDENTIAL IS NULL)

Note: I agree that positive check is always better. But I thought what if there are more values than Y and N, IS_CONFIDENTIAL<> 'Y' was the choice for me.

Bulb: Did you know you can enable hibernate to print the queries it executes! It is very helpful.


curl command to error out on HTTP error codes

$ curl -I -f ""
HTTP/1.1 200 OK
Date: Tue, 10 Jun 2014 20:01:02 GMT
Server: Apache

Ah! but it does not provide the / at the tail. We get a 404.

$ curl -I ""
HTTP/1.1 404 Not Found
Date: Tue, 10 Jun 2014 20:01:15 GMT
Server: Apache

echo $? will return 0 in both the case.

What if we are using it inside a script and want the process to fail on non success HTTP status?

Use –f option

$ curl -I -f ""
curl: (22) The requested URL returned error: 404

$ echo $?

The man says:

       -f, --fail
              (HTTP)  Fail  silently (no output at all) on server errors. This is mostly done to better enable scripts etc to better deal with failed attempts. In
              normal cases when a HTTP server fails to deliver a document, it returns an HTML document stating so (which often also describes why and more).  This
              flag will prevent curl from outputting that and return error 22.

              This  method  is  not  fail-safe  and  there  are occasions where non-successful response codes will slip through, especially when authentication is
              involved (response codes 401 and 407).

Note: -I is better option to user than --request HEAD, as --request HEAD request will hang for a while :)