1

I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question:

I have 2 tables one file level & another batch level. In a day, only 1 file_id is possible; but for each file_id there are multiple batches with different batch_id and then again for each batch_id there are multiple documents.

Now, I need something like the simple getters & setters. I want some data from the file level table.. on the basis of that get some data from the batch level table and then get some more data from the files table again.

This was very easy if I had an object & I could simply pass it around & setting stuff & filling up the object with things I want.

How can I do something similar with PL/SQL like passing the same object again & again ??

Improve this question
4
  • 2
    It sounds like your data can be gathered using pure SQL. You should prefer a set based (SQL) approach over row based getters and setters whenever possible.
    – Leigh Riffel
    Commented Jul 13, 2012 at 18:41
  • @LeighRiffel can you please elaborate a bit? i didn't get you.
    – Rahul Thakur
    Commented Jul 14, 2012 at 8:59
  • How are you calling the PL/SQL from the client application/API and what do you need to pass to/from? I get the impression you're used to using an ORM like hibernate.
    – Philᵀᴹ
    Commented Jul 15, 2012 at 0:56
  • @Phil The PL/SQL will be called from Oracle BI Publisher where the data source is configured. It will execute the script and publish the result in a pre-defined template. There are 5 filters in total batch_id, brand_id, status, from_date & end_date which gets passed from a screen to BIP where the parameters are also configured. Basically these parameters are for filtering the result according to the needs of the end user.
    – Rahul Thakur
    Commented Jul 16, 2012 at 6:19

1 Answer 1

Reset to default
2

Getters and Setters implies retrieving the data for one row and changing the data for one row. It sounds like you are planning to call a get procedure for each row you want in the file level table and then for each of those rows loop through getting the corresponding data from the batch level table etc.

My suggestion is that you write one SQL statement that selects all the data you need from the file level table joining in the data you need from the batch level table.

I don't know anything about BI Publisher, so based on your comment, none of this may apply. Perhaps someone familiar with BIP can say whether these parameters could be bound into the query to allow the query to determine what data should be returned. A PL/SQL method may be required for this application, but it needn't be more than a wrapper for your SQL.

Improve this answer
4
  • yeah, you are almost right, my batch level table may contain millions of records say 6months after release, so I don't want to put a huge overhead, what the only thing I think will reduce this is if I query the file level table first, get the file_id's associated with the dates, then query the batch_level table with those file_id's to get a smaller result set. But the problem is, for different combination of filters, i have to do it differently & in one case I don't need to query the file table at all. I'm ending up writing too many queries, i don't feel it's elegant enough.
    – Rahul Thakur
    Commented Jul 17, 2012 at 6:41
  • I agree that you wouldn't want to pull all the batch level data and filter it client side, but you should also avoid doing the join on the client side at all. The database can do the joins far better than client side code. You are paying for the Oracle database engine, so use it. Filters can sometimes be combined by using conditional WHERE clauses and this could even account for when the file table isn't needed at all. If this isn't possible, multiple queries would be a business requirement and does not necessarily make the solution inelegant.
    – Leigh Riffel
    Commented Jul 17, 2012 at 12:15
  • What bothers me in having multiple queries is that I will be hitting the db multiple times and that might be a performance issue again?
    – Rahul Thakur
    Commented Jul 17, 2012 at 16:58
  • I'm not sure I understand what you are doing then. The application should gather all the data it needs for a particular set of parameters using one query. The query will filter data on the server so that the smallest subset possible given the business requirements is transferred. Yes, a second query execution will be needed when the parameters are changed and some of the same data may be retrieved. I suppose you will have to determine the normal access path and whether you want to cache all the records up front or request data on demand like this. For most cases I would prefer the later.
    – Leigh Riffel
    Commented Jul 17, 2012 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.