"PL/SQL: numeric or value error%s" - demos/ms_ews_...

[ravaia]

Dear mortenbra!

After I have installed the whole package to my schema, I have tried to run the demo script of ews pkg but unfortunately I am facing with errors after errors. Please help me with these:

Instance info:
18.4.0.0.0
XE

1. getfolder, getitem, finditem etc... I've got the following error:
   Error report -
   ORA-06502: PL/SQL: numeric- or value error ()
   ORA-06512: at "SYS.UTL_RAW", line 380.
   ORA-06512: at "R1.NTLM_UTIL_PKG", line 145.
   ORA-06512: at "R1.NTLM_UTIL_PKG", line 293.
   ORA-06512: at "R1.NTLM_HTTP_PKG", line 466.
   ORA-06512: at "R1.MS_EWS_UTIL_PKG", line 300.
   ORA-06512: at "R1.MS_EWS_UTIL_PKG", line 677.
   ORA-06512: at line 5.

6502. 00000 - "PL/SQL: numeric or value error%s"
      *Cause: An arithmetic, numeric, string, conversion, or constraint error
      occurred. For example, this error occurs if an attempt is made to
      assign the value NULL to a variable declared NOT NULL, or if an
      attempt is made to assign an integer larger than 99 to a variable
      declared NUMBER(2).
      *Action: Change the data, how it is manipulated, or how it is declared so
      that values do not violate constraints.

What I have called:
`
declare
l_folder ms_ews_util_pkg.t_folder;
begin
debug_pkg.debug_on;
ms_ews_util_pkg.init('https://xxx/EWS/Exchange.asmx', someone, 'secret', 'file://home/orbit/wallets', 'secret2');

l_folder := ms_ews_util_pkg.get_folder (ms_ews_util_pkg.g_folder_id_inbox);
debug_pkg.printf('folder id = %1, display name = %2', l_folder.folder_id, l_folder.display_name);
debug_pkg.printf('total count = %1', l_folder.total_count);
debug_pkg.printf('child folder count = %1', l_folder.child_folder_count);
debug_pkg.printf('unread count = %1', l_folder.unread_count);
end;
`

After I have inserted some dbms putline code into the pkg i have the following values may causing this error:
p_number=1 p_length=4
p_number=-1576488441 p_length=4
l_domain_str=
p_number=5 p_length=2
p_number=5 p_length=2
p_number=40 p_length=4
p_number= p_length=2

This is where the error message points:
l_returnvalue := utl_raw.substr(utl_raw.cast_from_binary_integer(p_number, utl_raw.little_endian), 1, p_length);

--

2. After I have installed the package I have found these types created, can you tell me what these are for?
   SYS_PLSQL_9E442D16_45_1
   SYS_PLSQL_9E442D16_73_1
   SYS_PLSQL_9E442D16_DUMMY_1
   SYS_PLSQL_EFCB9540_104_1
   SYS_PLSQL_EFCB9540_150_1
   SYS_PLSQL_EFCB9540_158_1
   SYS_PLSQL_EFCB9540_340_1
   SYS_PLSQL_EFCB9540_348_1
   SYS_PLSQL_EFCB9540_410_1
   SYS_PLSQL_EFCB9540_47_1
   SYS_PLSQL_EFCB9540_9_1
   SYS_PLSQL_EFCB9540_96_1
   SYS_PLSQL_EFCB9540_DUMMY_1

Thank you for your help and time!

Regards, Andras

[ravaia]

I'm the monkey: I haven't given the DOMAIN to the username (CORRECT=DOMAIN\USER) (WRONG=USER)

Sorry for the interrupt, but I'm still curious about the types...

[mortenbra]

Hi Ravaia, regarding the types: When types used for pipelined functions are defined in a PL/SQL package specification (for exampe, "type t_folder_tab is table of t_folder;"), then Oracle automatically creates some schema-level types to represent these package types. You can see that Oracle created these because they are prefixed with "SYS". If you drop the package, these schema-level types also get dropped automatically.

In this article these types are called "Implicit" or "Shadow" types: https://oracle-base.com/articles/misc/pipelined-table-functions#implicit_types

Unlike the author of that article, I don't see any problem by having Oracle automatically handle the underlying types. I find it more convenient and self-documenting to define the types in the package along with the code which uses the types.