Solved

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

Posted on 2003-10-24
10
695 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
advertisement module in core php 4 160
JavaScript/Java - Changing an image background color 4 66
throw exception 21 44
jboss wildfly 10.1 10 87
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

770 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