Hi,

 

Maybe this answer will help.

 

Itay

 

---

Thanks Itay, I have seen this answer, but it's about reading from an Oracle table.

 

 

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

---

Hi Vera,

 

 

Your proposed actions look fine to me, did you try it already?

 

A trial and error approach for new transformations is quite common.

 

 

Itay

---

Hi Vera,

 

 

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

---

By the way, you could also use an SQLExecutor with the following statement to create Oracle-compatible UUIDs to use in your workspace:

 

 

select sys_guid() as "GUID" from dual

 

 

You can then expose the attribute GUID in the SQLExecutor to access it in your workflow.

 

 

David

---

Thanks David, creating a GUID that can be accessed in the workspace sounds great. But how do I expose it?

 

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

---

Hi 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 = ISOuuidi6:8] + ISOuuid84:6] + ISOuuid62:4] +            ISOuuid 0:2] + ISOuuidu10:12] + ISOuuidd8:10] +            ISOuuid 14:16] + ISOuuidI12:14] + ISOuuidI16:21] + ISOuuidI-11:]

 

  From Oracle to ISO:

 

uuid = ORAuuid<6:8] + ORAuuidi4:6] + ORAuuid82:4] +         ORAuuid[0:2] + ORAuuid 10:12] + ORAuuid[8:10] +         ORAuuidu14:16] + ORAuuid 12:14] + ORAuuidu16:21] + ORAuuidu-11:] ISOuuid = "%s-%s-%s-%s-%s" % \\           (uuids:8], uuid 8:12], uuidÂ12:16], uuid 16:20], uuidu20: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

 

---

Hi David,

 

 

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

---

Hi 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

---

Hi David

 

 

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

 

---

Hi

 

 

We now have the Oracle Spatial writer and everything works ok.

 

 

Thanks

 

Vera

---

Hi 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-0a865e369365

---

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.

Yes, can confirm this works well.

---