Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Help: Pass the parameter into pl/sql

Help: Pass the parameter into pl/sql

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Wed, 16 Jan 2002 14:56:01 GMT
Message-ID: <l.1011192962.1755676269@[64.94.198.252]>


Sorry for my previous postings, that I omit some codes because i thought that is not important.

I need to write a pl/sql which I can dynamically change the schema name  or table name in the cursor, so I can first get a list of table_names  and then call the procedure from perl or cron, the schema or table name is different every time I call, and there are several hundreds of tables, so it is impossible from me to hard coded the table name into the pl/sql. To make it easy to understand, let's take different table_name as example, this is not exactly what I wanted, but if somebody can help me solve this problem, I will know how to solve my  problem at our site.

SQL> create table test ( id number);
Table created.
SQL> create table test1(id number);
Table created.
SQL> create table table_final(table_name varchar2(10),id number); Table created.

Now I need a storaged procedure to move the data from test and test1  into archive table table_final, but with a table_name column.

SQL> 1 create or replace procedure test_procedure

  2                     (TNAME in varchar2)
  3      as
  4                      error test%ROWTYPE;
  5                 loop_counter NUMBER(12) := 0;
  6                      CURSOR error_log is
  7                      select * from TNAME;
  8              BEGIN
  9                      FOR error IN error_log LOOP
 10                             loop_counter := loop_counter + 1;
 11                             insert into table_final
 12                             (table_name,
 13                            ID)
 14                             values
 15                            ( TNAME,
 16                       error.id );
 17                    if loop_counter > 200 then
 18                            loop_counter := 0;
 19                            commit;
 20                   end if;
 21                    END LOOP;
 22*          END;

SQL> /
Warning: Procedure created with compilation errors. SQL> show error
Errors for PROCEDURE TEST_PROCEDURE:
LINE/COL ERROR
-------- ---------------------------------------------------------------
--
7/21     PL/SQL: SQL Statement ignored
7/35     PLS-00201: identifier 'TNAME' must be declared
11/28 PL/SQL: SQL Statement ignored
16/22 PLS-00364: loop index variable 'ERROR' use is invalid SQL> Could somebody help me out with this problem?

Thanks  

--

Sent by dbadba62 from hotmail piece of com This is a spam protected message. Please answer with reference header. Posted via http://www.usenet-replayer.com/cgi/content/new Received on Wed Jan 16 2002 - 08:56:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US