+2Hi,
Wondering if there is a solution to this problem:
When using the 'Sequenced Table Columns' Format Parameter on the Oracle Writers (allows you to specify an Attribute and Sequence to populate that Attribute with the next value from the sequence) but I couldn't use it with a Fan Out. If all Feature Types used the same Sequence that would be OK. But if one Sequence per Feature Type I couldn't get it to work.
Any suggestions?
Best answer by david_r
to go off on a tangent here, if the GID is supposed to be a primary key (like ObjectID in ArcGIS), I would personally argue strongly with the DB owner to permanently implement these triggers.
Auto-incrementing the primary key should be handled on the DB side on not in the client, in my humble opinion. It is transactionally safer and has a more logical separation of concerns.
Writing the data first and then populating the GID after might technically be a viable solution if there is no "unique not null" constraint on the column (a big no-no in itself), but I would never dare to put such a solution into an enterprise production environment...
David
+2
I don't know about the sequenced table columns, but if you want feature-level control over INSERT/UPDATE/DELETE, your best option might be to have three writers with corresponding writer modes (but otherwise identical), then using e.g. a TestFilter at the end of your process to route each feature to the correct writer depending on the value of fme_db_operation.
David
+2Hi David,
Think the format param 'fme_db_operation' does this for you at a feature level even with a single writer if configured correctly. Your suggestion may work better in some situations and explicitly shows what's going where.
Mark
yes, you're right about fme_db_operation, which also makes me realize that I misunderstood your original question somewhat :-)
I have made some small tests regarding the sequence table column setting, but I cannot find a way to set it at feature level, either. The help is also suspiciously silent on the matter.
David
+17Just a thought..
What about using the begin/end sql on the writer to do the job?
Cheers,
Itay
+17
[oracle_sequenced_cols column[:seqname][;column[:seqname]]...] \
Thanks for the suggestions. Columns are all named GID on multiple tables, column is defined as Primary Key so NOT NULL with one sequence per table named <table_name>_SEQ . This rules out End SQL as it will need a value. Only way I can see it working is to get the MAX(GID) for each table and set that as the lowest value and count up from there. Still messy and not worth it IMHO.
oracle_sequenced_cols I think has to be a parameter and not an attribute. Might ping off an email to Safe and see what they suggest.
Mark
does your tables already exist before you run FME? If so, could it be a possibility to set triggers on the GID columns in Oracle (short example).
This way you wouldn't have to specify anything for these values in FME.
David
+2
I don't 'own' the database so permanent changes may not be possible. That said I could add a trigger with Begin SQL and remove it with End SQL.
to go off on a tangent here, if the GID is supposed to be a primary key (like ObjectID in ArcGIS), I would personally argue strongly with the DB owner to permanently implement these triggers.
Auto-incrementing the primary key should be handled on the DB side on not in the client, in my humble opinion. It is transactionally safer and has a more logical separation of concerns.
Writing the data first and then populating the GID after might technically be a viable solution if there is no "unique not null" constraint on the column (a big no-no in itself), but I would never dare to put such a solution into an enterprise production environment...
David
+2My intention was to use a Fanout but for reasons explained above it doesn't work without a workaround. The sugestion of a permanent Oracle Trigger added by the DBA is probably the best solution.
In hindsight the idea of creating and removing a Trigger using BEGIN/END SQL statements probably won't work as the user may not have CREATE TRIGGER system privilege. The same applies for DROP/CREATE SEQUENCE to amend it based on a new value calculated by FME. Thanks David and Itay for all the suggestions. Mark
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
