I'm reading an Excel file with UUID fields looking like this {c52aff68-61fb-4004-bd58-84a3eb35e878}. How do I transfer them to an Oracle raw(32) fields in the Writer?

How to transfer a UUID fiels from Excel to Oracle Raw field.

+16

itay
Contributor
1425 replies
4 years ago
13 June 2019

Hi @fritspoulsen12

Maybe @david_r comment on using SQL in this post https://knowledge.safe.com/questions/3309/write-uuid-to-oracle-raw-field.html can help you.

Userlevel 4

Oracle uses a different (slightly non-standard) way of representing UUIDs compared to most other software. Basically you'll have to transform the ISO UUID to the Oracle equivalent by transposing character groups.

You can either do this using some Oracle helper functions that I posted here:

https://knowledge.safe.com/questions/3309/write-uuid-to-oracle-raw-field.html

Or you can use a PythonCaller in your workspace with something like this:

def iso_to_ora_uuid(feature):
    iso_uuid = feature.getAttribute('my_uuid')
    if iso_uuid:
        iso_uuid = iso_uuid.replace('{', '').replace('}', '').replace('-', '')
        ora_uuid = iso_uuid[6:8] + iso_uuid[4:6] + iso_uuid[2:4] + iso_uuid[0:2] + \
                   iso_uuid[10:12] + iso_uuid[8:10] + iso_uuid[14:16] + \
                   iso_uuid[12:14] + iso_uuid[16:21] + iso_uuid[-11:]
        feature.setAttribute('my_uuid', ora_uuid.upper())

This will transform the attribute my_uuid from ISO to Oracle RAW format.

Userlevel 4

Oracle uses a different (slightly non-standard) way of representing UUIDs compared to most other software. Basically you'll have to transform the ISO UUID to the Oracle equivalent by transposing character groups.

You can either do this using some Oracle helper functions that I posted here:

https://knowledge.safe.com/questions/3309/write-uuid-to-oracle-raw-field.html

Or you can use a PythonCaller in your workspace with something like this:

def iso_to_ora_uuid(feature):
    iso_uuid = feature.getAttribute('my_uuid')
    if iso_uuid:
        iso_uuid = iso_uuid.replace('{', '').replace('}', '').replace('-', '')
        ora_uuid = iso_uuid[6:8] + iso_uuid[4:6] + iso_uuid[2:4] + iso_uuid[0:2] + \
                   iso_uuid[10:12] + iso_uuid[8:10] + iso_uuid[14:16] + \
                   iso_uuid[12:14] + iso_uuid[16:21] + iso_uuid[-11:]
        feature.setAttribute('my_uuid', ora_uuid.upper())

This will transform the attribute my_uuid from ISO to Oracle RAW format.

Quick tip: I also use this handy little "calculator" quite a lot for one-off conversions:

https://robobunny.com/cgi-bin/guid

M

+2

Quick tip: I also use this handy little "calculator" quite a lot for one-off conversions:

https://robobunny.com/cgi-bin/guid

Useful that !

F

Thx guys, David-r's idea using the PythonCaller seems perfect for me. However, I can't make it work.

Can anyone show me, how to set up the PythonCaller parameters correct?

The FME template for the caller looks quite different, so I'm a bit confused (don't know Pyton at all) on how it should look.

Userlevel 4

Have a look at the attached FME template workspace (FME 2018.1)

iso_to_ora_uuid.fmwt

F

Thx a lot, David. Sometimes it's much easier, than you think. The FME template completely confused me.

Userlevel 4

Thx a lot, David. Sometimes it's much easier, than you think. The FME template completely confused me.

Good to hear it helped.

I understand why the default Python code template can be confusing to new users, fortunately the documention for the PythonCaller does a good job of explaining how it works.

F

Hi again, Been working with David-r's use of the PythonCaller. But I'm confused on the result when putting the generated UUID to the Oracle RAW-field.

The original GUID looks like this: {c52aff68-61fb-4004-bd58-84a3eb35e878}

After the PythonCaller like this: 68FF2AC5FB610440BD5884A3EB35E878

So far, so good, but in the Oracle RAW-field like this, like normal char-codes: 3638464632414335464236313034343042443538383441334542333545383738

Is that really, how it's supposed to look in a RAW-type Oracle field or do I need further conversion??

Userlevel 4

You should use the HexToRaw() function to cast the FME string to a proper Oracle RAW value.

If you have this Oracle UUID as a string:

68FF2AC5FB610440BD5884A3EB35E878

Then the SQL statement to insert it into Oracle could look like this:

insert into my_table (my_uuid) values (hextoraw('68FF2AC5FB610440BD5884A3EB35E878'))

F

@David_r. Can I do that in FME genericly using AttributeCreator or SQLExecutor?

Userlevel 4

Yes, definitely. Example in the SQLExecutor, if your UUID string is contained in the feature attribute my_uuid:

insert into my_table (my_uuid) 
values (hextoraw('@Value(my_uuid)'))

Notice the single quotes and that there is no semi-colon at the end.

F

@David'r, That's great, but can't I do the conversion earlier, before the actual insert, and then just get the field inserted automatically vie the FME Writer together with all the other fields?

Userlevel 4

@David'r, That's great, but can't I do the conversion earlier, before the actual insert, and then just get the field inserted automatically vie the FME Writer together with all the other fields?

That should work, yes. If it doesn't, please post a minimal workspace here + logs to reproduce the issue.

F

@david_r. Thx again. It now works with the SQLExecutor Insert.... method. I never really tried in AttributeCreator, but it seems it does not know the HEXTORAW function, but no problem now.

Userlevel 4

@david_r. Thx again. It now works with the SQLExecutor Insert.... method. I never really tried in AttributeCreator, but it seems it does not know the HEXTORAW function, but no problem now.

HEXTORAW is an Oracle-specific function and wouldn't make much sense outside that context, which is why it only works in the SQLExecutor.

F

@david_r. Thx again. For me, that's one of the main problems in using FME. To know when you use an unknown illegal function or just have the wrong syntax for using it correct in FME.

Userlevel 4

@david_r. Thx again. For me, that's one of the main problems in using FME. To know when you use an unknown illegal function or just have the wrong syntax for using it correct in FME.

No worries, we all have to start somewhere. Just keep asking questions :-)

How to transfer a UUID fiels from Excel to Oracle Raw field.

18 replies

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded