3 Replies Latest reply on Jun 18, 2019 7:46 AM by MansiRaval

    how to get comma seperated values of a column from a cursor

    Amrit Kaur Sachdeva-Oracle

      Hi All,

       

      I am having a cursor and then fetching the cursor in a table and then using forall inserting the rows in table depending on what values are there in the table which was fetched from cursor.After this I am creating a dynamic query for this dynamic query where wo_operation_id should be all values which were there in cursor collected in table.

       

      Below is my code here I am opening cursor and then fetching the cursor in a table  collection and then using forall and inserting the rows in table

       

        OPEN create_cur;

          LOOP

            FETCH create_cur bulk collect  INTO crt_opr_intf_tbl ;

            EXIT

          WHEN crt_opr_intf_tbl.count = 0;

            --insert into base table

            forall I IN 1..crt_opr_intf_tbl.count

            INSERT

            INTO mth_wo_operations_b

              (

                wo_operation_id,

                object_version_number ,

                organization_id ,

                work_order_id

              )

              VALUES

              (

                crt_opr_intf_tbl(i).wo_operation_id,

                1 ,

                crt_opr_intf_tbl(i).organization_id ,

                crt_opr_intf_tbl(i).work_order_id

              );

       

      After this I want to create a dynamic query which will insert records in table mth_wo_operations_b1 opr1 where opr1.wo_operation_id  should be the values which are in   crt_opr_intf_tbl(i).wo_operation_id

      I am not getting how to get all the values of wo_operation_id which are gtting inserted in mth_wo_operations_b table. Suppose insert query inserts records with wo_operation_id as 10,20,30 then I want the sql to give me 10,20,30

      I tried below code

      col_list := null;

                 forall I IN 1..crt_opr_intf_tbl.count

                

                 col_list := col_list || ',' ||  crt_opr_intf_tbl(i).wo_operation_id;

       

      but it gave below error

       

      • : PLS-00103: Encountered the symbol "COL_LIST" when expecting one of the following: . ( * @ % & - + / at mod remainder rem select update with delete insert || execute multiset save merge

      Thanks in Advance

        • 1. Re: how to get comma seperated values of a column from a cursor
          MansiRaval

          Do you need to know what wo_operation_id has been inserted into table mth_wo_operations_b?

           

          Provide the declaration part of cursor create_cur,Declaration part will show you the criteria for this cursor.

           

          Use below query to list down all operation_id inserted into mth_wo_operations_b one the insertions is done.

           

          select listagg(wo_operation_id,',') within group (order by wo_operation_id )"Operation ID" from mth_wo_operations_b;

           

          or show us the declaration part of create_cur:

          the table used in cursor can also used in above query to see the wo_operation_id list.

          • 2. Re: how to get comma seperated values of a column from a cursor
            Amrit Kaur Sachdeva-Oracle

            Hi Mansi,

             

            Thank you so much for the reply.

             

            Below is the declaration part of cursor

            CURSOR create_cur

              IS

                SELECT *

                FROM mth_work_order_details_int_gt wd

                WHERE wd.interface_batch_id  = p_wo_int_parms.wo_int_record.interface_batch_id

                AND wd.interface_status_code = wie_constants.c_int_inprocess

                AND wd.load_type             = wie_constants.c_load_type_operation

                AND wd.action_code           = wie_constants.c_create_action_code

                AND wd.header_number         = p_wo_int_parms.wo_int_record.header_number;

             

            If I use this

            select listagg(wo_operation_id,',') within group (order by wo_operation_id )"Operation ID" from mth_wo_operations_b;

            then it will get all values of wo_operation_id from mth_wo_operations_b

            I want only the ones which have been inserted in this run.

             

            • 3. Re: how to get comma seperated values of a column from a cursor
              MansiRaval

              See, if this can help, as the filters are same as cursor, you are only fetching below data it cursor and inserting all of them in mth_wo_operations_b table.

               

              SELECT  listagg(wo_operation_id,',') within group (order by wo_operation_id )"Operation ID"

                  FROM mth_work_order_details_int_gt wd

                  WHERE wd.interface_batch_id  = p_wo_int_parms.wo_int_record.interface_batch_id

                  AND wd.interface_status_code = wie_constants.c_int_inprocess

                  AND wd.load_type             = wie_constants.c_load_type_operation

                  AND wd.action_code           = wie_constants.c_create_action_code

                  AND wd.header_number         = p_wo_int_parms.wo_int_record.header_number;