Save Clob to file

Нашел на форуме быстый и элегантный способ сохранить clob в файл на сервере

DBMS_XSLPROCESSOR.clob2file(
  cl => l_clob
, flocation => 'XML_LOG'
, fname => myfile_name
);

Temporary lobs

Встала задачка собрать много строчек таблицы в один BLOB.
Краткий алгоритм как это сделать: делаем temporary lob - изменяем его, вставляя строки - вставляем - очищаем - возвращаемся к шагу 1.
В нижележащих тестах разница, на сколько отличается по времени работа с temporary lob, созданного с параметром cache = true и cache = false
Результаты: cache = true в 20 раз быстрее
Код и результата теста

SET SERVEROUTPUT ON

PROMPT CACHE=TRUE

DECLARE
  bl BLOB;
  l_raw RAW(32767);
  l_temp_cnt NUMBER;
  l_start_time NUMBER := dbms_utility.get_time;
  FUNCTION get_temp_blocks_cnt RETURN NUMBER IS
    l_cnt NUMBER;
  BEGIN
    SELECT SUM(u.blocks) 
    INTO l_cnt
    FROM v$tempseg_usage u, v$session s
    WHERE u.session_addr = s.saddr AND AUDSID = USERENV('SESSIONID');
    RETURN NVL(l_cnt, 0);
  END;
BEGIN
  l_temp_cnt := get_temp_blocks_cnt;
  DBMS_LOB.createtemporary(bl, TRUE);

  FOR i IN 1 .. 100000 LOOP
    l_raw := utl_raw.cast_to_raw(RPAD(i, 1000, ' '));
    dbms_lob.writeappend(bl, utl_raw.length(l_raw), l_raw);
    IF MOD(i, 10) = 0 THEN 
      dbms_lob.freetemporary(lob_loc => bl);
      DBMS_LOB.createtemporary(bl, TRUE);
    END IF;
  END LOOP; 

  dbms_output.put_line('Temp used=' || (get_temp_blocks_cnt - l_temp_cnt)); 
  dbms_output.put_line('Time used ' || (dbms_utility.get_time - l_start_time) / 100); 
END;
/

PROMPT CACHE=FALSE

DECLARE
  bl BLOB;
  l_raw RAW(32767);
  l_temp_cnt NUMBER;
  l_start_time NUMBER := dbms_utility.get_time;
  FUNCTION get_temp_blocks_cnt RETURN NUMBER IS
    l_cnt NUMBER;
  BEGIN
    SELECT SUM(u.blocks) 
    INTO l_cnt
    FROM v$tempseg_usage u, v$session s
    WHERE u.session_addr = s.saddr AND AUDSID = USERENV('SESSIONID');
    RETURN NVL(l_cnt, 0);
  END;
BEGIN
  l_temp_cnt := get_temp_blocks_cnt;
  DBMS_LOB.createtemporary(bl, FALSE);

  FOR i IN 1 .. 100000 LOOP
    l_raw := utl_raw.cast_to_raw(RPAD(i, 1000, ' '));
    dbms_lob.writeappend(bl, utl_raw.length(l_raw), l_raw);
    IF MOD(i, 10) = 0 THEN 
      dbms_lob.freetemporary(lob_loc => bl);
      DBMS_LOB.createtemporary(bl, FALSE);
    END IF;
  END LOOP; 

  dbms_output.put_line('Temp used=' || (get_temp_blocks_cnt - l_temp_cnt)); 
  dbms_output.put_line('Time used ' || (dbms_utility.get_time - l_start_time) / 100); 
END;
/  

CACHE=TRUE
Temp used=0
Time used 2.08
PL/SQL procedure successfully completed
CACHE=FALSE
Temp used=0
Time used 42.29
PL/SQL procedure successfully completed

Примечательно, что единственный способ, который сработал для очистки BLOB это

      dbms_lob.freetemporary(lob_loc => bl);
      DBMS_LOB.createtemporary(bl);

Ни присвоение NULL, ни empty_blob, который работает только для insert и select, как оказалось из найденной документации от версии 8.1

An exception is raised if you use these functions anywhere but in the VALUES clause of a SQL INSERT statement or as the source of the SET clause in a SQL UPDATE statement.
А для свежих версий
You cannot use the locator returned from this function as a parameter
to the DBMS_LOB package or the OCI.

Для дальнейших исследований можно заняться замерами потребляемой памяти.

Синхронизация пользователя из SSO (OID) в OEBS

Случайно удалил пользователя OEBS в OID. Как результат: пользователь в OEBS живет (а удалить оотуда ничего нельзя), не изменяется (пишет ошибку на выполение пакета fnd_ldap_wrapper) и не синхронизируется назад.

Покопавшись, так и не нашел культурного способа синхронизировать одного пользователя.

Решил действовать в лоб, а именно:
1. Завести руками пользователя в OID
2. Узнать его guid
3. Прописать guid в fnd_user
Первый шаг делается через Oracle Directory Manager, второй и третий скриптом

DECLARE
  l_user_name VARCHAR2(4000) := 'test_user';
  l_guid      RAW(16);
BEGIN
  l_guid := fnd_ldap_user.get_user_guid_and_count(p_user_name => l_user_name,
                                                  n           => :user_count);
  dbms_output.put_line('l_guid=' || rawtohex(l_guid));
  UPDATE fnd_user u SET u.user_guid = l_guid WHERE user_name = l_user_name;
END;

После этого пользователь без ошибок меняется через интерфейс и может зайти под своим паролем.

Два самых больших вопроса, которые меня мучают и о которых, возможно, напишу позднее:
* есть ли культурный способ запушить пользователя (ровно одного, без удаления всей ветки) в OID
* Если верить функции fnd_ldap_user.get_user_guid GUID пользователя хранится в атрибуте orclguid. Но в Oracle Directory Manager такого атрибута я так и не нашел. Наверное эта утилита не позволяет смотреть raw значения. Ну хоть бы написало, что атрибут есть и не пустой :(

SORT GROUP BY

В одном из планов вылез ужасно медленный SORT GROUP BY
После беглого анализа нашел статью
http://guyharrison.squarespace.com/blog/2009/8/5/optimizing-group-and-order-by.html
В котором в красивых картинках все было рассказано, а после приведен хинт /*+USE_HASH_AGGREGATION*/
К сожалению надежды на быструю починку запроса разрушились комментарием VJ Kumar и следующим его подтверждением Jason

when subquery is used to insert records into another table, Oracle
seems to always use sort group by, even hint USE_HASH_AGGREGATION is
in place.
К счастью на 11.2.0.4 добавление хинта сделало запрос работающим как полагается.