Solved

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

Posted on 2003-10-24
10
703 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

749 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