[Oracle Connector P9] Oracle Connector P9(v 2.0.1) does not work with OS10

[Oracle Connector P9] Oracle Connector P9(v 2.0.1) does not work with OS10

  
Forge Component
(5)
Published on 2015-03-13 by Grazina
5 votes
Published on 2015-03-13 by Grazina

Hi,

After I upgraded from OS 9.0 to OS 10.0, I also upgraded Oracle Connector P9 to version 2.0.1. When my app calls ExecuteSP action, I got the following error:


"[A]Oracle.ManagedDataAccess.Client.OracleTransaction cannot be cast to [B]Oracle.ManagedDataAccess.Client.OracleTransaction. Type A originates from 'Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342' in the context 'LoadFrom' at location 'D:\Outsystems\Temporary ASP.NET Files\callingcampaign\a3dafb23\535155cb\assembly\dl3\739e1c31\00c70b51_cf2ed201\Oracle.ManagedDataAccess.dll'. Type B originates from 'Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342' in the context 'Default' at location 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Oracle.ManagedDataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.ManagedDataAccess.dll'."


Is there a way to make it work with OS10? My apps relies on this action a lot.

Thank you for helping. 

Best regards,

Peter A.


 

Well... version 2.0.1 says it's compatible with version 9.1.300.0 of the platform. Not version 10.

Meanwhile you can use Advanced Queries to call your SPs.


Hello,

The problem is the DLL that's bundled with the connector has a different version than the one the system already has loaded.

I haven't tried it yet, but you could could try to get hold of the Type B file ('C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Oracle.ManagedDataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.ManagedDataAccess.dll) and replace that dll from inside the .net part of the extension (which is Type A)

Let me know if that does the trick for you.


Cheers,

João Grazina

Grazina wrote:

Hello,

The problem is the DLL that's bundled with the connector has a different version than the one the system already has loaded.

I haven't tried it yet, but you could could try to get hold of the Type B file ('C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Oracle.ManagedDataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.ManagedDataAccess.dll) and replace that dll from inside the .net part of the extension (which is Type A)

Let me know if that does the trick for you.


Cheers,

João Grazina


Hi Grazina,

I host my apps with OutSystems cloud server. I tried using FileSystem extension to copy and replace
the file you suggested but the access was denied. When I asked OS tech support to do this for me. They said: 

"The FileSystem extension only offers access to your local virtual folder available at D:\User\. For security reasons, no other folder is accessible to our customers and we don't support changes in these other folders.
We suggest you find an alternative, either by working with the developer of the Forge Component to release a version compatible with version 10, or by altering your applications to not be dependent upon this Forge Component."

Could you help make your extension to work with OutSystems 10? I do not know the other way to run Oracle
stored procedues (within .NET stack) which have input and output parameters. If you cannot, is there any other workarounds? Several of my company apps depend on your extension.

Thank you.







Hi Peter,

Yes, it's trickier from on the cloud.

I had the same question coming from a private chat. Suggestion is to open the extension, from visual studio use nuget to update the driver to the latest version, save and publish.

https://www.nuget.org/packages/Oracle.ManagedDataAccess/12.1.24160719

I haven't tried it myself, since I don't have an environment to test on at the moment, but it worked for him. Let me know if it does for you.

Kind Regards,

João Grazina

Hi, you can use Oracle package for workaround.

- create package header, and declare (static) variable on package header spec

- create package body to call your stored procedure, and store the output on package variable


In Outsystems, you use 2 Advance Queries/SQLs:

1. call the package procedure (using exec package.proc(input); or begin package.proc(input); end;)

2. select package.variable1, package.variable2, package.variable3 from dual


Hope it helps...

Grazina wrote:

Hi Peter,

Yes, it's trickier from on the cloud.

I had the same question coming from a private chat. Suggestion is to open the extension, from visual studio use nuget to update the driver to the latest version, save and publish.

https://www.nuget.org/packages/Oracle.ManagedDataAccess/12.1.24160719

I haven't tried it myself, since I don't have an environment to test on at the moment, but it worked for him. Let me know if it does for you.

Kind Regards,

João Grazina

Hi Grazina,

Thank you for your help.

After updating and publishing driver, the old error message is gone. Now, I got a new one saying:

"Connector unsupported type : STGetDonorInParam1Structure". So far, I did not change my input

parameter for the ExecuteSP action at all. It is "Record" type of a structure. The data type of the structure's field is either text or integer. The code has been working fine so far up until the platform upgrade from 9.0 to 10.0. 

I saw in your version note that "The OracleTypes are not supported on this version yet" in version 2.0.1.
Is that the reason why I got this error?


Peter Areewatanakul wrote:

Grazina wrote:

Hi Peter,

Yes, it's trickier from on the cloud.

I had the same question coming from a private chat. Suggestion is to open the extension, from visual studio use nuget to update the driver to the latest version, save and publish.

https://www.nuget.org/packages/Oracle.ManagedDataAccess/12.1.24160719

