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" />
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"/>
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;
drop user elma3 cascade;
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
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;
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).