PL/SQL. Аналог sizeof()

[ASokol]

PL/SQL. Аналог sizeof()

Есть ли в PL/SQL сабж?

Хочется

declare
  type recFoo is record (
    id varchar2(5)   
  );
  
  lRec recFoo;
  lBar varchar2(500) := '123456'; 
begin
  if length(lBar) <= [U]sizeof[/U](lRec.id) then
    lRec.id := lBar; 
  end if;
end;
/

потому что

declare
  type recFoo is record (
    id varchar2(5)   
  );
  
  lRec recFoo;
  lBar varchar2(500) := '123456'; 
begin
  begin
    lRec.id := lBar; 
  exception
    when value_error then
      null;
  end;
end;
/

не устраивает своей тормознутостью и громоздкостью.

 

[Andy_ua]

также интересно
функция vsize() не подходит?

 

[ASokol]

ну она вообще-то только в SQL работает.

PLS-00204: function or pseudo-column 'VSIZE' may be used inside a SQL statement only

declare
  lVar varchar2(2000) := '123';
  lMessage varchar2(2000) := '';
begin
  select vsize(lVar)
    into lMessage
    from dual;    
  raise_application_error(-20001, lMessage);
end;
/

Результат:

ORA-20001: 3
ORA-06512: at line 8

В sys.standard есть даже комментарий по этому поводу:

-- DUMP and VSIZE are now not allowed in non-sql plsql, has code to forbid
-- it there, and is defined as a builtin in stdspc. The body will not be
-- called in plsql.
--- CMB

 

[Andy_ua]

наверное стоит спросить на sql.ru

 

[ASokol]

Если интересно, то

Тільки зареєстровані користувачі бачать весь контент у цьому розділі

созданная тема.

 

[eugene_kr]

