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

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,


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

Hi Dimce,

I don't think expanding timeouts will be a solution.
Normally problems have to do with user-permissions.

The simplest way to test the permissions is by logging in into SqlPlus or SqlDeveloper with the user used to define the database-connection. If the user can select tables used by integrationstudio, than the problem is at least not the lack of permissions.

After selecting the databaseconnection, integration studio starts with a list of (user)schema's. The user therefor needs persmissions on the right Oracle system-tables. I don't know if the all_tables, DBA_tables or user_tables is used. 

After selecting the (user)schema, a list of available tables and views within the (user)schema is shown.
And after selecting a table/view, the entity and it's columns is generated. Here Oracle system-tables are used.

In your situation, I think the simplest way is allowing the user to select all system tables.

I hope this will help you to take the next steps.

Kind regards,
Eric

Solution

Eric Slikker wrote:

Hi Dimce,

I don't think expanding timeouts will be a solution.
Normally problems have to do with user-permissions.

The simplest way to test the permissions is by logging in into SqlPlus or SqlDeveloper with the user used to define the database-connection. If the user can select tables used by integrationstudio, than the problem is at least not the lack of permissions.

After selecting the databaseconnection, integration studio starts with a list of (user)schema's. The user therefor needs persmissions on the right Oracle system-tables. I don't know if the all_tables, DBA_tables or user_tables is used. 

After selecting the (user)schema, a list of available tables and views within the (user)schema is shown.
And after selecting a table/view, the entity and it's columns is generated. Here Oracle system-tables are used.

In your situation, I think the simplest way is allowing the user to select all system tables.

I hope this will help you to take the next steps.

Kind regards,
Eric

Hi Eric,


many thanks for your tipps and efforts.

I've got the solution:). It worked only when I created the DB-connection with the SYS-user like this:

User Id=SYS; Password=*****;DBA Privilege=SYSDBA;Data Source=IP:1521/oracle_cloud_db_domain

As I stated before, SQLPlus and SQLDeveloper are fully functional using the SYSTEM-user in Oracle and another user-schema which I created.

I made another try with Visual Studio making a short test program connecting to my Oracle Cloud Database. Also there it works.

Then I used the "Monitor Session" from within SQLDeveloper. There I was able to identify the session Integration Studio was trying to connect to the OCD. The SQL was this one:

select distinct owner from ((select distinct owner from all_tables) union (select distinct owner from all_views))

Then I granted SELECT-access to both tables to the SYSTEM-user and to mine own user. None of these works in Integration Studio.

Further I granted access to the tables-DBA_TABLES, USER_TABLES, USER_ALL_TABLES, DBA_ALL_TABLES.

All these steps didn't help connecting with another user than the  SYS one.


I couldn't imagine this would be that heavy...


Kind regards,

Dimce


Solution

Hi Dimce,

nive to hear things are working.
Maybe you can make two database-connections: one for development and one for deplayment.

The development-one needs the rights to select the system-tables.

When you deploy, the deployment database-connection can be choosen. This connection doesn't need (I think so, I'm not completely sure) the system-rights and only needs permissions on the used database-objects.

The database-connection of the extension can be choosen on different moments and places:
- when publishing the extension a database-connection must be choosen;
- when deploying the extension with lifetime a database-connection must be choosen;
- in servicecenter, on the extension, in the tab 'operation' the database-connection can be choosen.

And, I would really like it when you can explain how to make an allways-free Oracle cloud database avaliable for a private outsystems environment. This will give me opportunities to try Oracle-solutions within Outsytems.

Kind regards,

Eric

Hi Eric,


thanks for your tips. I will consider it, as soon as I publish the application.

I will try to create a document about the setup of the free Oracle Clodu Database. I will come back to you again.


Kind regards,

Dimce