Re: Using the copy command in PL/SQL

In article <19961215095301.EAA25633_at_ladder01.news.aol.com>, zelshoop_at_aol.com says...
>
>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

I don't believe you will ever get this to work. Copy is a SQL*Plus command. It only works in SQL*Plus. It is not an SQL command

-- 
Tony Noble
tnoble_at_mt.gov
Opinions expressed do not necessarily reflect those of my employer.