Oracle PL/SQL Insert comma separated string split into rows with varying label values

Vinipanda

Hi,

I had a requirement in the existing thread :https://community.oracle.com/tech/developers/discussion/comment/16775085#Comment_16775085.

and this is fiddle for last thread solution tested : https://dbfiddle.uk/?rdbms=oracle_18&fiddle=412a6e27a22741ee1c31eee4a3f2bf3a

This was answered, however the requirement has changed and i have replicated the scenario here in fiddle.

In short, string is taken as input, split into rows and pushed into data. Earlier the labels against which it was pushed were constant, in terms of name and number. Now they shall vary,

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e316816fb57801844988709c97d461d5

So, the table push_data_pivot has a new column, app_id.

On basis of app_id, there are label values. SO for app_id=1 there are 14 rows, with 14 labels. . For app_id=2, there are 10 labels.

So when string is passed in procedure with app_id, the string would be split using the same regex logic sequence wise, using the seq column.

Just like we were splitting from COL01....COL14, now we need to split from seq 1....10 or 1...14 as per app_id passed.

So earlier we were inserting the labels COL01..14 and the corresponding values.

Now we already have labels, so we need to match app_id, label seq and according push rows.

So if the procedure passes string with app_id=1, it would split the string accordingly in the 14(max) columns and so on.

I tried adding app_id and make changes in procedure but am not able to.

The proc would be executed for ids like this then:

BEGIN

