Forum Stats

  • 3,735,212 Users
  • 2,247,145 Discussions
  • 7,857,783 Comments

Discussions

Unable to assign milliseconds value to an array of "TYPE" "TABLE OF TIMESTAMP"

Karthikeyan Chidambaram
Karthikeyan Chidambaram Member Posts: 9
edited Jul 31, 2020 5:47PM in SQL & PL/SQL

Hi Sir/Mm,

1)

I have this particular value assignment code snippet in a stored procedure :

dbms_sql_dateArr  ( x ) := dateArr (lDateIndex);

2)

Both these are of the same declaration type and type.

Please find below the declarations of the above variables and their "TYPE" :

2.1)

Declarations inside the Stored procedure :

dbms_sql_dateArr   DBMS_SQL.date_table

dateArr            date_table;

(Note: Addition DBMS_SQL turned out to be mandatory)

2.2)

Script of the original declaration type :

create or replace

TYPE                  "DATE_TABLE" IS TABLE OF TIMESTAMP(6) WITH TIME ZONE

Now my issue is that "dateArr (lDateIndex);" in point 1) has a timestamp value along with milliseconds.

But "dbms_sql_dateArr  ( x ) :" gets stripped of the milliseconds value.

Could you please help me as i could not find answers in the forum?

Regards,

Karthik.

