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

Karthikeyan Chidambaram

    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.

      • 1. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
        Mike Kutz

        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

        • 2. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
          Paulzip

          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;

          1 位用户发现它有用
          • 3. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
            Karthikeyan Chidambaram

            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.

            • 4. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
              Paulzip

              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.

              • 5. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                Solomon Yakobson

                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.

                1 位用户发现它有用
                • 6. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                  Karthikeyan Chidambaram

                  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.

                  • 7. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                    Karthikeyan Chidambaram

                    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.

                    • 8. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                      Paulzip

                      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.

                      • 9. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                        Solomon Yakobson

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

                         

                        SY.  

                        • 10. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                          Karthikeyan Chidambaram

                          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.

                          • 11. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                            jaramill

                            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.

                            • 12. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                              Karthikeyan Chidambaram

                              Hi Jaramill,

                               

                              Yes, i pasted it.

                               

                              Thanks and Regards,

                               

                              Karthik.

                              • 13. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                                Saubhik

                                You need to use advanced editor (check FAQ), to put the code in a formatted way. Re: 2. How do I ask a question on the forums? 

                                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;
                                
                                1 位用户发现它有用
                                • 14. Re: Unable to assign milliseconds value to an array of "TYPE"  "TABLE OF TIMESTAMP"
                                  Karthikeyan Chidambaram

                                  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.

                                  1 2 上一个 下一个