использование длинной строки (более 4000) в запросе оракула

Я знаю, что в sql varchar2 может быть только около 4000.

Я знаю, что в оракуле PL varchcar2 может быть около 32000.

У меня определена переменная varchar2 длиной более 4000 символов, и я хочу использовать ее в запросе. Я не хочу вставлять значение в таблицу. Значение представляет собой строку с разделителями, которую я анализирую и вставляю в таблицу с помощью этого запроса. Этот запрос работает, когда длина переменной меньше 4000 символов. Есть ли способ заставить его работать до 32000 символов?

create global temporary table t(single_element varchar(500),element_no number);
declare
--declared as 32767 but this string contains less than 4000 characters. 
--This will work. If you expand the string to 32000 characters it will not work.
myvar varchar2(32767) := 'tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4';
begin
delete from t;
insert into t
SELECT SUBSTR(str, start_pos, (next_pos-start_pos)) AS single_element, element_no
FROM  (
        SELECT 
              ilv.str, 
              nt.column_value AS element_no, 
              INSTR(ilv.str, '^~', DECODE(nt.column_value, 1, 0, 1), DECODE(nt.column_value, 1, 1, nt.column_value-1)) + 2 AS start_pos,
              INSTR(ilv.str, '^~', 1, DECODE(nt.column_value, 1, 1, nt.column_value)) AS next_pos
        FROM   (
                select '~' || myvar || '^~' as str, 
                (Length(myvar) - length(replace(myvar,'^~','')))/2 + 2 as no_of_elements 
                from dual) ilv,

              TABLE(
                    CAST(
                       MULTISET(
                          SELECT ROWNUM FROM dual CONNECT BY ROWNUM < ilv.no_of_elements
                          ) AS number_ntt )) nt
         );
end;

Ошибка, которую я получаю при расширении «myvar» до 32000 символов:

can bind a LONG value only for insert into a LONG column

Есть ли способ обойти это ограничение размера, потому что я на самом деле не вставляю это значение в таблицу, я просто использую его в запросе?


oracle plsql varchar2
person kralco626    schedule 06.09.2011    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Вам нужно определить переменную как VARCHAR2? Не могли бы вы вместо этого определить его как CLOB?

Если я изменю объявление MYVAR с VARCHAR2(32767) на CLOB и определю тип NUMBER_NTT, ваш код будет работать для меня.

SQL> ed
Wrote file afiedt.buf

SP2-0161: line 2 truncated.
  1  declare
  2  myvar clob := 'tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testms
  <<snip>>
~tcd3~#testmsg3^~tcd4~#testmsg4';
  4  begin
  5  delete from t;
  6  insert into t
  7  SELECT SUBSTR(str, start_pos, (next_pos-start_pos)) AS single_element, elem
ent_no
  8  FROM  (
  9          SELECT
 10                ilv.str,
 11                nt.column_value AS element_no,
 12                INSTR(ilv.str, '^~', DECODE(nt.column_value, 1, 0, 1), DECODE
(nt.column_value, 1, 1, nt.column_value-1)) + 2 AS start_pos,
 13                INSTR(ilv.str, '^~', 1, DECODE(nt.column_value, 1, 1, nt.colu
mn_value)) AS next_pos
 14          FROM   (
 15                  select '~' || myvar || '^~' as str,
 16                  (Length(myvar) - length(replace(myvar,'^~','')))/2 + 2 as n
o_of_elements
 17                  from dual) ilv,
 18                TABLE(
 19                      CAST(
 20                         MULTISET(
 21                            SELECT ROWNUM FROM dual CONNECT BY ROWNUM < ilv.n
o_of_elements
 22                            ) AS number_ntt )) nt
 23           );
 24* end;
 25  /

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
       172

При этом я бы не стал анализировать строку с разделителями, особенно в PL/SQL. Но это делает работу.

person Justin Cave    schedule 06.09.2011
comment
Я пытался придумать более эффективный способ анализа строки, чем цикл. Это кажется быстрее, чем перебирать строку с разделителями и вставлять каждую строку по отдельности. У вас была другая идея о том, как разобрать строку? - person kralco626; 06.09.2011
comment
p.s. - я предвзято отношусь к операциям, не основанным на наборах... СУБД по своей природе работают на основе наборов, мне еще предстоит увидеть пример, в котором есть естественное преимущество использования циклов по сравнению с решением, основанным на наборах. - person kralco626; 06.09.2011

arrow_upward
1
arrow_downward

ОК, ну, это граничит с вашей предвзятостью реализации, хотя помните, что forall ЯВЛЯЕТСЯ операцией массового связывания, а не настоящим циклом, но смотрели ли вы на функцию dbms_utility.comma_to_table?

Это оптимизированная внутренняя функция анализа оракула, хотя и с некоторыми ограничениями, о которых вы можете прочитать здесь: http://www.techiegyan.com/2009/02/17/oracle-breaking-comma-separated-string-using-dbms_utilitycomma_to_table/

Вам нужно будет replace() сделать его с разделителями-запятыми, а также заключить в двойные кавычки, если у вас есть проанализированные поля, которые начинаются с цифр, специальных символов, содержат запятые и т. д.

Но если ваши данные позволяют - это, безусловно, сделает ваш код чище (и, вероятно, будет работать намного быстрее).

declare
   myvar      varchar2(32000) := 'tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3';
   mycnt      binary_integer;
   myresults  sys.dbms_utility.lname_array;
begin
   sys.dbms_utility.comma_to_table('"'||replace(myvar,'^~','","')||'"', mycnt, myresults );
   delete from t;
   forall ix in myresults.first..myresults.last 
      insert into tvalues (myresults(ix));
   commit;
end;
person Michael Broughton    schedule 07.09.2011
comment
думал об этом, однако, если они используют два специальных символа в качестве разделителей, я предполагаю, что это потому, что они не могут гарантировать, что данные не содержат запятых... - person kralco626; 08.09.2011