Перекомпил подчиненного типа

У нас есть два типа, один объектный, другой - таблица значений первого типа.
Первый тип:
create or replace type t_test1 as object
(
  ROW_ID          NUMBER,
  G_ID            NUMBER,
  QTY             NUMBER,
  PRICE           NUMBER
)
Второй тип:
create or replace type tb_test1 as table of t_test1

Если мы захотим добавить/убрать поле в первом типе, то при компиле мы получим ошибку:
ORA-02303: cannot drop or replace a type with type or table dependents

Что бы не делать подобных манипуляций:
- удалять второй тип, который ссылается на первый;
- редактировать первый;
- повторно создавать второй;

можно добавить в описание первого типа ключевое слово FORCE:
create or replace type t_test1 FORCE as object
(
  ROW_ID          NUMBER,
  G_ID            NUMBER,
  QTY             NUMBER
)
При перекомпиле второй тип станет инвалидным, поэтому его тоже надо перекомпилить.

FORCE действует только для типов данных, если на тип ссылается таблица, то FORCE не поможет.

Как проверить кто ссылается на пакет

Для этого используем системное представление (user/all/dba)_dependencies:

SELECT * FROM user_dependencies

NAME - имя объекта, для которого проверяем, на какие объекты он ссылается;
REFERENCED_NAME - имя объекта, для которого проверяем, какие объекты на него ссылаются.

Такие зависимости можно проверять не только для пакетов, а и для хранимых процедур/функций, триггеров, синонимов и т.д.

RollBack в цикле и ORA-1002 Fetch out of sequence

В рабочем проекте начала появляться ошибка "ORA-1002 Fetch out of sequence". Продебажил и заметил что ошибка появляется в неявном курсоре на следующей итерации. До самого курсора есть еще куча DML, после какого-то стоит Commit, после какого-то нет.
Смысл курсора такой: идет выгрузка документов в центральную базу с обновлением признака выгрузки. Вся внутренность цикла завернута в exception:
exception
  when others then
    v_st := SQLERRM;
    rollback;
    dbms_output.put_line(v_st);
end;

После первой неудачной итерации вижу как дебагер переходит на for s in (select... и выпадает ошибка ORA-1002 Fetch out of sequence
В итоге пришлось поставить Commit перед циклом и все заработало.

Поискал в интернете и нашел статью 

Провел свои эксперименты. Первоначально создал две таблички tbl_1 и tbl_2 с одним числовым полем id1 и id2 соответственно. В Test Windows в PL/SQL Developer попытался выполнить такой код:

	declare
	i integer;
	begin
	insert into tbl_1
	(id1)
	values
	(1);
	for s in (SELECT t.* FROM all_objects t where rownum < 3) loop
	begin
	insert into tbl_2
	(id2)
	values
	(1);
	i := 7 / (2-2);
	exception
	when others then
	rollback;
	dbms_output.put_line(SQLERRM);
	end;
	end loop;
	end;

view raw rollback_in_loop_1 hosted with ❤ by GitHub

И после первой итерации получаем "ORA-01002 Fetch out of sequence", а в DBMS Output только одну запись: ORA-01476: divisor is equal to zero.
Меняем код - добавляем Commit перед циклом и все отрабатывает без ошибок. На выходе 2 записи:
ORA-01476: divisor is equal to zero
ORA-01476: divisor is equal to zero
Пробуем использовать Savepoint:

	/*using savepoint*/
	declare
	i integer;
	cnt number := 0;
	begin
	insert into tbl_1
	(id1)
	values
	(1);
	for s in (SELECT t.* FROM all_objects t where rownum < 3) loop
	cnt := cnt + 1;
	execute immediate 'savepoint svp'||cnt;
	begin
	insert into tbl_2
	(id2)
	values
	(1);
	i := 7 / (2-2);
	exception
	when others then
	execute immediate 'rollback to savepoint svp'||cnt;
	dbms_output.put_line(SQLERRM || ' => ' || cnt);
	end;
	end loop;
	end;

view raw Rollback_in_loop_2 hosted with ❤ by GitHub

На выходе получаем 2 записи:
ORA-01476: divisor is equal to zero => 1
ORA-01476: divisor is equal to zero => 2     
  
Вольный перевод причины возникновения ошибки: Rollback возвращает нас к состоянию до открытия курсора, а значит курсор должен быть закрыт. А когда следующая итерация пробует получить данные с закрытого курсора возникает ошибка.
НО! Я пробовал посмотреть атрибут %ISOPEN, предварительно переделав все на явный курсор и НИЧЕГО. То есть атрибут после Rollback все-равно True. 
Но на будущее будем знать =)



 