PUSH_DATA_PIVOT_1(111,'[""Project title for IT Heads"","The values are,\n "exactly" up to the requirement and analysis done by the team.

Also it is difficult to,\n prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"]',1);

END;

/

BEGIN

PUSH_DATA_PIVOT_1(112,'[""Project title for IT Heads"","The values are,\n "exactly" up to the requirement and analysis done by the team.

Also it is difficult to,\n prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension"]',2);

END;

/

The old fiddle link is available in the old thread mentioned for reference.

Frank Kulash

Hi,

Entries for each id and string passed would be made for both app IDs, or as many app IDs as present in the table and the corresponding labels.

I see; every time you call the procedure, it should MERGE 24 rows (= 14 + 10, or however many your have for all app_ids out together).

Here's one way to do that:

CREATE OR REPLACE PROCEDURE push_data_pivot_1
( p_id     NUMBER
, data_string VARCHAR2
)
IS
BEGIN
  MERGE INTO push_data_pivot dst
  USING (
        SELECT id_pk
        ,    p_id	AS id
        ,    label_id
        ,    label
        ,    NULLIF ( REGEXP_SUBSTR (data_string, '(".*?")(,|\])', 1, seq, 'n', 1)
        	    , '""'
        	    ) AS value
        ,    seq
        ,    app_id
        FROM	push_data_pivot
        WHERE  id IS NULL
  	) src
  ON (  dst.id_pk = src.id_pk
    AND dst.id	  = src.id
    AND	dst.label_id = src.label_id
    AND dst.label   = src.label -- just guessing
    AND dst.seq    = src.seq
    AND dst.app_id  = src.app_id
    )
     WHEN MATCHED THEN UPDATE
       SET  dst.value = src.value
       WHERE dst.value || '?' <> src.value || '?'
     WHEN NOT MATCHED THEN INSERT (dst.id_pk, dst.id, dst.label_id, dst.label, dst.value, dst.seq, dst.app_id)
                VALUES (src.id_pk, src.id, src.label_id, src.label, src.value, src.seq, src.app_id)
  ;
END push_data_pivot_1;
/

This assume the template rows are stored in the same push_data_pivot table, with NULL ids. If you create a separate table for them, it should be easy to modify the USING clause to reference that table instead of push_data_pivot.

In your earlier thread, you wanted to clean the data somewhat before putting it into push_data_pivot. For example, you wanted to change '\n' to CHR(10) in some special cases. If you still need to do that, then nest REGEXP_REPLACE inside other functions, such as REPLACE, in the statement above. The only cleaning that the statement above does is values consisting of the two-character string '""' (that is, two double-quotes, with nothing in between them) are changed to NULL.

Vinipanda

The expected result is this:

Frank Kulash

Hi,

So, you want a procedure that will take two arguments (an ID number and a string) and MERGE either 10 or 14 rows into the push_data_pivot table. All the rows MERGEd from the same procedure call will have the same app_id (either 1 or 2) and the total number of rows merged will depend on app_id (14 rows for app_id=1, and 10 rows for app_id=2). Is that right?

How do you decide what the app_id will be for a given call?

Say you call the procedure with app_id=112 with app_id=2, but there are already 14 rows in the table for id=112. Do you want to UPDATE the rows with seq<=10, and DELETE the rows with seq>=11?

How do you decide what the label will be?

You may want to create a new table, that has one row for every distinct app_id, and a column that tells how many EAV rows there will be for that app_id.

Vinipanda

Hi Frank,

The table push_data_pivot now has the app_ids and corresponding labels already defined. I need to push data against them as per app_id and against the existing labels in the table.

The string passed at runtime wont be split into more rows than labels, this is fixed from the front end.

So if for app_id=2, we have only 10 labels, so the string we get at runtime also would have rows <=10 only. SO the length of string wont be an issue.

the procedure now will take 3 args, id,string and app_id. SO if app_id=2 for id=111, it would go to select * from push_data_pivot where app_id=2 and start inserting the id and string into rows against the existing labels in the sequence provided in sequence column.

So earlier we started splitting from COL01...14, now we would split from seq=1....10 or 14 accoridng to app_id passed.

Frank Kulash

Hi,

The table push_data_pivot now has the app_ids and corresponding labels already defined. I need to push data against them as per app_id and against the existing labels in the table.

Do you mean the rows in push_data_pivot with NULL ids have the label values? That doesn't seem like a very good design. Also, if the label is dependent on app_id and seq, do you really want to repeat the label on every row? That could be another design problem. Think carefully about these things before building on them.

I still don't know what you want to do in this situation:

1. The procedure is called with id=987, app_id=1, so 14 rows are inserted
2. Later, the procedure is called again with id=987 and app_id=2

• Do you want to UPDATE 10 rows and DELETE 4?
• Do you want to UPDATE 10 rows and leave the other 4 untouched?
• Do you want INSERT 10 news rows, and leave the other 14 untouched?
• Do you want to raise an error?
• Do you want to do something else?

Vinipanda

I totally understand the confusion, but such is the requirement Frank!

The rows with null ids are what exist already, and these would be taken as reference.

I know practically here there should have been a separate table with labels, app_id info but we need to take reference from the same table only with the null id rows..

So for each new id and string passed, we will have to make new rows.

Ex: if we pass id=100,string and app_id=2, then we would insert the id=100 for app_id=2, with string split into rows as per same seq of labels as currently defined.

With each new id and new app id rows would be inserted with the labels as mentioned.

So null ids in this table are reference for the new rows that would be inserted.

1. The procedure is called with id=987, app_id=1, so 14 rows are inserted
2. Later, the procedure is called again with id=987 and app_id=2

• Do you want INSERT 10 news rows, and leave the other 14 untouched?
• Yes, if app_id=2., 10 rows would be inserted with the labels as present against the null ids.
• Also 14 rows for app_id=1, 10 for app_id=2, these are examples,.The number of rows/appids could vary in future. We just need to split the string and insert along with the app_id and labels in sequence.
• Do you want to raise an error?

Yes

• Do you want to do something else?
• No

Vinipanda

So, when select * from push_data_pivot where app_id=2, fetch the labels and put the string rows against those and insert. I dont know how better i can explain this but this is what it is for now and sincerely hope you can help.

The insert/update logic using merge would anyways be used. So once there are entries for say id=100 and app_id=2. If it is passed again with different string, the entries would get updated.

Vinipanda

Or if this cannot be implemented at all, we can create a table for headers, separately. And when we are sure and ready with solution, i can convince the team that that's the only way.

But even if there is slightest possibility, i have to do it!

Please suggest which of the two would be feasible?

Frank Kulash

Hi,

Sorry, I still don't understand the requirements.

Do you want INSERT 10 news rows, and leave the other 14 untouched?

Do you want to raise an error?

Yes

Do you mean you want to INSERT 10 new rows, and then rise an error? That means the procedure will have to COMMIT changes, which is usually not a good idea. In the next reply you contradicted thaat:

So once there are entries for say id=100 and app_id=2. If it is passed again with different string, the entries would get updated.

Do you want to INSERT, or do you want to UPDATE? Pick exactly one.

we can create a table for headers, separately. And when we are sure and ready with solution, i can convince the team that that's the only way.

Please suggest which of the two would be feasible?

It can be done either way. I think having a separate table for the labels would be simpler, more efficient and easier to maintain.

Vinipanda

Hi Frank

Sorry for all the confusion. I had a meeting and got the requirement cleared up. So, the labels would change as per appid but the string would remain same.i.e. we need to pass only id and string like before where string would fill in value column.

New rows would get inserted for each app id. I.e. if we pass I'd 111 , then it would make entries for both app IDs. Later when it will get displayed on application, the labels would change as per app id. As of now in the current table we have labels for 2 app IDs, so we need to write proc which would take id and string.

And then make entry for the same string using both app IDs.

Ex. App id=1

ID=111

Label=ABC

String =xyZ

Appid=2

ID=111

Label=DEF

String=xyZ

Because the ultimate result would be changing labels for each I'd value.

Vinipanda

I hope this seems clearer now and apologize for the inconvenience as I also got clarity from client a short while ago.

Entries for each id and string passed would be made for both app IDs, or as many app IDs as present in the table and the corresponding labels.

Also we can assume the rows for each app id to be any number.

In our example it's 14 and 10, but it would be not differ much in reality.

This number can vary. We should need to make entries for these on basis of id and string passed.

Please let me know in case any clarification is needed and I will be happy to explain.

Vinipanda

The output expected would be something like this for say id=111 passed.

The id is 111 for all app_ids, in this sample data 1 and 2.The value column will also have the string repeat for both app_ids. Only the existing labels are changing as per the app id.

So when we pass:

BEGIN

PUSH_DATA_PIVOT_1(111,'[""Project title for IT Heads"","The values are,\n "exactly" up to the requirement and analysis done by the team.

Also it is difficult to,\n prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"]',1);

END;

/

This would make entry for id 111, with all the appids and corresponding labels.

Frank Kulash

Hi,

Entries for each id and string passed would be made for both app IDs, or as many app IDs as present in the table and the corresponding labels.

I see; every time you call the procedure, it should MERGE 24 rows (= 14 + 10, or however many your have for all app_ids out together).

Here's one way to do that:

CREATE OR REPLACE PROCEDURE push_data_pivot_1
( p_id     NUMBER
, data_string VARCHAR2
)
IS
BEGIN
  MERGE INTO push_data_pivot dst
  USING (
        SELECT id_pk
        ,    p_id	AS id
        ,    label_id
        ,    label
        ,    NULLIF ( REGEXP_SUBSTR (data_string, '(".*?")(,|\])', 1, seq, 'n', 1)
        	    , '""'
        	    ) AS value
        ,    seq
        ,    app_id
        FROM	push_data_pivot
        WHERE  id IS NULL
  	) src
  ON (  dst.id_pk = src.id_pk
    AND dst.id	  = src.id
    AND	dst.label_id = src.label_id
    AND dst.label   = src.label -- just guessing
    AND dst.seq    = src.seq
    AND dst.app_id  = src.app_id
    )
     WHEN MATCHED THEN UPDATE
       SET  dst.value = src.value
       WHERE dst.value || '?' <> src.value || '?'
     WHEN NOT MATCHED THEN INSERT (dst.id_pk, dst.id, dst.label_id, dst.label, dst.value, dst.seq, dst.app_id)
                VALUES (src.id_pk, src.id, src.label_id, src.label, src.value, src.seq, src.app_id)
  ;
END push_data_pivot_1;
/

This assume the template rows are stored in the same push_data_pivot table, with NULL ids. If you create a separate table for them, it should be easy to modify the USING clause to reference that table instead of push_data_pivot.

In your earlier thread, you wanted to clean the data somewhat before putting it into push_data_pivot. For example, you wanted to change '\n' to CHR(10) in some special cases. If you still need to do that, then nest REGEXP_REPLACE inside other functions, such as REPLACE, in the statement above. The only cleaning that the statement above does is values consisting of the two-character string '""' (that is, two double-quotes, with nothing in between them) are changed to NULL.

Vinipanda

Thanks Frank. This is working fine as of now. Will present this to team and lets see what happens next.

Thanks!!