Solved

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

Posted on 2003-10-24
10
691 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JUnit 4 @Before and @BeforeClass differences 3 48
Arrays.asList  VS  ArrayList 4 45
Windows 10 IE Certificate Issue 10 42
hibernate insert example 13 15
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This video teaches viewers about errors in exception handling.

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now