JSP and Oracle

Posted on 2003-03-31
Medium Priority
Last Modified: 2010-04-01
I have a jsp application running on Tomcat with an Oracle backend.
The app creates many items called Cards that each display a unique collection of data culled from various different attributes of different tables in the Oracle database via a simple select sql string.
Some of these strings will be VERY long.
There is also the option of creating a new card with a new string that must be created and stored with the others. Each string will be identified by it's relevant card.
Does anyone have any idea where or how I should store and manage these strings?
Text files?
Stored procedures?
Question by:morleycallaghan
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
  • 2
  • 2

Expert Comment

ID: 8240354
Is there a problem with using a database table to store and maintain these strings? I don't know whether these strings are used as-is or if there are different parameters, etc.

If you store them in a table and design the application around it you can make the number of your cards dynamic, based on the strings stored in the table. Just add a new string to the DB table to add a new card. From the description the table would only have to be two columns, a card ID and the string.


Author Comment

ID: 8241353
I think that the strings would be too long to store in a database: I know they are too long for Access. But I could store the address or path of the text file. I wonder, though, if anyone has had success using some other method?

Expert Comment

ID: 8241568
I've previously used a system that pulled the SQL string from a text file. It allowed me to change the SQL string without having to change the programming.

In Oracle a varchar(2) column can hold up to 4,000 characters. For larger strings you can use LONG or CLOB which will easily hold up to 2 gigabytes.

Author Comment

ID: 8241759
Ahh I wasn't sure how big a varchar2 could be. I am doing the prelminary work with Access and Access sure can't manage it.
That makes more sense that trying to play with things like stored procedures which give me the heebie jeebies.

Accepted Solution

jwenting earned 60 total points
ID: 8325474
As an alternative you could try putting the SQL statements into an XML file and loading that into memory (indexed on something, maybe an index number or other identifier).

That's probably cheaper in CPU and memory load, at least if the set of queries is quite static.

I've implemented the above for disconnecting all database specific code from an application I did for a customer.
All SQL is in the XML and can be changed to for example take changes in syntax for another database engine into account.
That version does rely on column names remaining the same in the resultsets, but I have ideas to get that separated out as well.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

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