Question

我正试图提出一个称为动态的运行时间表,并在索引表上使用大体更新数据,但在我试图执行这一错误时:

ERROR at line 1: ORA-06550: line 0, column 0: PLS-00801: internal error [74301

注

declare

     type index_tbl_type IS table of
        number
     index by binary_integer;
     num_tbl index_tbl_type;
     TYPE ref_cur IS REF CURSOR;
     cur_emp ref_cur;
    begin
         execute immediate  create table dynamic (v_num number) ;--Creating a run time tabl

         FOR i in 1..10000 LOOP
              execute immediate  insert into dynamic values( ||i|| ) ;--run time insert
         END LOOP;
        OPEN cur_emp FOR  select * from dynamic ;--opening ref cursor
            FETCH cur_emp bulk collect into num_tbl;--bulk inserting in index by table
        close  cur_emp;

        FORALL i in num_tbl.FIRST..num_tbl.LAST --Bulk update
             execute immediate  insert into dynamic values( ||num_tbl(i)|| ) ;
    end;

Answer 1

所有声明均希望发表一份内容表——INSERT、UPDATE或DELETE。 EXECUTE IMMEDIATE是一份PL/SQL声明,因此,你的法典正在对这一例外进行hur击。

种植这种猎物并不是生产的良好想法。表格应当使用DDL的文字而不是动态。

不管怎样,如果你想以这种充满活力的方式做一些事情的话,这就是如何做到:

第1步:创建一种可用于“质量标准”声明的“质量标准”

SQL> create or replace type my_nums as table of number
  2  /

Type created.

SQL>

步骤2:采用表格类型而不是PL/SQL。我已将“所有”条款改写成一个动态的INSERT声明,使用TABLE()条款中的收集方法。

SQL> declare
  2
  3        num_tbl my_nums;
  4        TYPE ref_cur IS REF CURSOR;
  5        cur_emp ref_cur;
  6  begin
  7      execute immediate  create table dynamic (v_num number) ;
  8
  9      FOR i in 1..10000 LOOP
 10           execute immediate  insert into dynamic values( ||i|| ) 
 11      END LOOP;
 12      OPEN cur_emp FOR  select * from dynamic ;
 13      FETCH cur_emp bulk collect into num_tbl;
 14      close  cur_emp;
 15
 16      execute immediate 
 17          insert into dynamic select * from table(:1)  using num_tbl;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>

步骤3:

SQL> select count(*) from dynamic
  2  /

  COUNT(*)
----------
     20000

SQL>

Answer 2

DECLARE

            TYPE numlist is table of number index by binary_integer;

            var_num numlist;

BEGIN

            for i in 1..1000 loop

                        var_num(i):=i;

            end loop;

            EXECUTE IMMEDIATE  create table exe_table(col1 number(10)) ;

            forall i in var_num.first..var_num.last

                        EXECUTE IMMEDIATE  INSERT INTO exe_table values(:P)  USING var_num(i);

end loop;

WHY 当时工作吗?

友情链接