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

session_privs and session_roles

So my friend was getting 'SYS.DBA_IND_COLUMNS table does not exit' while he was trying to run

desc dba_ind_coulmns

I was able to run the same query. So of course we are in the territory of authorizations. But how do I prove it?

Then can session_privs and session_roles into picture. For me the result was

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
SELECT ANY TABLE
SELECT ANY SEQUENCE

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
HS_ADMIN_SELECT_ROLE
HS_ADMIN_EXECUTE_ROLE
ADHOC

6 rows selected.

His user has more privilege but less roles.

For him the role was ADHOC_DML and no admin table roles and hence it was proved why he got 'table does not exist' error.

Another interesting table to know is v$session.

select SID,MACHINE,USERNAME from V$SESSION;

Git commands

> Log commits for a user since. Print in one line and with abbreviated commit

git log --committer=appandey --no-merges --since=2014-04-24 --pretty=oneline --abbrev-commit

> add all java files

git add \*.java

> Commit on someone else's behalf (same team)

git commit --author='author' -m '..'

fatal: No existing author found with 'author'

git commit --author='author <author@company.com>' -m '..'

e.g. git commit --author="amodpandey <amodpandey@gmail.com>" -m '...'

Did you know java TimeUnit class

I am not sure why Date din't do it, Calendar didn't do it and Apache DateUtils didn't do it?

Though every other product in every other company would need date diff !!!

I was introduced to TimeUnit through a retry wrapper class where I had to specify retry time using TimeUnit. So no longer we need a variable like private static final long numberOfSecondsInMinutes  = 60; :)

Date diff is a two liner with this class

1. Get the diff in millis
long diff = date1.getTime() - date2.getTime();

2. TimeUnit.MILLISECONDS.toDays(diff);

nJoy

Http Accept - 406

There are many HTTP headers which go unnoticed. Accept is one such header.

Accept is a way to tell the server what the client accepts. A server can smartly handle this.

e.g. Accept: application/xml to a service returns xml and Accept: application/json to same end point should return json.

Sometimes we have the server side code as to strictly say we support requests with Accept: application/json. I have seen ROR doing it when the return is of type JSON.

If the Web server detects that the data it wants to return is not acceptable to the client, it returns a header containing the 406 error code. 

I have observed the below behavior with our ROR set up

Success

Accept: application/xml,application/json
Accept: application/json,application/xml"
Accept: application/xml;q=0.9,application/json
Accept: */*

Failure (406)

Accept: */*, application/xml;
Accept: application/xml,*/*;
Accept: application/xml;q=0.9,*/*;
Accept: application/xml;

Note: I see */* is successful if Accept has only one entry with */*, else it expects application/json somewhere.

Chrome browser by default sends (may depend on browser and version)

1. Accept:
   text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8

So again it will fail. There are browser add ons which can help manipulate any of the HTTP headers for calls made through the browser. I have to use that for this call.

HTH someone, someday