oracle ORA-06502:PL/SQL:数值或值错误：大容量绑定：截断绑定

Question

declare
  filehandle1 utl_file.file_type;
  myquery varchar2(4000) := 'select column1 ||''~''|| column2 from mytable';
  mycursor sys_refcursor;
  myresults sys.odcivarchar2list;
begin
  filehandle1 := utl_file.fopen ('D42', 'mydata', 'w');
  open mycursor for myquery; 
  loop
   fetch mycursor bulk collect into myresults;
if myresults.count>0 Then
    for idx in myresults.first..myresults.last loop
      utl_file.put_line(filehandle1, myresults(idx));
    end loop;
End if;
    exit when mycursor%notfound;
  end loop;
  close mycursor;
  utl_file.fclose(filehandle1);
end;

这将返回来自100多个表的结果，但只有1个表崩溃。提前感谢你的帮助

Answer 1

您需要将每个CLOB值写入一个单独的文件，并将该文件名作为主数据记录的一部分。类似于这样：

declare
  data_file utl_file.file_type;
  clob_file utl_file.file_type;
  buffer varchar2(32767);
  position pls_integer;
  chars pls_integer;

  myquery varchar2(4000) := 'select column1 ||''~''|| column3, '
    || '''column2_'' || rownum, column2 from mytable';
  mycursor sys_refcursor;
  myresult varchar2(4000);
  myfilename varchar2(120);
  myclob clob;
begin
  data_file := utl_file.fopen ('D42', 'mydata', 'w');
  open mycursor for myquery; 
  loop
    fetch mycursor into myresult, myfilename, myclob;
    exit when mycursor%notfound;
    if myclob is not null and dbms_lob.getlength(myclob) > 0 then
      myresult := myresult ||'~'|| myfilename;
      clob_file := utl_file.fopen ('D42', myfilename, 'w', 32767);
      position := 1;
      chars := 32767;
      while position < dbms_lob.getlength(myclob) loop
        dbms_lob.read(myclob, chars, position, buffer);
        utl_file.put(clob_file, buffer);
        utl_file.fflush(clob_file);
        position := position + chars;
      end loop;
      utl_file.fclose(clob_file);
    end if;
    utl_file.put_line(data_file, myresult);
  end loop;
  close mycursor;
  utl_file.fclose(data_file);
end;
/

有一个data_file，其中包含所有非CLOB数据，包括将该行的CLOB写入的单个文件的名称。文件名可以是任何东西，只要它是唯一的；我使用了rownum，但是您可以使用行的主键ID，例如，如果它有一个。

创建一个虚拟表，如下所示：

create table mytable (column1 number, column2 clob, column3 varchar2(10));
insert into mytable (column1, column2, column3) values (1, null, 'First');
insert into mytable (column1, column2, column3) values (2, 'Second CLOB', 'Second');
insert into mytable (column1, column2, column3) values (3, 'Third CLOB', 'Third');

1~First
2~Second~column2_2
3~Third~column2_3

和具有相应CLOB值的column222和column23文件。

然后，如果我使用该数据文件和可用的CLOB文件以及控制文件运行SQL*Loader:

load data
characterset UTF8
truncate
into table mytable2
fields terminated by "~"
trailing nullcols
(
  column1 char(10),
  column3 char(10),
  clob_filename filler char(120),
  column2 lobfile(clob_filename) terminated by EOF
)

...新表填充为：

select * from mytable2;

   COLUMN1 COLUMN2                        COLUMN3  
---------- ------------------------------ ----------
         1                                First      
         2 Second CLOB                    Second
         3 Third CLOB                     Third