Поднять упавший джоб

begin
  dbms_job.broken(job => 904,broken => false);
end;

Передача массива с Delphi в коллекцию PL/SQL

Предыстория: понадобилось мне получать данные с кассового аппарата (КА) и передавать их в Оракл для дальнейшей обработки. Данные в КА хранятся в виде таблицы товаров с указанием цены, количества, к-ва продаж/возвратов и т.д. Получать данные можно только по одной строке с таблицы (такие методы у КА). И вот, что бы не передавать по одной строке с Делфи в Оракл решено было узнать как можно передать сразу все строки. Изначально план был такой: заполняем многомерный массив и передаем его в Оракл, но, скажу сразу, что ни многомерный, ни массив массивов передать не удалось и судя по некоторым форумам, которые я облазил за эти 2 дня, это сделать невозможно. У меня постоянно возникала ошибка несоответствия типов. Если все-таки такая возможность есть - комментируйте. 
В итоге я передавал в функцию, в качестве параметров одномерные массивы. Количество этих массивов соответствовало количеству необходимых мне для обработки колонок в таблице КА. 
Начну, пожалуй, с того, что я создал в 2 глобальных типа в PL/SQL:
create or replace type T_ExportDataFromMINI as object
  (
    ed_NumDoc           NUMBER,   -- Номер дока
    ed_NumRow           NUMBER, -- Номер строки в доке     
    ed_Price            NUMBER(12,2),   --Цена
    ed_QtySale          NUMBER,
    ed_SumSale          NUMBER(12,2),
    ed_QtyRet           NUMBER,
    ed_SumRet           NUMBER(12,2)
  ) 
и второй:
create or replace type t_exportdatafrommini_tree is table of t_exportdatafrommini 
Потом, в пакете, я прописал 7 локальных типов:
type t_mini_numdoc is table of number index by binary_integer; 
type t_mini_numrow is table of number index by binary_integer;
type t_mini_price is table of number(12,2) index by binary_integer;
type t_mini_qtysale is table of number index by binary_integer;
type t_mini_sumsale is table of number(12,2) index by binary_integer;
type t_mini_qtyret is table of number index by binary_integer;
type t_mini_sumret is table of number(12,2) index by binary_integer;   
и написал функцию, которая принимает параметры. Каждый параметр - это коллекция с типом, который я прописал выше. Сама функция:

	function PARSEEXPORTDATAFROMMINI2(p_numdoc t_mini_numdoc, p_numrow t_mini_numrow, p_price t_mini_price,
	p_qtysale t_mini_qtysal, p_sumsale t_mini_sumsale, p_qtyret t_mini_qtyret,
	p_sumret t_mini_sumret) return integer is
	i integer;
	v_array t_exportdatafrommini_tree := t_exportdatafrommini_tree();
	begin
	--наполняем коллекцию
	for i in 1..p_numdoc.COUNT loop
	v_array.Extend();
	v_array(v_array.count) := t_exportdatafrommini(p_numdoc(i), p_numrow(i), p_price(i),
	p_qtysale(i), p_sumsale(i), p_qtyret(i), p_sumret(i));
	end loop;
	--проверяем, что же пришло с РКА
	for rec in (select * from Table(cast(v_array as t_exportdatafrommini_tree))) loop
	insert into tbl_roma
	(tr_id, tr_doc_num, tr_log_info, tr_date)
	values
	(888, 888, rec.ed_NumDoc || '/' || rec.ed_NumRow || '/' || rec.ed_price || '/' ||
	rec.ed_qtysale || '/' || rec.ed_sumsale || '/' || rec.ed_qtyret || '/' || rec.ed_sumret, sysdate);
	end loop;
	commit;
	return 1;
	end;

