i have an input file: $home/dir/subdir/input.txt
content of input.txt:
123,0000,11111,3,1,X
124,0001,11112,3,1,Y
125,0002,,4,2,Y
129,0003,11114,4,2,X
I have a table where col2, col3 cannot be null:
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 |
|---|---|---|---|---|---|---|---|---|
| 123 | 0000 | 11111 | 3 | 1 | X | |||
| 124 | 0001 | 11112 | 3 | 1 | Y | |||
| 129 | 0003 | 11114 | 4 | 2 | X |
How do I insert the contents of input.txt to the table accordingly? using pl/sql and unix scripting
You can use the regexp_substr function.
In the following example, I used the regexp_like function to confirm it was in the given format. Adjust it as you like.
Example:
SELECT regexp_substr(string, '[^,]+', 1, 1) as col2,
regexp_substr(string, '[^,]+', 1, 2) as col3,
regexp_substr(string, '[^,]+', 1, 3) as col4,
regexp_substr(string, '[^,]+', 1, 4) as col7,
regexp_substr(string, '[^,]+', 1, 5) as col8,
regexp_substr(string, '[^,]+', 1, 6) as col9,
FROM table
WHERE REGEXP_LIKE(string, '[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+,[A-Za-z]+');
Result:
| COL1 | COL2 | COL3 | COL4 | COL5 | COL6 | COL7 | COL8 | COL9 |
|---|---|---|---|---|---|---|---|---|
| - | 123 | 0000 | 11111 | - | - | 3 | 1 | X |
| - | 129 | 0003 | 11114 | - | - | 4 | 2 | X |
| - | 124 | 0001 | 11112 | - | - | 3 | 1 | Y |