Может не совсем в ту степь...
Для простоты рассмотрим случай строк - можно опросить словарь схемы-получателя, запомнить размер поля-получателя (или pl/sql'льный массив в пакете) и используя динамический sql (execute immediate) по универсальному шаблону сгенерировать и выполнить необходимую проверку. Будет медленнее чем статический sql, но быстрее чем обработка исключений (imho).
Второй вариант - если известно что попадает на вход к твоему "sizeof" (а это должно быть известно, так как данные находятся в словаре-схемы), то можно заполнить структуру в памяти вида (ид-предельный размер) и написать функцию, которая по "ид" вернёт "предельный размер".
Ключевой момент в том, что данные берутся из самой системы и ничего хардкодить не надо.

 

[ASokol]

Нет там массива. А есть такая запись на схеме-приемнике:

  TYPE r_employee_sync IS RECORD (
    extsysID      employee.extsysid%TYPE, -- уникальный идентификатор ВО ВНЕШНЕЙ СИСТЕМЕ
    lastname      employee.lastname%TYPE, -- фамилия
    firstname     employee.firstname%TYPE, -- имя
    middlename    employee.middlename%TYPE, -- отчество
    ...
    puttingondate      employee.puttingondate%TYPE, -- дата приёма сотр на работу
    firedate           employee.firedate%type, -- дата увольнения
    appointmentname    employee.appointment%TYPE, -- название должности
    appointmentcode    VARCHAR2(10), -- код должности
    passportno         VARCHAR2(25), -- серия и номер паспорта (через пробел)
    ...

Она набивается данными и скармливается пакету на промежуточной схеме, который передает эти данные дальше. Часть полей VARCHAR2(n), часть %TYPE. Динамический sql там ни к селу, ни к городу. Record из пакета в словаре схемы? Особенно, когда сам record протянут через несколько пакетов.

SUBTYPE r_employee_setting IS PKI_EMPLOYEE_SYNC.r_employee_setting;

 

[eugene_kr]

Как учит Кайт - всё нужно проверять.

По поводу производительности предлагаю рассмотреть вывод такого скрипта:

declare
  MAX_ITER integer := 10000000;
  st timestamp;
  md timestamp;
  en timestamp;
  fi timestamp;
begin
  st := systimestamp();
  -- case1: with exception generation
  for i in 1 .. MAX_ITER loop
    declare
      type recFoo is record (
        id varchar2(5)   
      );
      
      lRec recFoo;
      lBar varchar2(500) := '123456'; 
    begin
      begin
        lRec.id := lBar; 
      exception
        when value_error then
          lRec.id := SubStr(lBar, 1, 5); 
      end;
    end;
  end loop;
  md := systimestamp();

  -- case2: without exception generation
  for i in 1 .. MAX_ITER loop
    declare
      type recFoo is record (
        id varchar2(5)   
      );
      
      lRec recFoo;
      lBar varchar2(500) := '12345'; 
    begin
      begin
        lRec.id := lBar; 
      exception
        when value_error then
          lRec.id := SubStr(lBar, 1, 5); 
      end;
    end;
  end loop;
  en := systimestamp();

  -- case3: regilar if-then-else check
  for i in 1 .. MAX_ITER loop
    declare
      type recFoo is record (
        id varchar2(5)   
      );
      
      lRec recFoo;
      lBar varchar2(500) := '123456'; 
      lLen integer := 5;
    begin
      if length(lBar) <= lLen then
        lRec.id := lBar; 
      else
        lRec.id := SubStr(lBar, 1, 5); 
      end if;
    end;
  end loop;  
  fi := systimestamp();
  dbms_output.put_line('case1: with exception = ' || to_char(md-st));
  dbms_output.put_line('case2: without exception = ' || to_char(en-md));
  dbms_output.put_line('case3: if check = ' || to_char(fi-en));
end;

У меня получилось, что постоянная генерация исключеия value_error (case1) потребовала ~в три раза больше времени, чем в случае когда она не генерилась (case2). "Идеальные" вариант с предварительной проверкой (case3 и case4) потребовали примерно в 1,5-2 раза больше времени чем случай case2.
Если предположить, что не все значения будут нарушать ограничения, то вариант с исключительными ситуациями будет работать тот же порядок времени, что и "идеальный" вариант.

По поводу "громоздкости" - в исходном примере "громоздкость" придают, судя по всему, только три строки "begin-end-exception". Так ведь процедуры можно писать и на jave/c...

Может имеет смысл "тягать" данные не через записи pl/sql, а через временные таблицы и реализовать проверку на уровне таблиц, когда явно известны ограничения таблицы-приёмника?

 

[Andy_ua]

на sql.ru дали ссылку на правильное решение

SQL> DECLARE
2 variable_name VARCHAR2(24) := 'string literal';
3 /*
4 Determine (non-intrusively) the declared length of a given varchar2
5 variable.
6 %param p_varchar2 the varchar2 for which the declared length is required.
7 */
8 FUNCTION declared_length (
9 p_varchar2 IN OUT VARCHAR2)
10 RETURN INTEGER
11 IS
12 FUNCTION declared_length (
13 p_char IN OUT CHAR,
14 p_varchar2 IN OUT VARCHAR2)
15 RETURN INTEGER
16 IS
17 BEGIN
18 IF (p_char IS NULL) THEN
19 p_char := ' ';
20 END IF;
21 RETURN LENGTH (p_char);
22 END declared_length;
23 BEGIN
24 RETURN declared_length (p_varchar2, p_varchar2);
25 EXCEPTION
26 WHEN OTHERS THEN
27 raise_application_error (-20000, 'failed to derive declared length', TRUE);
28 END declared_length;
29 BEGIN
30 dbms_output.put_line ('declared_length (variable_name) => ' || declared_length (variable_name)
31 dbms_output.put_line ('length (variable_name) => ' || LENGTH (variable_name));
32 dbms_output.put_line ('variable_name => "' || variable_name||'"');
33 END;
34 /
declared_length (variable_name) => 24
length (variable_name) => 14
variable_name => "string literal"

 

[ASokol]

Ага.