How to call a store procedured from external database

How to call a store procedured from external database

  

Hello! I can already connect to an external database (oracle) and use the tables. I need help on how to call a stored procedure from my oracle database? I have tried some things from the forums but I always get errors.


but this works in an oracle query manager

i also tried this:



This is one link I used: https://www.outsystems.com/forums/discussion/18298/is-there-any-extention-to-call-to-storeprocedure-from-sqlserver/



Hope you can guide me so I may continue my development.  Even any other ways to do it will do.

Thank you.

Hi Kim,

The errors you are getting are database errors from Oracle, which means that your syntax is not correct. A quick Google leads me here, which leads me to believe it should work without the "execute" (but I don't have much experience with Oracle recently).

Hi Kilian,

Thanks for the reply. 

Actually, I ran all the scripts on an oracle database query manager first before I tried them in outsystems advance query widget, and they were all running/ executing just fine.

So i thought there might be other syntax in outsystems


Well, the link I posted above says "Execute is sql*plus syntax", so I assume that you used sql*plus? :)

Also, any ORA or PLS you see in the error message are coming from the Oracle database. In fact, with SQL queries, Service Studio warns you a lot if it doesn't understand the syntax, but doesn't give you any errors: it assumes you know what you're doing :). So basically any error caused by a SQL query will be syntax related.

Did you try it without the "execute"? Does it work? Please let us know.

Hi,

You may want to try executing your SP via the Oracle Connector component. I do believe that using a SQL node to invoke SPs is a bit more forgiving in SqlServer than it is in Oracle.

Regarding select queries, you can still use Aggregates (preferable) and SQL nodes, but don't forget to use Integration Studio to identify the tables / views you want to use. The process is described here. (Note: If using a SQL node, you should use notation {Entity}.[Attribute]).