- 3,734,278 Users
- 2,246,936 Discussions
- 7,857,217 Comments
- 380.9K All Categories
- 2.1K Data
- 203 Big Data Appliance
- 1.9K Data Science
- 446.1K Databases
- 220.4K General Database Discussions
- 23 Multilingual Engine
- 506 MySQL Community Space
- 459 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 438 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.4K SQL & PL/SQL
- 20.8K SQL Developer
- 291.3K Development
- 6 Developer Projects
- 117 Programming Languages
- 288.1K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.2K Java
- 18 Java Learning Subscription
- 36.9K Database Connectivity
- 149 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.7K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 12 Java Essentials
- 138 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 195 Java User Groups
- 180 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Oracle PL/SQL Insert comma separated string split into rows with varying label values
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,
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:
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);
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);
The old fiddle link is available in the old thread mentioned for reference.