insert to multi row in db with 1 variable input PL/SQL?

Question

I have the input

var1,var2,var3

and I need to insert or update into 3 row table with split by ",", How do I do that, thank you

You should go here: Splitting string into multiple rows in Oracle. Please search before asking. — Pham X. Bach
– Pham X. Bach, Commented Apr 21, 2016 at 4:43

Markus Zaczek · Accepted Answer · 2016-04-21 07:39:48Z

2

If i understood your question correctly you want to create three rows out of one input text line broken down by some delimitter. You can do this with Regex_substr

CREATE TABLE TEST AS ( 
SELECT REGEXP_SUBSTR ('var1,var2,var3','[^,]+',1 ,LEVEL) values 
FROM DUAL 
CONNECT BY REGEXP_SUBSTR ('var1,var2,var3','[^,]+' ,1 ,LEVEL) IS NOT NULL)

and just the insert as follow up:

INSERT INTO TEST (
SELECT REGEXP_SUBSTR ('var1,var2,var3', '[^,]+',1,LEVEL) VALUES
FROM DUAL
CONNECT BY REGEXP_SUBSTR ('var1,var2,var3','[^,]+' ,1 ,LEVEL) IS NOT NULL)

For the update you need to be more specific on what the operation (before -> after) shall accomplish

answered Apr 21, 2016 at 7:39

Markus Zaczek

1546 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

insert to multi row in db with 1 variable input PL/SQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related