[ELMA3] Особенности разработки для Oracle

Программы и настройки

Oracle SQL Developer.
Для ELMA в папке bin сервера должны лежать библиотеки из Libs\Oracle.
Правильно настроенная конфигурация. Для сохранения и загрузки настроек используется класс OracleSettingsSection, соответсвено, в конфиге нужно его указать вместо базового.

1	`<section name="main"` `type="EleWise.ELMA.Extensions.Oracle.OracleSettingsSection, EleWise.ELMA.Extensions.Oracle"` `/>`

4. Нужна правильная строка подключения к БД.
5. В настройке провайдера добавилось 2 новых свойства: sysUser и sysPassword. Это логин и пароль глобального администратора на Oracle сервере.

1	`<main` `connectionStringName="MainDB"` `type="EleWise.ELMA.Extensions.Oracle.OracleProvider, EleWise.ELMA.Extensions.Oracle"` `backupEnabled="false"` `sysUser="login"` `sysPassword="password"/>`

В Oracle своеобразная структура баз. По сравнению с MS SQL и Firebird, где есть понятие БД (с алиасом), его файл и отдельно пользователи к ней, в Oracle БД = пользователь. То есть когда мы в строке соединения прописываем под каким пользователем подключаться, мы автоматом определяем к какой базе на сервере конектиться. Имя пользователя должно начинаться с префикса c##. Параметры sysUser и sysPassword не обязательны, но если они не заданы и БД еще не создана, то придется сделать это вручную. Скрипт создания базы и раздачи нужных прав на схему:

create user c##login identified by password;
grant all PRIVILEGES to c##login ;
GRANT SELECT ON  "SYS"."GV_$RESERVED_WORDS" TO c##login;
GRANT SELECT ON  "SYS"."V_$RESERVED_WORDS" TO c##login;

Для удаления базы используется команда:

1	`drop` `user` `elma3` `cascade;`

SQL-запросы

При разработке нужно всегда помнить, что в Oracle около 1200 ключевых слов. Каждое ключевое слово в запросах должно быть взято в кавычки. При этом нужно помнить, что такие названия в кавычках становятся регистрозависимыми. К примеру, UID и "Uid" это две разные колонки для Oracle. Список ключевых слов хранится прямо в базе, можно получить их запросом:

1	`select` `keyword` `from` `sys.v_$reserved_words`

Хранимые процедуры

Так просто в Oracle табличку из функции не вернуть. Лучше всего это делать через кастомные типы. Для того, чтобы вернуть табличку, нужно создать тип для строки и тип для таблички:

CREATE TYPE NOMENCLATURETREE_row as object
        ( "Id" integer
        , "Name" nvarchar2(2000)
        , PARENTGROUP integer
        , LinkId integer
        , nodetypeuid raw(16));
 
CREATE TYPE NOMENCLATURETREE_tbl as table of NOMENCLATURETREE_row;

После этого можно писать процедуру, которая возвращает результат типа таблица:

create or replace function NOMENCLATURETREE
(p_YEARNOMEN integer)
RETURN NOMENCLATURETREE_tbl PIPELINED
is
    temp NOMENCLATURETREE_row;
begin
    temp := NOMENCLATURETREE_row(null,null,null,null,null);
    FOR i IN (
        select
            dg."Id" as "Id",
            case
                when (rpi."Id" is not null) then rp."Name"
                when (dl."Id" is not null) then dr2."Name"
                else dg."Name"
            end as "Name", 
            dg.parentgroup, 
            case
              when (dl."Id" is not null) then dl.DEPOSITORY
            else dg."Id" end as LinkId, 
            dg.typeuid as nodetypeuid  
        from depositorygroup dg
            left join depositorygroup dr on dr."Id" = dg."Id"
            left join registrationplaceitem rpi on rpi."Id" = dr."Id"
            left join REGISTRATIONPLACE rp on rp."Id" = rpi.registrationplace
            left join depositorylink dl on dl."Id" = dr."Id"
            left join depositorygroup dr2 on dl.depository = dr2."Id"
        start with 1=1
            and dg.parentgroup is null
            and exists
            (select 1 from registrationplaceitem regpli, accountingyear ay
            where 1=1
            and dg."Id" = regpli."Id"
            and ay."Id" = regpli.ACCOUNTINGYEAR
            and ay."Year" = p_YEARNOMEN)
        connect by nocycle prior dg."Id" = dg.parentgroup
    )
    LOOP
        temp."Id" := i."Id";
        temp."Name" := i."Name";
        temp.PARENTGROUP := i.PARENTGROUP;
        temp.LinkId := i.LinkId;
        temp.nodetypeuid := i.nodetypeuid;
        PIPE ROW (temp);
    END LOOP;
    RETURN;
end NOMENCLATURETREE;

Рекурсивные процедуры

Oracle поддерживает стандарт по рекурсивным запросам только с 11-ой версии. У нас должно еще работать и на 10-ой. До 11 версии в Oracle существовал только свой синтаксис (через CONNECT BY). Подробнее написано здесь (глава про Oracle).