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 ??
Best Answer
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.