Specifics of developing for Oracle

Programs and settings

2) For ELMA, the bin folder of the server must contain libraries from Libs\Oracle

3) A correctly set up configuration. To save and load settings, the OracleSettingsSection class is used, therefore it should be specified in the config instead of the base class.

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

4) A correct database connection string is required
5) In the provider setting, two new properties were added - sysUser and sysPassword. These are login and password of the global administrator of the Oracle server.

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

The structure of databases is specific in Oracle. Compared to MS SQL and Firebird, which have the concept of a database (with an alias), its file and users, in Oracle, database = user. I.e. when you specify a user in the connection string, you automatically define to which database to connect. The sysUser and sysPassword parameters are optional, and if they are not specified and a database is not created yet, you will need to do it manually. A script for creating a database and granting access to the scheme:

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

To delete a database, use the command:

drop user elma3 cascade;

SQL queries

When developing, keep in mind that there are about 1200 keywords in Oracle. Each keyword must be enclosed in quotation marks in queries. Such names in quotation marks become case-sensitive. For example, UID and Uid are two different columns for Oracle. The list of keywords is stored in the database, you can get it with this query:

select keyword from sys.v_$reserved_words

Stored procedures

You cannot simply return a table from a function in Oracle. It is best to use custom types for that. To return a table, create a type for a string and a type for a table:

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;

After that you can write a procedure that returns a result of the table type:

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;

Recursive procedures

Oracle supports the standard on recursive queries starting with version 11. It should work with version 10 for us. Before the version 11, Oracle had only its own syntax (via CONNECT BY).