Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Help: Pass the parameter into pl/sql
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;
7/21 PL/SQL: SQL Statement ignored 7/35 PLS-00201: identifier 'TNAME' must be declared11/28 PL/SQL: SQL Statement ignored
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