search

mortenbra / alexandria-plsql-utils

Oracle PL/SQL Utility Library
778 stars 316 forks source link

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

Open ravaia opened 4 years ago

ravaia commented 4 years ago

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.

  1. 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 commented 4 years ago

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 commented 4 years ago

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.