INSERT /*+ APPEND */

    Я хотела написать один большой пост про оптимизацию INSERT, однако в процессе создания, поняла, что будет лучше разбить его на несколько частей. Это первая и, надеюсь, не последняя из них.

SEQUENCE: как вернуть несколько значений

Если какому-то из ваших приложений потребовалось запросить ID из базы пачками, то этого можно добиться совместив вызов NEXTVAL с иерархическим запросом.

select STG_SEQ.nextval
from dual 
connect by level < 10;

При этом, вернется 9 ID, так что будьте внимательны.

BITMAP CONVERSION TO ROWIDS

Ни для кого не секрет, что иногда оптимизатор Oracle ведет себя как хочет, и его настройка превращается в сплошное шаманство и пляски с бубном. На волне этой боли и душевных терзаний, я хочу поговорить о подсказке для оптимизатора OPT_PARAM.

Динамический проход по столбцам в таблице

Иногда на жизненном пути попадаются задачи, в которых нужно обойти столбцы в таблице с неизвестной структурой. Допустим, есть таблица, в которой заказчики обожают добавлять, удалять и изменять столбцы, а вам нужно завязать какую-то логику на все столбцы, столбцы определенного типа, и так далее.
В этом случае, если вы испытываете вполне понятное отвращение, к тому, чтобы каждый раз менять код приложения, вам придется воспользоваться dynamic SQL. (Если вы испытываете отвращение и к dynamic SQL тоже, у меня для вас плохие новости...)

Рассмотрим пример кода, как это может работать

create or replace
  package Test_Dynamic_Col
    is
/* важная переменная, с помощь которой мы будем обходить поля в таблице */
      some_rec sometable%rowtype; 
end;
/
create or replace
  procedure test_proc( p_some_rec sometable%rowtype )
    is
        v_attr_value varchar2(30);
    begin
/* Скидываем значение в глобальную переменную */
        Test_Dynamic_Col.some_rec := p_some_rec;
/* Выбираем нужные поля */
        for attr in (select column_name, data_type from user_tab_columns where  table_name = 'SOMETABLE') loop
        execute immediate 'begin :1 := Test_Dynamic_Col.some_rec.' || attr.column_name || '; end;'
          using out v_attr_value;
/* Здесь логика, по вашему усмотрению */
          dbms_output.put_line(attr.column_name || ' is ' || v_attr_value);
        end loop;
end;

На что надо обратить внимание:

1. Глобальная переменная может быть объявлена либо в спецификации этого пакета, либо в спецификации другого пакета, но не в теле самого пакета, иначе dynamic SQL не заметит её при выполнении.
2. Параметры в Dynamic SQL должны быть SQL типов.
3. Запись в PL/SQL не должна быть SQL типа.

Debug в Oracle 12с

Если вы когда-либо запускали debug для Oracle 11G и младше, то все, что было нужно для запуска, это привилегии плюс скомпилированный для отладки кусок кода, который хочется запустить:

1.  DEBUG CONNECT SESSION .
2. EXECUTE для DBMS_DEBUG_JDWP.
3. EXECUTE для процедуры, которую надо отладить.
4. Сама процедура, для которой проведено “Compiled for Debug”.

Для отладки в Oracle 12c все это по прежнему необходимо, но уже недостаточно. Есть большая вероятность, что при первом же запуске отладчика вы увидите это:

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.10’, ‘49428’ )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database SCOTT – ORA12CTESTDB

Это произошло с вами потому, что начиная с версии 12c, если вы хотите запустить отладку PL\SQL в базе через  отладчик, использующий Java Debug Wire Protocol (JDWP),  такой, как например SQL Developer или JDeveloper, то вам потребуются дополнительная настройка jdwp ACL привилегий для подключения к БД сессии через отладчик.

Один из простых способов задать эти настройки выглядит так:

BEGIN

 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE

 (

 host => '192.168.0.10',

 lower_port => null,

 upper_port => null,

 ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),

 principal_name => 'scott',

 principal_type => xs_acl.ptype_db)

 );

END;

Host может быть именем хоста, доменным именем, IP адресом или subnet-ом.