ORA-12537 when creating an ORACLE database connection

Hello,

I am getting the following error message when creating an ORACLE database connection, when clicking the button "Test Connection":

Connection String test failed: ORA-12537: Network Session: End of file

I have already searched in the community and Google after the error message. The post "ORA-12537: Network Session:End fo file Error" under https://www.outsystems.com/forums/discussion/18490/ora-12537-network-sessionend-fo-file-error/ hasn't brought me a solution.

The database is working perfectly in the Oracle Cloud (SQL Developer).

I have tested many various combination in the creation of the database connection. Here is one example:

What I am nout sure, is the entry under "Service Name". While on older Oracle database by default it is ORCL, I could not find this information on my cloud database. 

I also tried with the "Advanced configuration". Here is also an example of it:


The version of Service Center is 11.7.3 (Build 5968).


What could be the issue here?


Cheers,

Dime

Hi Dimce,

As suggested in the post give it a try by adding  Min Pool Size=XX; and/or Max Pool Size=YY;, where XX and YY are the values you want to configure the connection pool size with.

Regards,

Swatantra

Hi Swatantra,


no, it's definitely not an Outsystems-issue. Meanwhile I found out, the publishing an Oracle Cloud database is rather complicated (virtual cloud network, public subnet, port release and such things). So, I am still not able to connect to the Oracle Cloud database using an Oracle-Console tool SQLPlus on my local machine. At the moment I guess it is port release issue.

As soon as I manage the connection from the SQLPlus-tool, I will create the Outsystems-database connection.

Thanks for your reply.


Kind regards,

Dimce

If you are using same db with outsystems then can also check using the configuration manager tool for connection through RDP

Solution

Hi,


I found the issue. In the whole configuration of the Oracle Cloud Database there was missing "Route Rules". Once I created the route rule, I was able to establish the Oracle Connection out of the OutSystems-framework.


Kind regards,

Dimce

Solution

Hi Dimce,


Good to see,you got the solution.

Happy coding :)

Dimce Tumbov wrote:

Hi,


I found the issue. In the whole configuration of the Oracle Cloud Database there was missing "Route Rules". Once I created the route rule, I was able to establish the Oracle Connection out of the OutSystems-framework.


Kind regards,

Dimce

Ah, check.

Routing rules... over cloud. Good catch.


Dimce Tumbov wrote:

Hi,


I found the issue. In the whole configuration of the Oracle Cloud Database there was missing "Route Rules". Once I created the route rule, I was able to establish the Oracle Connection out of the OutSystems-framework.


Kind regards,

Dimce

Hi Dimce,

looks like you had a deep dive into the Oracle/Outsystems connection.
Do you see a way to connect a private oracle cloud-database to a private outsystems environment?
Would be nice to do some testwork.

Kind regards,
Eric


Hi Eric,


I am just trying to do that.

Latest update is, I am struggling with the erorr messages ORA-01013 and ORA-00604 in the Integration Studio. This happens as soon as I try to connect the external (Oracle) tables.

So far, no similar post brought me the solution including this one:

https://www.outsystems.com/forums/discussion/45239/error-ora-01013-by-adding-database-connection-in-wizard/

My OutSystems environment is a free one.


Keep you posted...


Kind regards,

Dimce

Hi Dimce,

just to make sure. 

You have an (external) Oracle cloud database that's not within a VPN-construction. So the connection is completely over the public internet. (In my question I meant an allways free database-subsctiption from Oracle.)

And you managed to make a database-connection in servicecenter, that passes the test-connection.

Now you use integration studio to define an external table for this database-connection and this works. You are able to define the external table in integration studio and this integration will publish without errors. This means that the connection really works.

However, when you use the external table from within outsystems (advanced SQL or aggregate), you get  the errors above?

If you can explain me how to configure this Oracle cloud to make this database connection in servicesstudio work, I can try to make the external table work.

Because I didn't get the databaseconnection defined in servicestudio in a way it passes the test, I created this idea: https://www.outsystems.com/ideas/7347/using-wallet-for-external-database-connection-to-oracle-cloud-database.
I still think this is a good way for implementing a secure databaseconnection to the oracle cloud.

Kind regards,
Eric

Hi Eric,


please find my replies inline.


One additional point: I don't understand how it is possible to connect to my Oracle database and to create/view database tables from within SQL Developer (on my local machine), but not from the OutSystems Integration Studio?

Kind regards,

Dimce


Eric Slikker wrote:


Hi Dimce,

just to make sure. 

You have an (external) Oracle cloud database that's not within a VPN-construction. So the connection is completely over the public internet. (In my question I meant an allways free database-subsctiption from Oracle.)

DT: There is a VPN construction for my Oracle cloud database (OCD). Nevertheless, I have a public IP address of the OCD and I am able to connect from within the SQL Developr tool on my local machine.

And you managed to make a database-connection in servicecenter, that passes the test-connection.

DT: Yes. 

Now you use integration studio to define an external table for this database-connection and this works. You are able to define the external table in integration studio and this integration will publish without errors. This means that the connection really works.

DT: No. I have the issue at this point. As soon as I select my database connection I expect the database tables to be displayed in the "Import-Wizard". But this never happens. It breaks with the errors ORA-01013 and ORA-00604.

Here are the steps explained with screenshots:

1.



2. After pressing "Next" on this screen

3. After approx. 3 minutes comes the error message. Sometimes it is only ORA-01013 and sometimes also the ORA-00604.

However, when you use the external table from within outsystems (advanced SQL or aggregate), you get  the errors above?

If you can explain me how to configure this Oracle cloud to make this database connection in servicesstudio work, I can try to make the external table work.

DT: Although I have made screenshots from the OCD-setup, it's pretty complicated to explain to someone else. I would rather create a new user scheam in my OCD and announce it to you. What do you say?

Because I didn't get the databaseconnection defined in servicestudio in a way it passes the test, I created this idea: https://www.outsystems.com/ideas/7347/using-wallet-for-external-database-connection-to-oracle-cloud-database.

DT: That would be great if it ever happens.
I still think this is a good way for implementing a secure databaseconnection to the oracle cloud.

Kind regards,
Eric



Hi again,


my latest findings are, the error messages ORA-00604 and ORA-01013 are comming due to a timeout. As mentioned above the time frame after which the errors appear is 3 min.

The timeout issue is discussed here:

https://stackoverflow.com/questions/33800974/catch-time-out-exception

and the "CommandTimeout" parameter is explained here:

https://docs.oracle.com/cd/E63277_01/win.121/e63268/OracleCommandClass.htm#DAFBGECA

But, I can not find where can I set this timeout. Maybe in the database connection under "Configuration Parameter"? If yes, what is the name of this parameter?


Kind regards,

Dimce