Coder's Block

Imagine the following table. First column (ID) is the Primary Key. There is a reflexive relationship between the 1st column and the 3rd (notice the colouring of the letters). Basically, Parent_ID mentions the parent of a particular ID in a hierarchical fashion representing a folder tree. So assuming this is in Oracle DB, how can we get the file path?

There’s a neat little concept in Oracle to do this called CONNECT_BY. Lets look at the answer first,

SELECT path
  FROM
 (SELECT ID                              ,
   PARENT_ID                             ,
   FILE_NAME                             ,
   SYS_CONNECT_BY_PATH(FILE_NAME, '/')                             AS path,
   RANK() OVER (ORDER BY SYS_CONNECT_BY_PATH(FILE_NAME, '/') DESC) AS rank
    FROM files
   WHERE id=? CONNECT BY PRIOR id = parent_id
 )
 WHERE rank=1;

The output for the above table is as follows,

Now the explanation,

• CONNECT BY PRIOR Operator – Made to use in such reflexive hierarchical situations. What it does is, it identifies this relationship and arranges records based on that arranges the records.
• SYS_CONNECT_BY_PATH(FILE_NAME, ‘/’) – Uses the CONNECT BY feature and then uses column FILE_NAME to make a relationship between the child records and the parent records.
• RANK() OVER (ORDER BY LENGTH([<path>] DESC)) - This is a bit of a tricky one.
• First of all, this orders the paths in a descending order of lengths of paths. The reason to do this is that is ex: for ID-4, it shows all paths: /Root/, /Root/First level Folder 1/ and /Root/First level Folder 1/Second level Folder, so we need to find the longest path.
• Secondly, we use the uses the RANK() method to rank the paths on their length in the descending way to find the longest path. As a result, the longest path would have the rank 1. In the outer query, the WHERE clause makes sure that unwanted paths are not shown to us

If more explanation is needed, please leave a comment and I shall get back to you

Well.. what I do code here is too simple for this blog but I’m pretty proud of my usage of generics in this.

Logic is, check for a value in the map… If it is there, get it. Otherwise, get a default.

Thats what this method do,

What if the Map’s values are Objects? Then I’d have to cast it to whatever type as well, right? Thats what this other method do

via http://www.cyberciti.biz/faq/how-do-i-start-oracle-service-in-unix/

Consider a tree structure made up of Nodes and their specialized subclasses. Each Node has children which could be subclasses of Node.

We’re going to traverse this tree structure without using recursion but only using 2 Stacks.

Here goes the code:

I think the code is self explanatory.. Leave a comment if you bump into problems/improvements

Problem:

Here’s the code

@Transactional
void outer(){
   inner1();    // @Transactional
   inner2();    // @Transactional
}

Both inner methods are transactional. Now, what I wanted to happen was, when outer() is called and if inner2() is rolled back, inner1() too should roll back.

Solution:

Use the NESTED option for the @Transactional annotation. As in the documentation, this is what happens when you do that,

Execute within a nested transaction if a current transaction exists, behave like PROPAGATION_REQUIRED else.

And PROPAGATION_REQUIRED or REQUIRED option  means,

Support a current transaction, create a new one if none exists.

Based on that the code changes as follows,

@Transactional(propagation = Propagation.NESTED)
void outer(){
   inner1();    // @Transactional
   inner2();    // @Transactional
}

You can read about the other options in Spring Doc. I found another decent explanation here.

Since I couldn’t find a good list of extensions to CCK, I thought of making one

• Views – Something you might even find useful than CCK. Enables you to make custom pages and blocks etc based on nodes and load of other attributes
• Date – Enable date fields, including Date Picker controls
• Link – Links a node to another node or any page via a hyperlink
• FileField – Enables file upload for CCK
• Modal Nodereference – When filling forms to create a new node, instead of only picking the existing node references create new nodes
• Computed Field – Lets you add a custom “computed fields” to your content types which can be populated with values that you define ONLY via PHP code.

I am PRETTY sure I forgot some important ones. Please suggest those in the comments area. Thank you!!!

Problem: I had an SP to call which takes a param Date. I called this via JDBC with a valid Java Date, but it refused to save the time fields I supplied.

When preparing the SP for calling I did as follows,

mightySp('paramName',:paramName);

When supplying the parameter, I gave as follows,

tempPlsqlBlock.declareParameter(new SqlParameter("paramName", Types.DATE));

Solution: Instead of calling with param type Date, call with String and convert to date in the Java layer.

When preparing the SP for calling I used the TO_DATE method and convert the String to an Oracle Date as follows,

mightySp('paramName',TO_DATE(:paramName,'yyyy/mm/dd:hh:mi:ssam')

When supplying the parameter, I gave as follows,

tempPlsqlBlock.declareParameter(new SqlParameter("paramName", Types.VARCHAR));

The sessions in Oracle is shown in a system view called v$session. It has a quite a bit of columns, so my team mate Gowtham lists the most significant ones:

SELECT USERNAME, OSUSER, STATUS, MACHINE, PROGRAM, SERVER, TYPE, ACTION, CLIENT_INFO, LOGON_TIME, COMMAND, EVENT

FROM v$session

where USERNAME is not null

order by machine;

Just to summarise the list of all the events,

SELECT count(*), event FROM v$session s GROUP by event;

We can even filter them to find the locks and group them by name as follows,

SELECT osuser, event, count(*)

FROM v$session s

where event like 'enq:%'

GROUP by event,osuser

ORDER BY osuser;

A problem with Oracle DB that I see is that the ORDER BY clause is case sensitive. I came across this problem and I found the solution here in a post by Natalka Roshak. Solution is something like the following,

SELECT id,name FROM awesomeness WHERE order by nlssort(name,'NLS_SORT=BINARY_CI');

Here, she also mentions that this is valid for Oracle versions >= 10gR2.

In their documentation Oracle. says that it works by converting the string equivalent datatype  to RAW format, which is then sorted and displayed.
They show interesting ways to use this function.

Anyhow, while I was sharing this with my team, one of my team mates, Gowtham, pointed out that this can be done by a more intuitive way: Simply convert everything to uppercase!

SELECT id,name FROM awesomeness WHERE order by UPPER(name);

Actually, the cause for this problem is when I wanted to pass a Nested Table to an Oracle Stored Procedure and figuring out I can’t except using Oracle specific classes from my Java code.

I found this solution by William Robertson with a quite a bit of searching and apparently, it only works for Oracle 9i or above. This works for me:

SELECT EXTRACTVALUE(xt.column_value,'e')
FROM TABLE(XMLSEQUENCE
     ( EXTRACT
          ( XMLTYPE('<coll><e>' ||
              REPLACE('10.01.03.04.234','.','</e><e>') ||
              '</e></coll>')
     , '/coll/e') )) xt;