+6
I have a GM-access-table that I want to import to oracle. Instead of the original auto-number ID, I want to use an UUID in oracle, which I generate with the UUIDGenerator, and save it in a raw-type field.
How do I do this, as in FME raw-type columns are not shown?
Thanks in advance!
Vera
+13
+6
If I knew how to write to Oracle, I would try the following:
- create a reader to my GM-access table
- create an UUID
- use the SQLExecuter to convert the uuid to raw (hextoraw-function)
>> write to Oracle
any succestions?
Vera
+13
Your proposed actions look fine to me, did you try it already?
A trial and error approach for new transformations is quite common.
Itay
the problem is that the Oracle string representation of an UUID differs from the ISO standard used by the UUIDGenerator. You therefore cannot use the UUIDGenerator to write directly to an Oracle RAW field.
To write UUIDs to Oracle, the easiest is to use the SQLExecutor with the Oracle SYS_GUID() function, e.g.
insert into mytable ("ID", "NAME", "MYGUID") values (1, 'John Doe', SYS_GUID())
David
select sys_guid() as "GUID" from dual
You can then expose the attribute GUID in the SQLExecutor to access it in your workflow.
David
+6
If I write GUID in the field "Attributes to expose" and then click on "Populate from SQL-Query" it disappears again ?!
Another thing is, we have to be able to convert the UUIDs (ISO-Standard) in Oracle-Standard and back again. Is there a transformer to convert it back again?
Thanks, Vera
you should not click "Populate" when you type in "GUID" manually.
Also, there is an error below, this is the correct syntax to get a GUID from Oracle using an SQLExecutor:
select rawtohex(sys_guid()) as GUID from dual
To convert to/from ISO / Oracle UUIDs you will have to resort to Python (it is possible to do it with pure FME transformers but it is a bit messy). The important parts are
From ISO to Oracle:
ORAuuid = ISOuuid[6:8] + ISOuuid[4:6] + ISOuuid[2:4] + ISOuuid[0:2] + ISOuuid[10:12] + ISOuuid[8:10] + ISOuuid[14:16] + ISOuuid[12:14] + ISOuuid[16:21] + ISOuuid[-11:]
From Oracle to ISO:
uuid = ORAuuid[6:8] + ORAuuid[4:6] + ORAuuid[2:4] + ORAuuid[0:2] + ORAuuid[10:12] + ORAuuid[8:10] + ORAuuid[14:16] + ORAuuid[12:14] + ORAuuid[16:21] + ORAuuid[-11:] ISOuuid = "%s-%s-%s-%s-%s" % \\ (uuid[:8], uuid[8:12], uuid[12:16], uuid[16:20], uuid[20:32])
You will of course have to write the necessary wrappers around this, but it is very easy if you have a little bit of experience with the PythonCaller (look at the sample code and the help for details). If not, let us know.
David
+6
Thanks for the Python Code.
I'm able to populate my table now by inserting all attributes (including GUID) with an SQL-statement in an SQLExecutor.
This doesn't work for the geometry though. My original table is an GM-access one and I write to oracle (non spatial), the type of the geometry-field in Oracle is SDO-GEOMETRY. As you answered me some time ago https://safe.secure.force.com/AnswersQuestionDetail?id=906a0000000cmaqAAA , I can use the GeometryeExtractor / -Replacer to get the geometry. This works fine, if I have an FME-writer, where I can create a blob-field to which I connect the geometry-field of the GeometryReplacer.
But in this case, because of the GUID I'm inserting all attributes with an SQL-Statement. As I found out by googling, populating an SDO_Geometry-field with an SQL-Statement is quite complicated.
So how can I get the geometry to oracle?
Thanks, Vera
I would try and use the regular Oracle Spatial writer, if possible. That is without doubt the easiest, by far.
To write the UUID values, try manually adding the relevant RAW field to the Oracle Spatial writer, but specify it as data type VARCHAR2(32). Hopefully that should work and enable you to write both the UUID and the geometry at the same time.
David
+6
We don't have Oracle Spatial on the Oracle-side, and we don't have the FME-licence with the Oracle-Spatial writer.
Obviously, I'm not able to write GM-access geometries to Oracle-nonspatial, even if I use the GeometryExtractor / -Replacer (from Oracle to oracle-nonspatial it works). Is there a different way I can do this?
If we upgraded FME to be able to use the Oracle spatial writer, could I use it even without having Oracle Spatial (Oracle Enterprise Edition)?
Thanks, Vera
+6
We now have the Oracle Spatial writer and everything works ok.
Thanks
Vera
I have a client having the same problem. You say "We now have the Oracle Spatial writer and everything works ok." but you do not say how. i.e, the VARCHAR2(32) defintion iof the ID fiels does not help. Thanks.
Flavio
I used the SQLExecuter and ran the following "select sys_guid() as uuid from dual" and then exposed uuid and left the column as raw and this seemed to work for me.
For reference, here are a couple of Oracle helper functions that can convert between the two UUID representations:
CREATE OR REPLACE FUNCTION GUIDTORAW (Guid IN VARCHAR2)
RETURN RAW
AS
BEGIN
RETURN HEXTORAW (
SUBSTR (Guid, 7, 2)
|| SUBSTR (Guid, 5, 2)
|| SUBSTR (Guid, 3, 2)
|| SUBSTR (Guid, 1, 2)
|| SUBSTR (Guid, 12, 2)
|| SUBSTR (Guid, 10, 2)
|| SUBSTR (Guid, 17, 2)
|| SUBSTR (Guid, 15, 2)
|| SUBSTR (Guid, 20, 4)
|| SUBSTR (Guid, 25, 12));
END GUIDTORAW;
CREATE OR REPLACE FUNCTION RAWTOGUID (RawData IN RAW)
RETURN VARCHAR2
AS
BEGIN
DECLARE
HexData VARCHAR2 (32) := RAWTOHEX (RawData);
BEGIN
RETURN LOWER (
SUBSTR (HexData, 7, 2)
|| SUBSTR (HexData, 5, 2)
|| SUBSTR (HexData, 3, 2)
|| SUBSTR (HexData, 1, 2)
|| '-'
|| SUBSTR (HexData, 11, 2)
|| SUBSTR (HexData, 9, 2)
|| '-'
|| SUBSTR (HexData, 15, 2)
|| SUBSTR (HexData, 13, 2)
|| '-'
|| SUBSTR (HexData, 17, 4)
|| '-'
|| SUBSTR (HexData, 21, 12));
END;
END RAWTOGUID;Example:
select RAWTOGUID(sys_guid()) from dual;Sample return value:
6065d946-9e83-bc00-e053-0a865e369365I used the SQLExecuter and ran the following "select sys_guid() as uuid from dual" and then exposed uuid and left the column as raw and this seemed to work for me.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.