Help:pass the parameter to the pl/sql

We have several schema who has the tables with the same table definition and name,say table1 (with col1,col2,col3....) now we need to archive them to a data warehouse into one big table, the table has the same definition, but with one more column,"schema_name". So I am tring  to create a stored procedure, which will loop the the same table in different schemas.

What I am trying to do is:

create or replace procedure error_log_archive

                (SCHEMA in varchar2)
as
                sensor_rec SCHEMA.table1%ROWTYPE;
                 CURSOR A is
                select s.* from SCHEMA.table1 s
                where error_time < sysdate - 14;
Begin
              for loop
                     insert into big_table
                     (sensor_rec.col1,
                      sensor_rec.col2,
                      sensor_rec.col3,


                      .....

                      schema_name)
              values (sensor_rec.col1,
                      sensor_rec.col2,
                      sensor_rec.col3,


                      .....

                      SCHEMA)
              end loop;

but oracle does not allow me to have variables in the declare section, could somebody help me out?

Thanks  

-- 
Sent by dbadba62 from hotmail subpart  from  com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new