Re: Stored Procedure

rkrishag_at_ikon.com wrote:
>
> Hi,
>
> Can any one help me write a stored procedure. The requirement is like
> this. I have a number of load tables. Records are continuosly loaded into
> this using sqlloader. Each upload has a unique batch id. There is an
> audit table which monitors the number of records being loaded everyday. A
> field of audit table contains the name of the table on which the upload
> was performed. For each upload a record is inserted in the audit table
> which now has the batch id and the table name on which the upload was
> done.
>
> I want to write a query for which I pass the load table name and batch id
> as the parameter. The query should access the corresponding load table
> and according to the batch id determine how many records have been
> loaded. Since there are more than 20 load tables how do I write a stored
> procedure or a trigger.
>
> Please do get back to me on this ASAP.
>
> Regards
> Rajesh
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Hi Rajesh,

try something like this:

set termout off
set echo off
set pagesize 0
spool c:\c_rec1.sql
select 'SELECT ' TABLE_NAME || 'table, ' || BATCH_ID ||' id, COUNT(<pk-field>) FROM ' || TABLE_NAME || ' WHERE BATCH_ID = ' || BATCH_ID ||'; ' from <audit_table>;
spool off
spool c:\c_rec2.sql
@c:\c_rec1.sql
spool off

-- 
Regards

Matthias Gresz    :-)