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

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.

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.

Hi Paul,

1) Well the milliseconds is not stored either.

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

(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.

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")

Thanks and Regards,

Karthik.

Hi Jaramill,

Yes, i pasted it.

Thanks and Regards,

Karthik.

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.