sql - How to use a common cursor in PLSQL script for multiple tables in same definition -

there 4 table in db a, b, c , d. tables has exact same structure. need use same set of procedures process table , generate single file

several utility procedures available in design need pass row parameter procedure.

what best way implement plsql script purpose ?

please provide high level overview?

note: basic problem use common cursor , common rowtype this.

oracle native dynamic sql it's easy way query table using fixed set of parameters , result variables (including records):

execute immediate 'select ... ' || table_name || ' ....'      using in_par_1, in_par2, out out_par_1, ...;  open <cursor> 'select ... ' || table_name || ' ....'      using ...; loop     fetch <cursor> ...;     exit when <cursor>%notfound;     ... end loop 

(and others ways dynamic data exchange provided).

the biggest nds problem lack of compile-time error checks, might not easy support , there risk break , don't notice that.

in case 1 trick can used:

1. create view aggregates data , marks data source
2. create 1 procedures set depends on common data structure (the view) , data source id (procedure's parameter).

the sample listing:

create or replace view view_abcd     select 'a' data_type, a.*     union     select 'b', b.* b     union     select 'c', c.* c     union     select 'd', d.* d;  ... procedure/function process_data( datatype ) ... select .... view_abcd v v.data_type = datatype , ...