Oracle - функция вычисления контрольного разряда EAN-13

Функция для вычисления контрольного разряда штрихкода EAN13 по первым двенадцати цифрам.
На входе подаем строку (12-значное число).
На выходе получаем цифру контрольного разряда.
function GetControlNumber(p_code varchar2) return number
  is
   v_chet number;
   v_nechet number;
   v_itog number;
   v_res number;
  begin
     
     v_chet := to_number(substr(p_code,2,1))+to_number(substr(p_code,4,1))+to_number(substr(p_code,6,1))+to_number(substr(p_code,8,1))+to_number(substr(p_code,10,1))+to_number(substr(p_code,12,1));
     v_nechet := to_number(substr(p_code,1,1))+to_number(substr(p_code,3,1))+to_number(substr(p_code,5,1))+to_number(substr(p_code,7,1))+to_number(substr(p_code,9,1))+to_number(substr(p_code,11,1));
     v_itog := mod(((v_chet*3)+v_nechet),10);

     if v_itog>0 then 
       v_itog := 10 -v_itog;
     end if; 
     v_res:=v_itog;
     
   return v_res;
  end;


Пользуйтесь на здоровье.

Про алгоритм вычисления контрольного разряда можно прочитать здесь.

Oracle - Фичи и полезности (Часть №1)

Здесь буду выкладывать всякие полезные мелочи и запросы, которые могут пригодиться с их описанием.

1. Получаем список всех полей интересующей нас таблицы.
select  column_name 
  from user_tab_columns 
  where upper(table_name) = upper('название_таблицы')


2. Меняем формат даты по умолчанию в сессии.
alter session set NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi:ss';
— Пока сессия не закрыта, будет действовать установленный таким образом формат.

Читать дальше →

Oracle - Функции RANK() и DENSE_RANK() (Или учимся выбирать необходимые значения внутри множественной выборки)

Начнем с функции RANK(). С объяснения для чего она и в каких случаях она нам может пригодиться.

Функция RANK() — это очень полезная функция, она позволяет нам пронумеровать набор по некоторому группирующему значению внутри всего выбранного набора данных. Проще всего показать это на примере. Для этого создадим небольшую таблицу:
ID	PERSON	DT			SM

1	Роман	01.10.2016 11:51:31	545.3
2	Роман	01.10.2016 11:51:31	445.2
3	Роман	01.10.2016 11:51:31	145.3
4	Кирил	01.05.2016 16:51:31	99.5
5	Алена	01.07.2016 12:51:31	445.3
6	Роман	01.12.2016 16:51:31	876.1
7	Кирил	01.06.2016 16:51:31	237.22
8	Алена	01.12.2016 16:51:31	145.3
9	Алена	01.02.2016 14:51:31	534.7
10	Роман	01.03.2016 16:51:31	165.3
11	Кирил	01.04.2016 16:51:31	345.2


Пусть в этой таблице будет содержать некоторые выставленные счета разным пользователям PERSON на разные суммы SM в разное время DT.

Читать дальше →

Полезные мелочи при администрировании и работе с Oracle

Здесь буду публиковать полезные мелочи для БД Oracle, которые пригождаются в быту.

Узнаем все пути к каталогам Oracle
SELECT * FROM V$DIAG_INFO


Узнаем версию сервера Oracle
SELECT * FROM V$VERSION

Размер Таблиц и Tablespace в Oracle

Узнаем сколько занимает каждый TableSpace:
(Размер указан в мегабайтах)
select a.tablespace_name, totalspace, nvl(freespace,0) freespace,
   (totalspace-nvl(freespace,0)) used,
  ((totalspace-nvl(freespace,0))/totalspace)*100 "%USED"
