Error in advanced query: Could not find server in sys.servers
Application Type
Traditional Web
Platform Version
11.0.606.0

Hey guys, we are trying to call a stored procedure from an external Oracle Database (we have created a connection and a connector for that database), and we get the following error:

The call is 

exec [BRMDBTST].[BRMDBTST_RO_Connection].[cyta_invoice_bundles].create_bundle

Please advise on how to proceed


Regards,

Marios

 


Hey Marios

Where are you running the stored procedure, is it inside the OutSystems SQL node. I believe there is a syntax issue. '[' and ']' are used for table names in the OutSystems node. I am quite sure you know about this already. I believe there has to be a change in the syntax. 

Can you share your statement and the SQL Executed here. That might help to answer.

Thanks

Hi Ashish,

I'm Marios colleague. Based on the following forum discussion:

https://www.outsystems.com/forums/discussion/14867/how-to-execute-stored-procedures/

The '[]' are not creating any issues. 

Running the sql without [] returns the same error message.

The executed sql is the following:

exec BRMDBTST.BRMDBTST_RO_Connection.cyta_invoice_bundles.create_bundle 'test1','test1','test1','1','5','6';

Hi Marios, 

based on the error message, I would expect that it is running against a SQL server instead of Oracle database. Is it really running on the external database? Check the connection as well in Service Center and validate that you have used an entity that is connected to the Oracle database.

regards Hans

Hey Panos

So is this an external DB, a different DB Catalog, or the same DB that you are trying to run this on. Are the tables available in OutSystems as entities or external Databases.

I am not quite sure if I understand the depth of the problem, but, in the case of an external DB would it be possible to run the procedure using a BPT that reacts to other events of the creation of a record in OutSystems and use a post service that then puts data into the external DB.

If the database is the same a BPT could fulfill what you want to do using the stored procedure I believe, although I have not had much hands-on on the same.

I hope it strikes a thought.

Thanks

@Hans and @Ashish Sarwal,

This is an external Oracle database we are trying to connect to. The connector and the server details are shown below:

We can access the tables on this server, using a connector, but we cannot execute stored procedures.

This is how we call the stored procedures

exec [BRMDBTST].[BRMDBTST_RO_Connection].[cyta_invoice_bundles].create_bundle 

exec [database].[Connection].[oracle_package].stored_procedure_name


Regards,


Marios &Panos


Hi,

As far as I've noticed, OutSystems set the right connection based on the used Entity (having the OutSystems database as default).
So, if you have an external entity 'myOracleEntity' and use that Entity within an SQL, it will use the connection which is set to the extension that contains/wraps the entity 'myOracleEntity'.

when you use just an 'exec', it doesn't see that it has to use another connection than the default connection. 

The article about stored procedures contains a hint  

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */ to switch to another external database. I don't know if it works with oracle as well, you can try it. 

If not, I suggest to do add an output Entity 'MyOracleEntity' (so an entity of the oracle db) and change the statement to something like: 

exec [...];

select '' as column1, etc from {MyOracleEntity}.


good luck!

Hans

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.