BUG : timestamp wtih timezone

Ja Steam
2013-10-07
2013-10-13
  • Ja Steam
    Ja Steam
    2013-10-07

    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 (
    ID number,
    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');
    COMMIT;
    
    DECLARE
    obj json;
    ret json_list;
    
    BEGIN
    obj := json();
    ret := json_dyn.executeList('select id, to_char(log_entry,''YYYY-MM-DD HH24:MI:SS TZH'') from my_log_table');
    ret.print;
    END;
    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"
    *Cause:   
    *Action:
    

    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.

    Jan S.

     
    Last edit: Ja Steam 2013-10-07
  • Hi Jan,

    Right now I don't have the time to maintain pljson.
    If anyone is interested in becoming a maintainer then let me know.

    /Jonas

     
  • James Sumners
    James Sumners
    2013-10-08

    Hmmm, I could give it a go.

     
  • Ja Steam
    Ja Steam
    2013-10-13

    Hello James,

    Sorry for the tardif response.
    I'm confirming the patch fixed the date time issue.
    This workaround is also confirmed on new version 11.2.0.4.

    Thanks for the awesome help. Any place I can donate for your assistance with this bug?

    Jan S.