Re: Help:pass the parameter to the pl/sql

Nonsense.

Why don't you post the code with the variable declaration in the declare section, and the received Oracle error message number and message, so we can see what you are doing.

But you might also want to take a good look at your FOR loop. Where, exactly did you find syntax like that? SQL Server? This is not an appropriate place for a FOR loop in Oracle. Try this:

create or replace procedure error_log_archive (SCHEMA in varchar2) IS

   CURSOR a_cur is
   select * -- and why alias a single table reference?    from SCHEMA.table1
   where error_time < sysdate - 14;

   sensor_rec a_cur%ROWTYPE;

Begin

   OPEN a_cur;
   LOOP

      FETCH a_cur INTO sensor_rec;
      EXIT WHEN a_cur%NOTFOUND;

                    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;
      COMMIT;
      CLOSE a_cur;

EXCEPTION
   WHEN OTHERS THEN
      ....
END error_log_archive;
/

And if you are really intending to log errors seriously consider PRAGMA AUTONOMOUS_TRANSACTION.

Daniel Morgan

Mike F wrote:

> 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
Received on Tue Jan 15 2002 - 16:46:05 CST