• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 734
  • Last Modified:

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

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
rtirbany
Asked:
rtirbany
2 Solutions
 
CEHJCommented:
Forgive me for being a bit suspicious about decode - but what are you going to do with it? ;-)
0
 
savalouCommented:
It may work better if you used stored procedures and called them with callableStatements.
0
 
rtirbanyAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CEHJCommented:
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
 
objectsCommented:
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
 
CEHJCommented:
Don't agree with the recommendation - my comments are not worthless
0
 
objectsCommented:
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
 
CEHJCommented:
I certainly don't mind savalou getting points too
0
 
CEHJCommented:
8-)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now