view raw 2 hosted with ❤ by GitHub

Далее переходим к Делфи. На форму был добавлен элемент TOracleQuery, прописаны параметры сессии, добавлен SQL текст:
begin
  :res := PKG_TEST.PARSEEXPORTDATAFROMMINI2(:p_numdoc, :p_numrow, :p_price, :p_qtysale, :p_sumsale, :p_qtyret, :p_sumret);
end;
И определены переменные через Variables Editor:

Самое главное, что бы для переменных, с помощью которых мы хотим передавать массивы была установлена галка PL/SQL Table.
Далее оглашаем необходимое количество массивов и инициализируем их:
.....
  arNumdoc, arNumrow, arPrice, arQtysale, arSumsale, arQtyret, arSumret: variant;
.....
  arNumdoc := VarArrayCreate([0, 3999], varVariant);
  arNumRow := VarArrayCreate([0, 3999], varVariant);
  arPrice := VarArrayCreate([0, 3999], varVariant);
  arQtysale := VarArrayCreate([0, 3999], varVariant);
  arSumsale := VarArrayCreate([0, 3999], varVariant);
  arQtyret := VarArrayCreate([0, 3999], varVariant);
  arSumret := VarArrayCreate([0, 3999], varVariant);

Я указал размер массива равным 4000 элементов, так как в КА нет метода, что бы получить количество элементов в памяти. После заполнения массивов данными (метод VarArrayPut) я меняю верхнюю границу массивов с помощью метода VarArrayRedim.
Далее заполняем SQL переменные:
with oqExportData do
    begin
      SetVariable('p_numdoc', arNumdoc);
      SetVariable('p_numrow', arNumrow);
      SetVariable('p_price', arPrice);
      SetVariable('p_qtysale', arQtysale);
      SetVariable('p_sumsale', arSumsale);
      SetVariable('p_qtyret', arQtyret);
      SetVariable('p_sumret', arSumret);
      Execute;
    end
 
И после всего этого обнуляем переменные:
   arNumdoc := Unassigned;
  arNumrow := Unassigned;
  arPrice := Unassigned;
  arQtysale := Unassigned;
  arSumsale := Unassigned;
  arQtyret := Unassigned;
  arSumret := Unassigned;
 
 

Знак зодиака в Oracle

Недавно, захотел я узнать представителя какого знака зодиака больше у нас в департаменте. Приведу кусок кода, который как-раз и парсит знаки по дате рождения:

      case 

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 3 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 4 AND EXTRACT(day FROM t.birthdate) <= 19)  THEN 'Овен'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 4 AND EXTRACT(day FROM t.birthdate) >= 20) OR (EXTRACT(MONTH FROM t.birthdate) = 5 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Телец'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 5 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 6 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Близнецы'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 6 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 7 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Рак'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 7 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 8 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Лев'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 8 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 9 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Дева'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 9 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 10 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Весы'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 10 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 11 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Скорпион'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 11 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 12 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Стрелец'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 12 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 1 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Козерог'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 1 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 2 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Водолей'

            WHEN (EXTRACT(MONTH FROM t.birthdate) = 2 AND EXTRACT(day FROM t.birthdate) >= 21) OR (EXTRACT(MONTH FROM t.birthdate) = 3 AND EXTRACT(day FROM t.birthdate) <= 20) THEN 'Рыба'

      end 

В общем по тем сотрудникам, у которых в названии профессии указано "программист" (искал like '%програм%')  вышел вот такой результат:

Так что если вы дева или весы - у вас больше шансов стать программистом =)