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.

SQL equal or not equal and Mr NULL

I created a table with a column

IS_CONFIDENTIAL CHAR(1)

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

AND IS_CONFIDENTIAL <> 'Y'

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 11.2.0.3.0 - 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 :))

IS_CONFIDENTIAL NOT NULL  CHAR(1)

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.

Bulb: CHAR vs VARCHAR

curl command to error out on HTTP error codes

$ curl -I -f "http://json.org/example"

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://json.org/example/"

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 "http://json.org/example/"

curl: (22) The requested URL returned error: 404

$ echo $?

22

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 :)