parsing oracle 11g

Question

I have the following snippet of a file that needs parsing :

000000837002100302015-04-13-15.55.12.1922082015-04-1300032128CHECK CLEARED CHECK CLEARED 00000000000030000000000000000000000000000000016594703

The respective positions for the string are as follows:

RECORD1                     - POS (1  :10 ) - DUR 10
RECORD2                     - POS (11 :13 ) - DUR 3
RECORD3                     - POS (14 :17 ) - DUR 4
RECORD4                     - POS (18 :43 ) - DUR 26
RECORD5                     - POS (44 :53 ) - DUR 10
RECORD6                     - POS (54 :61 ) - DUR 8
RECORD7                     - POS (62 :95 ) - DUR 34
RECORD9                     - POS (96 :215) - DUR 120
RECORD10                    - POS (216:233) - DUR 18
RECORD11                    - POS (234:251) - DUR 18
RECORD12                    - POS (252:266) - DUR 15
RECORD13                    - POS (267:268) - DUR 2

I need to parse the string to extract these records from that string. Can anyone help with the substr/instr functionality to account for the string and blank spaces. The extracted data would then get inserted into a table. Thank you in advance!

bob dylan · Accepted Answer · 2015-05-13 14:28:07Z

1

Can you not simply use a sqlloader control file e.g.

echo "OPTIONS (DIRECT=TRUE)"
echo "LOAD DATA"
echo "INFILE '/path/to/file/$1'"
echo "BADFILE '/path/to/file/log/$1.bad'"
echo "DISCARDFILE '/path/to/file/log/$1.dsc'"
echo "APPEND INTO TABLE table.t"
echo "("
echo "    RECORD1      POSITION (1:10),"
echo "    RECORD2   POSITION (11:13),"
etc...
echo ")"

answered May 13, 2015 at 14:28

bob dylan

1,5181 gold badge14 silver badges32 bronze badges

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

3 Comments

QuickDrawMcgraw Over a year ago

Thank you Dmitry, i was confused on how the blank spaces would fall into place, but i think i understand what you are getting at. If I did not have the position specs i would have to use instr to figure out where the strings were to break to extract the different records?

bob dylan Over a year ago

How would instr help? Without knowing the lengths how would you expect 000000837002100302015 to be logically broken down?

QuickDrawMcgraw Over a year ago

Yes Bob, i was just confused with the blank space aspect of it. I was thinking up to the first 'check cleared' would be one string, then the spaces, then the second 'check cleared' would be the second string, etc.

Dmitry.Samborskyi · Accepted Answer · 2015-05-13 14:42:44Z

0

You don't need instr, because the position is already specified. Instead use substr

insert into some_table 
select substr(text, 1, 10) rec1, substr(text, 11, 3) rec2, ... 
from text_table;

edited May 13, 2015 at 14:42

answered May 13, 2015 at 14:21

Dmitry.Samborskyi

4852 silver badges10 bronze badges

Collectives™ on Stack Overflow

parsing oracle 11g

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related