Oracle: unimplemented error due to execute immediate command

Question

I have given a task to create a procedure for copying data from the table and insert the copied data inside the same table and the change one of the column data. For example I have a table with with columns(ID, C1,C2....C20), the datas in column "ID" have same value and I want to change to another value after I copied it. I tried to use cursor and record since it returns a lot of data and the following is my procedure code:

  create or replace procedure copy_data(tab_name varchar2 ,column_to_change  varchar2, change_value varchar2, rowcount number)
is
  stmt varchar2(100);
  stmt2 varchar2(100);
  type test_cursor is REF CURSOR ;
  cur_cv test_cursor;
  v_test_rec job%rowtype;
begin
  stmt := 'v_test_rec.'||column_to_change;
  stmt2 := 'insert into ' || tab_name || ' values v_test_rec ';
  open cur_cv for 'select * from ' || tab_name;
  loop
  fetch cur_cv into v_test_rec;
  stmt := change_value;    
  execute immediate (stmt2);
  exit when cur_cv%rowcount > (rowcount - 1); 
  end loop;
  close cur_cv;
end;
/

tab_name is the table to copy data from, column_to_change is the column that I want to change the data inside, change_value is the value I want to change to from column_to_change, rowcount is the total count of the column.

when I compiled it, it was success, but when I execute it :

execute copy_data ('job', 'ccn_cd', 'ITUE02', 112);

I got error, and the error is following:

ORA-03001: 
ORA-06512: "HR.COPY_DATA", 行16
ORA-06512: 行1
03001. 00000 -  "unimplemented feature"
*Cause:    This feature is not implemented.
*Action:   None.

I am trying to understand which part of my code was wrong but still can't figure it out. I am sorry that my english isn't that good. Can anyone give me some hints or solutions to my problem? Thank you.

Answer 1

I think it must something like this one.

CREATE OR REPLACE PACKAGE ... AS

SUBTYPE test_rec_type IS job%rowtype;
PROCEDURE copy_data(tab_name varchar2 ,column_to_change  varchar2, change_value varchar2, rowcount number);
END;

CREATE OR REPLACE PACKAGE BODY ... AS

PROCEDURE copy_data(tab_name varchar2 ,column_to_change  varchar2, change_value varchar2, rowcount number) is

  stmt varchar2(100);
  stmt2 varchar2(100);
  cur_cv SYS_REFCURSOR;
  v_test_rec test_rec_type;

begin
      stmt2 := 'insert into ' || tab_name || ' values :v_test_rec ';
      open cur_cv for 'select * from ' || tab_name;
      loop
         fetch cur_cv into v_test_rec;
         execute immediate stmt2 USING v_test_rec;
         ...
      end LOOP; 

END copy_data;

However, you can use PL/SQL types only in latest Oracle 12.1 release. Older releases do not support them.

Check also documentation for EXECUTE IMMEDIATE Statement: If the data type is a collection or record type, then it must be declared in a package specification.