ION Database Connection Point - Read from Oracle

Hi,

I'm trying to read BODs from a table in an Oracle database using the Database Connection point. I've used the code in the ION technical reference to create a function to read the BODs from the table and have that working. Where I run into issues is when I try to update something to mark the BODs as having already been read so they aren't pulled in again the next time ION calls the function. The technical reference has examples of doing this in SQL Server but not in Oracle. When I tried doing this in my function, I get a "cannot perform a DML operation inside a query" error. When I try using a stored procedure to read the BODs instead of a funtion it doesn't work because I have no idea how to get ION to retrieve the BOD from an OUT parameter. It seems to only be able to retrieve the BOD from the return value of a function. There's nothing in the technical reference for using a stored procedure this way in Oracle, just a function. FYI, I'm a SQL Server guy and am winging it with Oracle. It's a client requirement. Any help would be appreciated.

Greg

Find more posts tagged with

ION Connect

Comments

Legacy Contributor

Hi Greg:

This is a long standing thorn in the flesh of Oracle based DB Connector. The main restriction arises from inherent Oracle architecture. For reasons best known to them, it doesn't allow read and update within the same Stored procedure.

The only way to do that in an oracle SP is using the "pragma AUTONOMOUS_TRANSACTION". See: docs.oracle.com/.../transact.htm . However this method comes with a warning from Oracle thatany transaction initiated this way is independent, the results of the DML will be committed outside of the scope of the parent transaction. In most cases that would not be an acceptable workaround.

In ION's case, we initiate the parent transaction. We do not want an independent transaction that is not in the control of the parent process. Because this may lead to the child transaction resulting in an error, which ION will not be aware of - leaving the parent transaction in a lurch.

For this reason, using this method is NOT RECOMMENDED from the ION team - Which is to say, unfortunately no proper solution to this problem and its a limitation when the DB used is Oracle. Still eternal optimists, who hope that things would never go wrong do use this method (in a rather adventurous way which we do not Endorse!)

Having said that, this problem is in our radar. We continue to investigate if there are other options in Oracle/ Build our own solution to solve this issue. Until now, there is no break-through.

Though my answer doesn't help :-( I hope it at least clarifies where we are with this. As soon as, we have any other official solution this problem, I promise to update this thread :-)

antonio-caria

Hi Greg

Create a SP and not a function.

if the SP is Java Code ( not PL/SQL )

You can call Machine processes which can update DB outside the main process (like SQL Server CLR).

You can also use DBMS_PIPE on PL/SQL ...

You can also use a Daemon Process which controls the records readed...

However I prefer always use "pragma AUTONOMOUS_TRANSACTION" if the Select / update is a simple process.

A.Caria

Legacy Contributor

one of the preferred ways to resolve this issue safely and in a controlled and manageable way is to use a 2 step process. First create a standard set of I/O Boxes in Oracle, using the scripts available from the ION Desk. Set up a standard connection point to read from this outbox. Then also use your DB connector and your current stored procedure / function but instead of it trying to update data and return content, have it simply write the data required to the outbox. Then it will happily update what you need and write to the outbox and return without error. ION will happily read from the outbox and so you have a stable and reliable solution

Legacy Contributor

600774 suggested to use 2 step document flow. I have tried that and it’s working well.

Surprisingly the SP works in oracle where it reads the table and updates without raising any ORA error, where as the same SP when called from ION raises the ORA error. Vignesh explained the reason, not sure what version of oracle he is referring to. I tried in oracle 12. I think whatever is in ION need to be fixed. I

Copyright © 2025 Infor. All rights reserved.