File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes SQL from a particular business scenario Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "SQL from a particular business scenario" Watch "SQL from a particular business scenario" New topic

SQL from a particular business scenario

Rajkumar Katudia
Ranch Hand

Joined: Jul 28, 2009
Posts: 51

Lets say I have an application for school administration.

Lets say it has the following business scenarios:
- Admit Student
- Transfer student
- Check grades
- Maintain syllabus
an so on...

Lets look at the following business scenario "Transfer student"

Lets say it accepts 3 parameters student_id, from_school, to_school

In the backend it will execute few SQL statements and do the required business change.

Let's say it run following SQLs:
SQL1 An SQL to update history or audit table,
SQL2 An SQL to add student to "to_school"
SQL3 An SQL to remove from "from_school"
SQL4 An SQL to update syllabus table
and so on...

Now, my question is how can we get the SQLs (SQL1 through SQLn) executed as part of a particular business scenario?

I need the SQLs for performance testing of my app.
William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

Begin Transaction



Rajkumar Katudia
Ranch Hand

Joined: Jul 28, 2009
Posts: 51
William P O'Sullivan wrote:Begin Transaction




Oh I am sorry, I was not clear earlier.

Let me re-phrase.

I use the app's UI to perform Transfer Student.

The app executes the SQLs as mentioned in my OP.

My question is is there a query or some way to get the SQL's.

Something like a log table in oracle server where all the queries that were executed is stored.

What I want to do is this.

Run the business secnario through the app once.

Get the SQLs from oracle server and execute it "n" number of times to create n records.

I do not have the SQLs. It is generated by the app and executed on the DB. So, is there a way to get the querries from oracle DB?
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

I'd say Database Replay seems fit your needs well. It looks quite complicated however (I've just googled it up and never actually heard of it much before). Similar tool is the Log Miner, though again, I've no experiences with it.

You might also use SQL Trace facility, that will produce trace logs, from whose individual SQL commands could be recovered. You'll have hard time putting in bind values, though.

I've implemented a special PreparedStatement/CallableStatement wrappers in my project, inspired by the DebuggableStatement. I've modified it to save the executed statements to a SQL script file instead of log. If you've got a centralized factory for creating statements in your code, setting it up is easy. I've spend only some three hours or so getting my version of DebuggableStatement to work, and took a few shortcuts in formatting the bind variables (such as no support for blobs/clobs and few others).

Edit: you might also want to check out P6Spy.
I agree. Here's the link:
subject: SQL from a particular business scenario
jQuery in Action, 3rd edition