Java source compilation in Oracle

Its a pretty lame subject, I had very limited access to customers box and was not able to use my toys on Toad. Amazingly it took a while to get things in one place and there are a couple of problematic points along the way.

Abstract

A couple of Java source packages residing in Oracle. Need them transferred and compiled.

Compiling Java source in Oracle

ALTER JAVA SOURCE OWNER.JavaSource COMPILE;
ALTER JAVA CLASS OWNER.JavaClass RESOLVE;

After I tried to compile the sources, compile returned success, resolve though returned nothing. This means we have errors or dependencies. My Java source is called "ReceiveMail", name stands for itself..

Debugging Java source compile

Actually its very easy, you will easily find additional filters for your query. All_errors/user_errors/dba_errors tables dont have timestamp, but its not needed, they store recent errors (or warnings in case of Java source):

SELECT * FROM user_errors WHERE name = 'RECEIVEMAIL';

Compiling Java source in Oracle example

There is one more (un)expected twist, normally Oracle creates objects in UPPERCASE, just thats not the case for Java source. Standard is respected. This also led to a stupid one hour delay because I was not able to locate the source which was already compiled and valid.

ALTER JAVA SOURCE RECEIVEMAIL COMPILE;
ALTER JAVA CLASS "ReceiveMail" RESOLVE;

Dont forget the correct "ClassName" in correct case. The resolve command can actually also compile the source I did not performed a full scale analysis on when and how, but two other packages that I had got automatically compiled while I was playing with resolve command.

Use this select to see the state of you Java objects:

SELECT object_name, object_type, status FROM user_objects WHERE object_type like '%JAVA%';

You will see if there are any more filters needed, I only had 8 packages, so it was enough. After a successful compilation objects status changed to valid.

Extra grants

If you got this far and your sources are working, then thats it. I was missing one more extra grant for my schema which is called 'INVOICE', you must have appropriate permissions to run this and choose your own schema instead of 'INVOICE':

BEGIN
  dbms_java.grant_permission('INVOICE', 'SYS:java.util.PropertyPermission', '*', 'read,write' );
  dbms_java.grant_permission( 'INVOICE', 'SYS:java.net.SocketPermission', 'pop.server.com:*', 'accept, connect, resolve' );
END;

PL/SQL digits to words in Lithuanian

A PL/SQL function to convert numbers to words in Lithuanian, may be adjusted to your language rules. Maximum number 999999,99; decimal sign is ",". Execution example:

select EBA_OLS_DIG_TO_WORD('Lt 999999,99') from dual;

Result:

devyni šimtai devyniasdešimt devyni tūkstančiai devyni šimtai devyniasdešimt devyni Lt. 99 ct.

Code:

create or replace function "EBA_OLS_DIG_TO_WORD"
(num in VARCHAR2)
return VARCHAR2
is
word VARCHAR2(1022); -- result variable
work VARCHAR2(20); -- given number converted to char
k NUMBER := 0; -- length
n1 VARCHAR2(20) := 'vienas'; -- digits
n2 VARCHAR2(20) := 'du';
n3 VARCHAR2(20) := 'trys';
n4 VARCHAR2(20) := 'keturi';
n5 VARCHAR2(20) := 'penki';
n6 VARCHAR2(20) := 'šeši';
n7 VARCHAR2(20) := 'septyni';
n8 VARCHAR2(20) := 'aštuoni';
n9 VARCHAR2(20) := 'devyni';
n0 VARCHAR2(20) := 'dešimt'; -- tens and thousands
nh VARCHAR2(20) := 'šimtas';
nhd VARCHAR2(20) := 'šimtai';
nt VARCHAR2(20) := 'tūkstantis';
ntd VARCHAR2(20) := 'tūkstančiai';
ntu VARCHAR2(20) := 'tūkstančių';
ty VARCHAR2(20) := 'asdešimt'; -- endings
c VARCHAR2(1); -- loop digit value
d VARCHAR2(1); -- 4th digit in 5 digit number (for ...teen numbers)
n NUMBER := 0; -- number variable, for other use cases
suf VARCHAR2(6) := ''; -- suffix value for cents (hundreds of money)
skip4 BOOLEAN := false; -- flag for skipping 4th digit in 5 digit number
BEGIN
work := substr(num,4); -- prefix "LT_" (3 digits, adjust if needed)
k := length(work); -- length
n := instr(work,','); -- looking for decimal
if (n > 0) then
suf := substr(work, n+1, k-n+1); -- reading cents
if (length(suf)=1) then -- ,8 to ,80 correction
suf:=suf'0';
end if;
work := substr(work, 1, n-1); -- number corection, skipping cents
k := length(work); -- length adjustment
end if;
FOR i IN 1..k -- loop
LOOP
c:=substr(work,i,1); -- next digit
if (skip4 = false) then -- skip this part for 4th digit in 5 digit number
case c
when '1' then if ((k-i+1 != 2)and(k-i+1 != 5)and(skip4 = false)) then word:=wordn1; end if;
when '2' then if ((k-i+1 != 2)and(k-i+1 != 5)) then word:=wordn2; else word:=word'dvi'; end if;
when '3' then if ((k-i+1 != 2)and(k-i+1 != 5)) then word:=wordn3; end if;
when '4' then word:=wordn4; -- gramma mess with 1,2 and 3 digits
when '5' then word:=wordn5;
when '6' then word:=wordn6;
when '7' then word:=wordn7;
when '8' then word:=wordn8;
when '9' then word:=wordn9;
--when '0' then word:=wordn0;
else null;
end case;
end if;
word:=word' '; -- space btw words

