Using the copy command in PL/SQL

From: ZelShoop <zelshoop_at_aol.com>
Date: 1996/12/15
Message-ID: <19961215095301.EAA25633@ladder01.news.aol.com>#1/1

Hi,

I'm having a problem trying to use the copy command within PL/SQL. I have a list of existing tables from which I want to copy their data into another set of tables. There are probably better way to do this, but I'm trying to make use of the dynamic PL/SQL capabilities, but I'm having problems creating the copy command in such a way that it will be recognized by Oracle. I have created a procedure which receives in a sql statement create by another procedure. In the second procedure get the names of the table through a cursor, and while there exist tablenames, I loop, creating the copy command statement and calling the procedure which is to do the copy.

Both procedures create without problems, but the second procedure will not recognize the copy statement. Can anyone help?

Here's a copy of what I'm doing

create or replace procedure execute_copy (stmt in varchar2) is v_cursor_table := integer;
v_num_rows := integer;

begin

v_cursor_table := dbms_sql.open_cursor;
dbms_sql.parse (v_cursor_table, stmt, dbms_sql.v7); v_num_rows := dbms_sql.execute(v_cursor_table); end;

create or replace procedure initiate_execute_copy IS

v_tablename     varchar2(35);
v_stmt          varchar2(500);
CURSOR c_tablenames IS
        SELECT table_name FROM user_tables;

begin
OPEN c_tablenames;
LOOP

        FETCH c_tablenames INTO v_tablename;
        v_stmt := 'copy from foo/bar_at_T:a-boo:A -' ||


                ' replace '||v_tablename||' -' ||

                ' using select * from '||v_tablename||';';

      execute_copy(v_stmt); 
        DBMS_OUTPUT.PUT_LINE(v_stmt);

EXIT WHEN c_tablenames%NOTFOUND;
END LOOP;
CLOSE c_tablenames;
END; If I replace the v_stmt with something like 'drop table fratz', it works fine.

Thanks in advance,

Mike Received on Sun Dec 15 1996 - 00:00:00 CST