Solved

calling an oracle functions (ie decode and lpad) from java

Posted on 2003-10-24
10
699 Views
Last Modified: 2011-09-20
Hi,
  I'm trying to incorporate an oracle 'DECODE' function in one of my querystrings, and an oracle 'LPAD' function in another.  If heard this would help performance...When using a regular statement, I get an 'invalid column' error (and yes, I've checked a gazillion times).  I'm new to jdbc/java.sql...Should I be using something else/perhaps a callable statement in code?  Is this the best thing to do for performance or should I do the decode/lpad work in java?
Please advise.
0
Comment
Question by:rtirbany
10 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 9619167
Forgive me for being a bit suspicious about decode - but what are you going to do with it? ;-)
0
 
LVL 3

Assisted Solution

by:savalou
savalou earned 35 total points
ID: 9619317
It may work better if you used stored procedures and called them with callableStatements.
0
 

Author Comment

by:rtirbany
ID: 9620174
Thanks for the responses guys...
I'm trying to increase performace and was under the impression that using oracle functions would be preferred over additional java code.
Isn't this true?  If so, would a stored proc be better than using both the oracle functions & ad-hoc queries?   My concern w/ this is that my WHERE clause is adjusted dynamically by what the user enters as search criteria.

DECODE is used b/c I am expecting some null values returned, and I don't want them appearing on the screen as 'null'.  Should I have the jsp page making the adjustment instead?

LPAD is used b/c I'm expecting a field called 'indent level' to be returned and I'd like to use it to manage my presentation.  The querystring looks like:
   select ...,lpad(' ',4*(indent_level),...

any advice would be much appreciated..
thanks
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 86

Accepted Solution

by:
CEHJ earned 35 total points
ID: 9620562
Of course, stored procs should improve performance generally. I would eschew decode and lpad (particularly the former) for the following reasons:

a. it makes the db code non-generic
b. it makes the code opaque and hard to maintain (I certainly wouldn't like to be on the receiving end of a decode function as long as your arm ;-))

You're better pushing the logic further towards the front end, or at least as far as makes the code generic and RDBMS-portable.
0
 
LVL 92

Expert Comment

by:objects
ID: 10224819
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Delete

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

objects
EE Cleanup Volunteer
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10225984
Don't agree with the recommendation - my comments are not worthless
0
 
LVL 92

Expert Comment

by:objects
ID: 10229421
Considered a split, but didn't feel there was a guarantee that the comments would have led to a solution. But if its that important to you.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Split between savalou and CEHJ

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

objects
EE Cleanup Volunteer
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10229578
I certainly don't mind savalou getting points too
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10285127
8-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Arrays.asList  VS  ArrayList 4 71
table example 4 32
Error in @AspectJ Based AOP with Spring 2 18
login form jsp example 2 37
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question