Hi Folks,
I am struggling with a rather wierd issue. The DBA of my application has exposed some function based view(not exactly sure what that means) which returns results like any query over a table. I am having a tough time consuming it in my
java application. The name of the view is - `networknode_api.get_ftapp_networknode_hrchy` The SQL for it is(syntax might be a bit strange as there is actual
word `table` in the query)
The query returns a hierarchical result data, provided below is a sample output (ignore the ouput if the formatting is too werid)
Parent_NODE | Child_NODE | NODE_DISPLAY_NAME | BEGIN_DATE | END_COB_DATE | LVL
null | CCN | Connent Node | 01/20/2013 | 10/19/2013 | 1
CCN | AVT | Avg Vol Turn | 01/20/2013 | 10/19/2013 | 2
AVT | L:2 | L:2 | 01/20/2013 | 10/19/2013 | 3
AVT | L:6 | L:6 | 01/20/2013 | 10/19/2013 | 3
CCN | NUT | Nap Up Turn | 01/20/2013 | 10/19/2013 | 2
.
.
.
I have the following method in my DAO -
Exception is thrown at line - stmt.execute(....)
java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier
I debugged through the code and noted the driver and connection details that the connection object has embedded. Using that I wrote a sample java
jdbc code-
The above code works perfectly fine. Also tried the above code using normal Oracle JDBC driver and too works fine. I am baffled; what goes wrong when using the connection from a DataSource object. Does the driver treat SQL statements differently based on how the connection object is obtained? Appreciate your inputs/comments for same.
The oracle db version is - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Application Server - Weblogic 10.3