TubbyKarthikeyan ChidambaramjaramillPaulzip

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,328 Blue Diamond
    edited Jul 30, 2020 10:39AM Accepted Answer

          for x in 1 .. prowcount

          loop

            datearr          := datearr_2d(datedataindexarr(x));

            dbms_sql_datearr := datearr(ldateindex); --        / / Conversion           issue

          end loop;

          dbms_sql.bind_array(l_cursor, ':COL_' || lindex, dbms_sql_datearr);

    dbms_sql_datearr is a dbms_sql.date_table, so it can't preserve milliseconds in the bind.

    Change

    dbms_sql_datearr dbms_sql.date_table;

    to

    dbms_sql_tswtz DBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLE;

    and references of dbms_sql_datearr to dbms_sql_tswtz

    Karthikeyan ChidambaramKarthikeyan Chidambaram

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown
    edited Jul 29, 2020 12:48PM

    After reading the fine manual on DBMS_SQL:

    Syntax (for DBMS_SQL.DATE_TABLE)

    type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER; 

    The DATE data type does not hold "milliseconds".

    Instead, use one of the TIMESTAMP table types.

    MK

    Tubby
  • Paulzip
    Paulzip Member Posts: 8,328 Blue Diamond
    edited Jul 29, 2020 1:28PM

    Date type does not have millisecond resolution, it only goes to second ranges.  You'll need a timestamp type :

    So, perhaps change DBMS_SQL.DATE_TABLE to DBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLE;

    Karthikeyan ChidambaramKarthikeyan Chidambaram
  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 30, 2020 8:24AM

    Hi Paulzip,

    I tried all the following, but milliseconds are not retained.

    1)

    create or replace

    TYPE                  "DATE_TABLE" IS TABLE OF TIMESTAMP_UNCONSTRAINED--timestamp_table (neither the caps nor or the small cases)

    create or replace

    TYPE                  "DATE_TABLE" IS TABLE OF TIMESTAMP_LTZ_UNCONSTRAINED--timestamp_with_ltz_table (neither the caps nor or the small cases)

    create or replace

    TYPE                  "DATE_TABLE" IS TABLE OF TIMESTAMP_TZ_UNCONSTRAINED--timestamp_with_time_zone_Table (neither the caps nor or the small cases)

    Below are the initial declarations of the TYPE taken from manual's hyperlink :

    (Though I had to comment out the index by clause or else it gives compilation error)

    2)

    create or replace

    TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED --INDEX BY BINARY_INTEGER;

    create or replace

    TYPE timestamp_with_ltz_table IS TABLE OF

        TIMESTAMP_LTZ_UNCONSTRAINED --INDEX BY binary_integer;

    create or replace

    TYPE timestamp_with_time_zone_Table IS TABLE OF

        TIMESTAMP_TZ_UNCONSTRAINED --INDEX BY binary_integer;

    There were two more in the manual, TIME_UNCONSTRAINED and TIME_TZ_UNCONSTRAINED but these did allow my Stored procedure to compile.

    What could be the issue ?

    Thanks and Regards,

    Karthik.

  • Paulzip
    Paulzip Member Posts: 8,328 Blue Diamond
    edited Jul 30, 2020 9:05AM

    The milliseconds could be a display issue, rather than a storage issue.

    You'll need to show your code.  I have zero context to work with, so would be guessing what the problem is.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,411 Black Diamond
    edited Jul 30, 2020 9:33AM

    First of all dates in Oracle have no fractional seconds - timestamps do. So calling table of timestamps type DATE_TABLE is misleading. Secondly, what do you mean by "milliseconds are not retained"? If you mean milliseconds are always zero then you most likely assign DATE to TIMESTAMP:

    DECLARE

        V_TS TIMESTAMP_UNCONSTRAINED;

    BEGIN

        V_TS := SYSDATE; -- SYSDATE is a date, so no milliseconds

        DBMS_OUTPUT.PUT_LINE('             SYSDATE is a date, so no milliseconds: ' || V_TS);

        V_TS := SYSTIMESTAMP; -- SYSTIMESTAMP is a timestamp so it has milliseconds

        DBMS_OUTPUT.PUT_LINE('SYSTIMESTAMP is a timestamp so it has milliseconds: ' || V_TS);

    END;

    /

                 SYSDATE is a date, so no milliseconds: 30-JUL-20 09.30.22.000000000 AM

    SYSTIMESTAMP is a timestamp so it has milliseconds: 30-JUL-20 09.30.22.483000000 AM

    PL/SQL procedure successfully completed.

    SQL>

    So clarify "milliseconds are not retained" and show your code.

    SY.

    Karthikeyan ChidambaramKarthikeyan Chidambaram
  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 30, 2020 9:52AM

    Hi Solomon,

    There is no option to attach text files in this post, is there any way i can send it ?

    Thanks and Regards,

    Karthik.

  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 30, 2020 9:54AM

    Hi Paul,

    1) Well the milliseconds is not stored either.

    Table column data type > "TXN_PAYMENT_EXP_DATE                    TIMESTAMP(6)"

    Table where value is stored.jpg

    (I had also tried "TIMESTAMP(6)  WITH TIME ZONE")

    There is no option to attach script files in this post. How do i send it across?

    Thanks and Regards,

    Karthik.

  • Paulzip
    Paulzip Member Posts: 8,328 Blue Diamond
    edited Jul 30, 2020 9:59AM

    It still doesn't help us.  Something in your code seems to be truncating the milliseconds, this is probably because it is going through a date type at some point, but as you refuse to post the code it's guesswork.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,411 Black Diamond
    edited Jul 30, 2020 10:08AM

    Garbage in - garbage out. If code that populates column TXN_PAYMENT_EXP_DATE doesn't provide milliseconds then youdon't have them...

    SY.  

  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 30, 2020 10:16AM

    Hi Paul,

    I am very sorry, i thought of attaching the files to avoid cluttering.

    1) So, here is my stored procedure, conversion loss is here in this line during assignment to left side > "Conversion issue"

    (Editor has replaced ( x ) with an emoji)

    --

    create or replace

    PROCEDURE INSERT_TRANSACTION_DATA (

       dataTypeArr             IN     STR5_TYPE,

       strArr_2D               IN     VARCHAR2_TABLE_2D,

       numArr_2D               IN     NUMBER_TABLE_2D,

       dateArr_2D              IN     DATE_TABLE_2D,

       pWorkItem_Arr                  DBMS_SQL.number_table,

       pColCount               IN     NUMBER,

       pRowCount               IN     NUMBER,

       pQueryStr               IN     VARCHAR2,

       pAuditQuery             IN     VARCHAR2,

       dbms_sql_MakerArr              DBMS_SQL.varchar2_table,

       dbms_sql_ItemTypeArr           DBMS_SQL.number_table,

       dbms_sql_QueueIdArr            DBMS_SQL.number_table,

       ARR_OperationId         IN     DBMS_SQL.number_table,

       ARR_ReferenceNumber     IN     DBMS_SQL.varchar2_table,

       pUpdateFlag             IN     VARCHAR2,

       REDSULT_VAL                OUT VARCHAR2,

       StrDataIndexArr                DBMS_SQL.number_table,

       NumDataIndexArr                DBMS_SQL.number_table,

       DATEDATAINDEXARR               DBMS_SQL.NUMBER_TABLE,

    -- dbms_sql_makerdateArr   IN     DBMS_SQL.Date_Table,

       dbms_sql_makerdateArr   IN     DBMS_SQL.timestamp_table,

       PRMTABLEFLAG            IN     varchar2)

    AS

       lStrIndex          NUMBER := 0;

       lNumIndex          NUMBER := 0;

       lDateIndex         NUMBER := 0;

       lIndex             NUMBER := 0;

       lDataType          VARCHAR2 (5);

       numArr             number_table;

       dateArr            date_table;

       strArr             varchar2_table;

       i                  NUMBER;

       l_cursor           NUMBER;

       sampledate         timestamp;--Added for HUBSPS-455

       dbms_sql_numArr    DBMS_SQL.number_table;

       dbms_sql_dateArr   DBMS_SQL.date_table;--Added for HUBSPS-455

       dbms_sql_strArr    DBMS_SQL.varchar2_table;

       l_status           NUMBER;

    BEGIN

       DBMS_OUTPUT.put_line ('Inside INSERT_TRANSACTION_DATA');

       l_cursor := DBMS_SQL.open_cursor;

       DBMS_OUTPUT.put_line ('cursor open');

       DBMS_SQL.parse (l_cursor, pQueryStr, DBMS_SQL.native);

       FOR lIndex IN 1 .. pColCount

       LOOP

          lDataType := dataTypeArr (lIndex);

          IF lDataType = 'S'

          THEN

             lStrIndex := lStrIndex + 1;

             --   strArr := strArr_2D(lStrIndex);

             FOR x IN 1 .. pRowCount

             LOOP

                strArr := strArr_2D (StrDataIndexArr (x));

                dbms_sql_strArr := strArr (lStrIndex);

             END LOOP;

             DBMS_SQL.bind_ARRAY (l_cursor, ':COL_' || lIndex, dbms_sql_strArr);

          ELSIF lDataType = 'N'

          THEN

             lNumIndex := lNumIndex + 1;

             --   numArr := numArr_2D(lNumIndex);

             FOR x IN 1 .. pRowCount

             LOOP

                numArr := numArr_2D (NumDataIndexArr (x));

                dbms_sql_numArr := numArr (lNumIndex);

             END LOOP;

             DBMS_SQL.bind_ARRAY (l_cursor, ':COL_' || lIndex, dbms_sql_numArr);

          ELSIF lDataType = 'D'

          THEN

             lDateIndex := lDateIndex + 1;

             --   dateArr := dateArr_2D(lDateIndex);

             --dateArr := dateArr_2D(DateDataIndexArr(lDateIndex));

             FOR x IN 1 .. pRowCount

             LOOP

                dateArr := dateArr_2D (DateDataIndexArr (x));

                dbms_sql_dateArr := dateArr (lDateIndex);//Conversion issue

               

             END LOOP;

             DBMS_SQL.bind_ARRAY (l_cursor, ':COL_' || lIndex, dbms_sql_dateArr);

          END IF;

       END LOOP;

       DBMS_SQL.bind_ARRAY (l_cursor, ':QID', dbms_sql_QueueIdArr);

       DBMS_SQL.bind_ARRAY (l_cursor, ':WI', pWorkItem_Arr);

       DBMS_SQL.bind_ARRAY (l_cursor, ':MAKER', dbms_sql_MakerArr);

       -- dbms_sql.bind_ARRAY( l_cursor, ':PROD' , dbms_sql_ItemTypeArr); Changes for 0 product id getting stamped

       IF PRMTABLEFLAG = 'Y'

       then

        DBMS_SQL.bind_ARRAY (l_cursor, ':MAKERDATE', dbms_sql_MakerDateArr);

        END IF;

       IF pUpdateFlag = 'N'

       THEN

          DBMS_SQL.bind_ARRAY (l_cursor, ':PROD', dbms_sql_ItemTypeArr); -- Changes for 0 product id getting stamped

          DBMS_SQL.bind_ARRAY (l_cursor, ':OPERATION_ID', ARR_OperationId);

          DBMS_SQL.bind_ARRAY (l_cursor, ':REFERENCE_NUM', ARR_ReferenceNumber);

       END IF;

       l_status := DBMS_SQL.execute (l_cursor);

       DBMS_SQL.close_cursor (l_cursor);

        IF( length(pAuditQuery)>10) THEN

    l_cursor := dbms_sql.open_cursor;

    dbms_output.put_line('cursor open');

    dbms_sql.parse( l_cursor, pAuditQuery, dbms_sql.native );

    dbms_sql.bind_ARRAY( l_cursor, ':WI' , pWorkItem_Arr);

    l_status := dbms_sql.execute( l_cursor );

    dbms_sql.close_cursor(l_cursor);

        END IF;

       REDSULT_VAL := 'success';

    END INSERT_TRANSACTION_DATA;

    --

    2) Supporting Supporting scripts :

    --

    create or replace

    TYPE                  "DATE_TABLE" IS TABLE OF TIMESTAMP_TZ_UNCONSTRAINED--timestamp_with_time_zone_Table

    create or replace

    TYPE            "DATE_TABLE_2D" is table of Date_Table

    create or replace

    TYPE timestamp_with_time_zone_Table IS TABLE OF

        TIMESTAMP_TZ_UNCONSTRAINED --INDEX BY binary_integer;

    create or replace

    TYPE            "NUMBER_TABLE" IS TABLE OF NUMBER

    --

    3)

    Table column where milliseconds is lost during insersion > "TXN_PAYMENT_EXP_DATE                    TIMESTAMP(6)"

    (I had also tried "TIMESTAMP(6)  WITH TIME ZONE")

    Table where value is stored.jpg

    Thanks and Regards,

    Karthik.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Jul 30, 2020 10:20AM
    8a5b0006-ace9-4702-b057-82e20ee4a199 wrote:Hi Solomon,There is no option to attach text files in this post, is there any way i can send it ?Thanks and Regards,Karthik.

    No one gives out their personal e-mail address here to avoid spam.

    No one downloads files here to avoid spam.

    Post some of the contents of the text file here.

  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 30, 2020 10:24AM

    Hi Jaramill,

    Yes, i pasted it.

    Thanks and Regards,

    Karthik.

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Jul 30, 2020 10:25AM

    You need to use advanced editor (check FAQ), to put the code in a formatted way.  

    CREATE OR replace PROCEDURE insert_transaction_data (    datatypearr             IN    str5_type,    strarr_2d               IN    varchar2_table_2d,    numarr_2d               IN    number_table_2d,    datearr_2d              IN    date_table_2d,    pworkitem_arr           dbms_sql.number_table,    pcolcount               IN    NUMBER,    prowcount               IN    NUMBER,    pquerystr               IN    VARCHAR2,    pauditquery             IN    VARCHAR2,    dbms_sql_makerarr       dbms_sql.varchar2_table,    dbms_sql_itemtypearr    dbms_sql.number_table,    dbms_sql_queueidarr     dbms_sql.number_table,    arr_operationid         IN    dbms_sql.number_table,    arr_referencenumber     IN    dbms_sql.varchar2_table,    pupdateflag             IN    VARCHAR2,    redsult_val             OUT   VARCHAR2,    strdataindexarr         dbms_sql.number_table,    numdataindexarr         dbms_sql.number_table,    datedataindexarr        dbms_sql.number_table,-- dbms_sql_makerdateArr   IN     DBMS_SQL.Date_Table,    dbms_sql_makerdatearr   IN    dbms_sql.timestamp_table,    prmtableflag            IN    VARCHAR2) AS    lstrindex          NUMBER := 0;    lnumindex          NUMBER := 0;    ldateindex         NUMBER := 0;    lindex             NUMBER := 0;    ldatatype          VARCHAR2(5);    numarr             number_table;    datearr            date_table;    strarr             varchar2_table;    i                  NUMBER;    l_cursor           NUMBER;    sampledate         TIMESTAMP;--Added for HUBSPS-455    dbms_sql_numarr    dbms_sql.number_table;    dbms_sql_datearr   dbms_sql.date_table;--Added for HUBSPS-455    dbms_sql_strarr    dbms_sql.varchar2_table;    l_status           NUMBER;BEGIN    dbms_output.put_line('Inside INSERT_TRANSACTION_DATA');    l_cursor := dbms_sql.open_cursor;    dbms_output.put_line('cursor open');    dbms_sql.parse(l_cursor, pquerystr, dbms_sql.native);FOR lindex IN 1..pcolcount LOOP ldatatype := datatypearr(lindex);IF ldatatype = 'S'      THEN    lstrindex := lstrindex + 1;         --   strArr := strArr_2D(lStrIndex);    FOR x IN 1..prowcount LOOP        strarr := strarr_2d(strdataindexarr(x));        dbms_sql_strarr (x) := strarr(lstrindex);    END LOOP;dbms_sql.bind_array(l_cursor, ':COL_' || lindex, dbms_sql_strarr);ELSIF ldatatype = 'N' THEN    lnumindex := lnumindex + 1;         --   numArr := numArr_2D(lNumIndex);    FOR x IN 1..prowcount LOOP        numarr := numarr_2d(numdataindexarr(x));        dbms_sql_numarr (x) := numarr(lnumindex);    END LOOP;    dbms_sql.bind_array(l_cursor, ':COL_' || lindex, dbms_sql_numarr);ELSIF ldatatype = 'D' THEN    ldateindex := ldateindex + 1;         --   dateArr := dateArr_2D(lDateIndex);         --dateArr := dateArr_2D(DateDataIndexArr(lDateIndex));FOR x IN 1..prowcount         LOOP    datearr := datearr_2d(datedataindexarr(x));    dbms_sql_datearr (x) := datearr(ldateindex);//Conversion issue END    loop;    dbms_sql.bind_array(l_cursor, ':COL_' || lindex, dbms_sql_datearr);END IF;   END    loop;    dbms_sql.bind_array(l_cursor, ':QID', dbms_sql_queueidarr);    dbms_sql.bind_array(l_cursor, ':WI', pworkitem_arr);    dbms_sql.bind_array(l_cursor, ':MAKER', dbms_sql_makerarr);   -- dbms_sql.bind_ARRAY( l_cursor, ':PROD' , dbms_sql_ItemTypeArr); Changes for 0 product id getting stamped    IF prmtableflag = 'Y' THEN        dbms_sql.bind_array(l_cursor, ':MAKERDATE', dbms_sql_makerdatearr);    END IF;    IF pupdateflag = 'N' THEN        dbms_sql.bind_array(l_cursor, ':PROD', dbms_sql_itemtypearr); -- Changes for 0 product id getting stamped        dbms_sql.bind_array(l_cursor, ':OPERATION_ID', arr_operationid);        dbms_sql.bind_array(l_cursor, ':REFERENCE_NUM', arr_referencenumber);    END IF;    l_status := dbms_sql.execute(l_cursor);    dbms_sql.close_cursor(l_cursor);    IF ( length(pauditquery) > 10 ) THEN        l_cursor := dbms_sql.open_cursor;        dbms_output.put_line('cursor open');        dbms_sql.parse(l_cursor, pauditquery, dbms_sql.native);        dbms_sql.bind_array(l_cursor, ':WI', pworkitem_arr);        l_status := dbms_sql.execute(l_cursor);        dbms_sql.close_cursor(l_cursor);    END IF;    redsult_val := 'success';END insert_transaction_data;
    Karthikeyan Chidambaram
  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 30, 2020 10:27AM

    Hi Solomon,

    I have confirmed that i have passed in milliseconds and it got printed too.

    But is lost during reassignment where i have mentioned.

    Regards,

    Karthik.

  • Paulzip
    Paulzip Member Posts: 8,328 Blue Diamond
    edited Jul 30, 2020 10:39AM Accepted Answer

          for x in 1 .. prowcount

          loop

            datearr          := datearr_2d(datedataindexarr(x));

            dbms_sql_datearr := datearr(ldateindex); --        / / Conversion           issue

          end loop;

          dbms_sql.bind_array(l_cursor, ':COL_' || lindex, dbms_sql_datearr);

    dbms_sql_datearr is a dbms_sql.date_table, so it can't preserve milliseconds in the bind.

    Change

    dbms_sql_datearr dbms_sql.date_table;

    to

    dbms_sql_tswtz DBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLE;

    and references of dbms_sql_datearr to dbms_sql_tswtz

    Karthikeyan ChidambaramKarthikeyan Chidambaram
  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 30, 2020 4:09PM

    Hi Paul,

    Sorry for this delayed reply.

    I am extremely grateful to you for your precise understanding, prompt responses and solution.

    All these worked.

    dateArr            date_table;

    dateArr := dateArr_2D (DateDataIndexArr (x));

    dbms_sql_tswtz ( x ) := dateArr (lDateIndex);

    dbms_sql_tswtz DBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLE;

    create or replace TYPE                  "DATE_TABLE" IS TABLE OF TIMESTAMP(9) WITH TIME ZONE

    create or replace TYPE timestamp_with_time_zone_Table IS TABLE OF     TIMESTAMP_TZ_UNCONSTRAINED

    The relation between the above Right hand side and Left hand side declarations was not very intuitive and i couldn't figure out which of the declarations provided in the manual link were correct, but you provided me the exact.

    (There is no better way unfortunately to be grateful to you than this reply.)

    Thanks you very very much and Regards,

    Yours Truly,

    Karthik.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Jul 30, 2020 4:18PM

    Then to show your gratitude to Paul, mark his reply as the correct ANSWER to the thread.

  • Karthikeyan Chidambaram
    Karthikeyan Chidambaram Member Posts: 9
    edited Jul 31, 2020 2:08PM

    Hi Jaramill,

    I marked Paul's last answer as correct.

    It now shows as Correct Answer

    Thanks and Regards,

    Karthik.

    jaramill
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Jul 31, 2020 5:47PM
    Karthikeyan Chidambaram wrote:Hi Jaramill,I marked Paul's last answer as correct.It now shows as Correct AnswerThanks and Regards,Karthik.

    This helps the community overall.

    Thanks

    Paulzip
Sign In or Register to comment.