Hello Jonas & ALL,
I really need some help. I've got a need to get timestamp with time zone but I get an error(below). I've used to an alter session to change the timestamp format (in short I truncate off the miliseconds) and manage to generate a timestamp value, but not with time zone. Problem seems to also apply to TIMESTAMP WITH LOCAL TIME ZONE. Here is and example:
CREATE TABLE MY_LOG_TABLE (
LOG_ENTRY TIMESTAMP WITH TIME ZONE
INSERT INTO MY_LOG_TABLE(1,TIMESTAMP '2013-10-06 09:25:30.44 +0200');
INSERT INTO MY_LOG_TABLE(2,TIMESTAMP '2013-10-06 09:26:25.55 -0200');
INSERT INTO MY_LOG_TABLE(3,TIMESTAMP '2013-10-06 09:27:16.34 +0500');
obj := json();
ret := json_dyn.executeList('select id, to_char(log_entry,''YYYY-MM-DD HH24:MI:SS TZH'') from my_log_table');
Error report -
ORA-06502: PL/SQL: numeric or value error: dbms_sql.describe_columns overflow, col_name_len=44. Use describe_columns2
ORA-06512: at "SYS.DBMS_SQL", line 2251
ORA-06512: at "SYS.JSON_DYN", line 64
ORA-06512: at line 7
06502. 00000 - "PL/SQL: numeric or value error%s"
I've also noticed that as soon as the column is of data type timestamp with timzone, JSON_DYN do not produce and error, but also does NOT include the column in the JSON output.
Thanks in advance for assistance.
Right now I don't have the time to maintain pljson.
If anyone is interested in becoming a maintainer then let me know.
Hmmm, I could give it a go.
The fix for this problem is simple: change line 46 of json_dyn (body) to "l_dtbl dbms_sql.desc_tab2;" and line 65 to "dbms_sql.describe_columns2(l_cur, l_cnt, l_dtbl);"
Patch available at https://github.com/jsumners/pljson/commit/7cc11c204b873023c9b8b443083ac4983c613493.patch
Sorry for the tardif response.
I'm confirming the patch fixed the date time issue.
This workaround is also confirmed on new version 188.8.131.52.
Thanks for the awesome help. Any place I can donate for your assistance with this bug?