case (k-i+1) -- lets take a digits position
when 6 then begin -- X*****
if (c = 1) then -- singular
word:=wordnh' ';
elsif (c = 0) then -- nothing
null;
else
word:=wordnhd' '; -- plural
end if;
end;
when 5 then begin -- *X**** (difficult digit, may have teen numbers)
if (c = 1) then -- if its teen number (1*)
d := substr(work,i+1,1); -- next digit in teen numbers
case d
when '0' then word:=wordn0;
when '1' then word:=word'vienuolika';
when '2' then word:=word'dvylika';
when '3' then word:=word'trylika';
when '4' then word:=word'keturiolika';
when '5' then word:=word'penkiolika';
when '6' then word:=word'šešiolika';
when '7' then word:=word'septyniolika';
when '8' then word:=word'aštuoniolika';
when '9' then word:=word'devyniolika';
else null;
end case;
skip4:=true; -- skip 4th digit on the next loop
--exit; -- dont exit the loop
elsif (c = 0) then -- if its zero, do nothing
null;
elsif (c = 2) then -- gramma for "2*"
word:=trim(word);
word:=wordn0' ';
elsif (c = 3) then -- gramma for "3*"
word:=word'tris'n0' ';
else -- default thounsands
word:=trim(word);
word:=wordty' ';
end if;
end;
when 4 then begin -- **X***
if (skip4=true) then -- plural on teen numbers in 5th & 4th place
word:=wordntu' ';
elsif (c = 1) then -- singular
word:=wordnt' ';
elsif (c = 0) then -- plural
word:=wordntu' ';
else -- plural
word:=wordntd' ';
end if;
skip4:=false; -- stop skipping 4th digit
end;
when 3 then begin -- ***X**
if (c = 1) then -- singular
word:=wordnh' ';
elsif (c = 0) then -- nothing
null;
else -- plural
word:=wordnhd' ';
end if;
end;
when 2 then begin -- ****X* (same problem as with 5th digit)
if (c = 1) then -- if its teen number (1*)
d := substr(work,i+1,1); -- next digit in teen numbers
case d
when '0' then word:=wordn0;
when '1' then word:=word'vienuolika';
when '2' then word:=word'dvylika';
when '3' then word:=word'trylika';
when '4' then word:=word'keturiolika';
when '5' then word:=word'penkiolika';
when '6' then word:=word'šešiolika';
when '7' then word:=word'septyniolika';
when '8' then word:=word'aštuoniolika';
when '9' then word:=word'devyniolika';
else null;
end case;
exit; -- no more digits left, exit loop
elsif (c = 0) then -- nothing
null;
elsif (c = 2) then -- gramma with "2*"
word:=trim(word);
word:=wordn0' ';
elsif (c = 3) then -- gramma with "3*"
word:=word'tris'n0' ';
else
word:=trim(word); -- others
word:=wordty' ';
end if;
end;
else null;
end case;
END LOOP;
word:=word' Lt.'; -- add currency if needed
if (n > 0) then -- condition for suffix numbers
--word:=word' 'EBA_OLS_DIG_TO_WORD('ct 'suf)' ct.'; -- convert cents to words if preffered
word:=word' 'suf' ct.'; -- dont convert cents
end if;
return word; -- return
END;