Skip to main content
Solved

FeatureWriter catch contrain oracle


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 `')).

View original
Did this help you find an answer to your question?

10 replies

erik_jan
Contributor
Forum|alt.badge.img+17
  • 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.


Forum|alt.badge.img
erik_jan wrote:

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.

 


Forum|alt.badge.img
  • 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 `')).


Forum|alt.badge.img
larry wrote:

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?


Forum|alt.badge.img
  • June 20, 2017
mario_aylwin wrote:

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.

 

 


Forum|alt.badge.img
larry wrote:
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+44
mario_aylwin wrote:

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.

 

 


gio
Contributor
Forum|alt.badge.img+15
  • 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.


Forum|alt.badge.img

Yes,

I use the transformer SQLExecutor and that functionne nicely.

Thanks every body.


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 

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings