Skip to main content
Solved

FeatureWriter catch contrain oracle

  • June 20, 2017
  • 10 replies
  • 42 views
M
Forum|alt.badge.img

Can'i catch the contraints oracle with the FeatureWriter? I need to test the integrity of oracle and catch the exception and do something if an error occure.

Execution of statement `UPDATE "MYTABLE" SET "STATUT_PRP_R" = :"STATUT_PRP_R" WHERE "OBJECTID" = :"OBJECTID"' did not succeed; error was `ORA-02290: violation de contraintes (PRP_AQU_BORNEINCENDIE_P_CHK) de vérification'. (serverType=`...

Best answer by larry

Bonjour @mario_aylwin,

If you strictly depend on the database and you are building your queries dynamically, you can use a SQLExecutor instead of a FeatureWriter and check at the <Rejected> port for failed queries. The _reader_error attribute will have the error message from Oracle.

Example _reader_error value: An error occurred while accessing a table result for feature type `QueryFactory_SQLExecute'; message was `Execution of statement `INSERT INTO TABLE1 (COLUMN1) VALUES ('X')' did not succeed; error was `ORA-02290: check constraint (TEST.TABLE1_CHK1) violated'. (serverType=`ORACLE8i', serverName=`********', userName=`********', password=`***', dbname=`')' (server type is `ORACLE8i', server name is `*******', username is `*******', password is `***', database name is `')).

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

10 replies

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • June 20, 2017

The FeatureWriter itself can not catch the Oracle Constraints.

But using the AttributeValidator, you can filter a lot of invalid values before writing.

However, you still have to be aware of the different constraints in the Oracle database.

M
Forum|alt.badge.img

The FeatureWriter itself can not catch the Oracle Constraints.

But using the AttributeValidator, you can filter a lot of invalid values before writing.

However, you still have to be aware of the different constraints in the Oracle database.

Sorry, but the AttributeValidator doesn't works for me. The DBA have database control and it's not possible for me to test all type of constraints with AttributeValidator. I use SQLExecutor but i have to build all my query dynamically. Someone have other solution to catch the exception.

 

L
Forum|alt.badge.img
  • larry
  • Best Answer
  • June 20, 2017

Bonjour @mario_aylwin,

If you strictly depend on the database and you are building your queries dynamically, you can use a SQLExecutor instead of a FeatureWriter and check at the <Rejected> port for failed queries. The _reader_error attribute will have the error message from Oracle.

Example _reader_error value: An error occurred while accessing a table result for feature type `QueryFactory_SQLExecute'; message was `Execution of statement `INSERT INTO TABLE1 (COLUMN1) VALUES ('X')' did not succeed; error was `ORA-02290: check constraint (TEST.TABLE1_CHK1) violated'. (serverType=`ORACLE8i', serverName=`********', userName=`********', password=`***', dbname=`')' (server type is `ORACLE8i', server name is `*******', username is `*******', password is `***', database name is `')).

M
Forum|alt.badge.img

Bonjour @mario_aylwin,

If you strictly depend on the database and you are building your queries dynamically, you can use a SQLExecutor instead of a FeatureWriter and check at the <Rejected> port for failed queries. The _reader_error attribute will have the error message from Oracle.

Example _reader_error value: An error occurred while accessing a table result for feature type `QueryFactory_SQLExecute'; message was `Execution of statement `INSERT INTO TABLE1 (COLUMN1) VALUES ('X')' did not succeed; error was `ORA-02290: check constraint (TEST.TABLE1_CHK1) violated'. (serverType=`ORACLE8i', serverName=`********', userName=`********', password=`***', dbname=`')' (server type is `ORACLE8i', server name is `*******', username is `*******', password is `***', database name is `')).

Yes, i use that technique for now. But, it's not easy to use GEOMETRY with SQLExecutor.

An other solution to catch Oracle Constraint?

L
Forum|alt.badge.img
  • larry
  • June 20, 2017

Yes, i use that technique for now. But, it's not easy to use GEOMETRY with SQLExecutor.

An other solution to catch Oracle Constraint?

Maybe you can do a two steps update. Attributes first using a SQLExecutor and if ok, update the geometry only using a writer or feature writer.

 

 

M
Forum|alt.badge.img
Maybe you can do a two steps update. Attributes first using a SQLExecutor and if ok, update the geometry only using a writer or feature writer.

 

 

Yes, i will use that solution for now. With out other choice. Thanks every body.

 

 

mark2atsafe
Safer
Forum|alt.badge.img+59

Yes, i use that technique for now. But, it's not easy to use GEOMETRY with SQLExecutor.

An other solution to catch Oracle Constraint?

Could you extract the geometry as wkt using a GeometryExtractor and then use the SQLExecutor to write that straight to a geometry column? Not the best way, and I don't know if you need to update other geometry-related columns too, but worth mentioning I thought.

 

 

FME Evangelist to the Rich and Famous!!
gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • June 21, 2017

U can use a sql executor/creator to read out the user_constraints.

Query it for your table and constraint_name or just relevant table to get its constraint.

SELECT * FROM user_constraints

WHERE table_name = '<your table name>'

AND constraint_name = '<your constraint name>';

Then you can apply a test if the objects respect these.

M
Forum|alt.badge.img

Yes,

I use the transformer SQLExecutor and that functionne nicely.

Thanks every body.

M

Bonjour @gio​ 

I don't know when this option was changed. But, i try to read out the user_contraints and i have only the last line. And the contraints message are not in it.

 

How can i retrieve the complete constraints?

Thanks

 

Spec : FME 2021.2 with Oracle 19c

 

image 

image 

 

Community Stats

32,154
Posts
122,048
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.