from
  (select tablespace_name, sum(bytes)/1048576 totalspace
   from dba_data_files
   group by tablespace_name) a,
  (select tablespace_name, sum(Bytes)/1048576 freespace
   from dba_free_space
   group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
  and ((totalspace-nvl(freespace,0))/totalspace)*100 > 90
 order by 5 desc
 


Теперь узнаем сколько занимают таблицы в указанном TableSpace:
(Размер в мегабайтах, имя нужного TableSpace — берем из результатов предыдущего запроса, для всех при больших базах может выполняться долго)

select de.owner, de.segment_name, sum(de.bytes)/1048576, de.segment_type from dba_extents de
 where de.owner in ('нужный tablespace')
group by de.owner, de.segment_name, de.segment_type


Вот и всё.

Скрипт для получения сессий блокирующих таблицы (Oracle)

Выводит сессии которые блокируют таблицы:
select distinct ddl.session_id,
                s.SERIAL#,
                ddl.owner,
                ddl.name,
                ddl.mode_held,
                ddl.mode_requested,
                s.username,
                s.status
  from dba_dml_locks ddl, v$session s
 where ddl.session_id = s.SID

Русские буквы в SQLPlus под Windows

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

Итак, если видим кракозябры вместо букв, то делаем:

1. Смотрим что у нас с NLS_LANG в реестре. Должно быть:
HKLM\Software\Oracle\Home0\NLS_LANG = AMERICAN_AMERICA.CL8MSWIN1251
Скорее всего, там RUSSIAN_RUSSIA — меняем.

2. Деламе батник которым будем запускать SQLPlus:
@mode con cp select=1251
@sqlplus
@exit


Всё.
Запускаем, при помощи bat файла, SQLPlus и радуемся русским буквам. :)

Работа с деревьями в Oracle (PL/SQL)

В этой публикации объясняется, как работать со специфическими методами, которые присутствуют в базе данных Oracle, для работы с Деревьями. Итак, сразу к делу…

--Создаем таблицу для экспериментов с деревьями.
create table my_tree (
  id number,
  id_parent number,
  nm varchar2(100)
  );


--Добавляем нашей тестовой таблице индекс (для порядка).
create index idx_my_tree on my_tree(id,id_parent);

--Заполняем нашу тестовую таблицу классификатора (категориями продуктов).
insert into MY_TREE values (1,null, 'Продукты');
insert into MY_TREE values (2,1, 'Колбасы');
insert into MY_TREE values (3,1, 'Полуфабоикаты');
insert into MY_TREE values (4,1, 'Фрукты');
insert into MY_TREE values (5,3, 'Котлеты');
insert into MY_TREE values (6,3, 'Пельмени');
insert into MY_TREE values (7,3, 'Манты');
insert into MY_TREE values (8,3, 'Бифштексы');
insert into MY_TREE values (9,2, 'Копченая');
insert into MY_TREE values (10,2, 'Вареная');
insert into MY_TREE values (11,4, 'Помидоры');
insert into MY_TREE values (12,4, 'Огурцы');
insert into MY_TREE values (13,4, 'Бананы');
insert into MY_TREE values (14,2, 'Салями');


Читать дальше →

Создание пользователя (схемы) в Oracle

Пример простого создания пользователя (схемы) в БД Oracle:

--Создаем нового пользователя test с паролем 240580
create user test IDENTIFIED BY 240580
default tablespace users
temporary tablespace temp

--Добавляем квоту на дисковое пространство.
alter user test quota 100M on users;


--Разрещаем создавать сессии пользователю (подключаться)
grant create session to test


--Разрешаем создавать таблицы пользователю
grant create table to test


Читать дальше →

Узнаем какая сессия блокирует пакет

Чтобы узнать какая сессия блокирует пакет:
SELECT s.*,
       l.lock_type,
       l.mode_held,
       l.mode_requested,
       l.lock_id1,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM   dba_lock_internal l,
       v$session s
WHERE  s.sid = l.session_id
AND    UPPER(l.lock_id1) LIKE '%ПАКЕТ%'
AND    l.lock_type = 'Body Definition Lock'


P.S.
Правильная остановка Джобов:
declare
v_job_id binary_integer:=1;
begin
  dbms_job.broken(v_job_id,true);
  dbms_job.remove(v_job_id);
  commit;
end;