I haven't tried it myself, since I don't have an environment to test on at the moment, but it worked for him. Let me know if it does for you.

Kind Regards,

João Grazina

Hi Grazina,

Thank you for your help.

After updating and publishing driver, the old error message is gone. Now, I got a new one saying:

"Connector unsupported type : STGetDonorInParam1Structure". So far, I did not change my input

parameter for the ExecuteSP action at all. It is "Record" type of a structure. The data type of the structure's field is either text or integer. The code has been working fine so far up until the platform upgrade from 9.0 to 10.0. 

I saw in your version note that "The OracleTypes are not supported on this version yet" in version 2.0.1.
Is that the reason why I got this error?


I tested different cases and here is the result:

1. Procedure without input and output parameters:  OK
2. Procedure with an integer parameter: Fail with "Connector unsupported type" message
3. Procedure with a text parameter: Fail with "Connector unsupported type" message
4. Procedure with I/O parameters: Fail with "Connector unsupported type" message

It looks like the "convertToOracleType" method in your extenion does not work.

Harlin Setiadarma wrote:

Hi, you can use Oracle package for workaround.

- create package header, and declare (static) variable on package header spec

- create package body to call your stored procedure, and store the output on package variable


In Outsystems, you use 2 Advance Queries/SQLs:

1. call the package procedure (using exec package.proc(input); or begin package.proc(input); end;)

2. select package.variable1, package.variable2, package.variable3 from dual


Hope it helps...

Hi Harlin,

Thank you.

I have tried your workaround but it failed when running the second SQL with the error message:

"ORA-06553: PLS-221: 'OUT_TEST' is not a procedure or is undefined"

It seems like the second SQL used a different session from the first one; therefore, it had no access to the result

of the first SQL. If I run the attached script in TOAD manually, everything work as expected. (see the attached picture)

Did I miss anything?


 



Peter Areewatanakul wrote:

Harlin Setiadarma wrote:

Hi, you can use Oracle package for workaround.

- create package header, and declare (static) variable on package header spec

- create package body to call your stored procedure, and store the output on package variable


In Outsystems, you use 2 Advance Queries/SQLs:

1. call the package procedure (using exec package.proc(input); or begin package.proc(input); end;)

2. select package.variable1, package.variable2, package.variable3 from dual


Hope it helps...

Hi Harlin,

Thank you.

I have tried your workaround but it failed when running the second SQL with the error message:

"ORA-06553: PLS-221: 'OUT_TEST' is not a procedure or is undefined"

It seems like the second SQL used a different session from the first one; therefore, it had no access to the result

of the first SQL. If I run the attached script in TOAD manually, everything work as expected. (see the attached picture)

Did I miss anything?


 



I think you need to specify schema name and grant execute to OSRUNTIME?

E.g:

select myschema.package.var1 from dual

Outsystems will access your package via OSRUNTIME user, so you need to grant execute. 


Solution

Ups, I forgot that you cannot access package variable directly via select.

You need to create getter function inside package.

Here's my sample, I have test it and it works...


create or replace package test_os_pkg as
  result1 varchar2(100);
  result2 varchar2(100);
  
  procedure convertcase(input varchar2);
  function getresult1 return varchar2;
  function getresult2 return varchar2;
end;
/
create or replace package body test_os_pkg as
 
  procedure convertcase(input varchar2) as
  begin
    result1 := lower(input);
    result2 := upper(input);
  end;  
  
  function getresult1 return varchar2 as
  begin return result1; end;
  
    function getresult2 return varchar2 as
  begin return result2; end;
end;
/




I use External Database, so the need for setting %LogicalDatabase%

Hope it helps...

Solution

Hi, since this can save many people using Oracle DB, I have put this how to on separate post:

http://www.outsystems.com/forums/discussion/21999/how-to-call-oracle-stored-procedure-with-outputs-using-advance-query-sql

Any questions, please reply there...

Harlin Setiadarma wrote:

Hi, since this can save many people using Oracle DB, I have put this how to on separate post:

http://www.outsystems.com/forums/discussion/21999/how-to-call-oracle-stored-procedure-with-outputs-using-advance-query-sql

Any questions, please reply there...

Hi Harlin,

Thank you so much for helping. It works!

I also found a link which explains about how to create a general "getter" function to get a package variable

from any package. Here is the link if anyone is interested:

http://www.talkapex.com/2010/03/how-to-reference-package-variables/



Peter Areewatanakul wrote:

Harlin Setiadarma wrote:

Hi, since this can save many people using Oracle DB, I have put this how to on separate post:

http://www.outsystems.com/forums/discussion/21999/how-to-call-oracle-stored-procedure-with-outputs-using-advance-query-sql

Any questions, please reply there...

Hi Harlin,

Thank you so much for helping. It works!

I also found a link which explains about how to create a general "getter" function to get a package variable

from any package. Here is the link if anyone is interested:

http://www.talkapex.com/2010/03/how-to-reference-package-variables/



Thanks, that's good to know... 

I will mention this link on my how to page.