Oracle user defined function call from Advanced Query

How do I call an Oracle database user defined function from Advance query.  I get the error in the screen shot.  Outsystems version 11.8.8

Hi Ainsworth,

Take a look at this post, see if it helps. :)

Ainsworth Grant wrote:

How do I call an Oracle database user defined function from Advance query.  I get the error in the screen shot.  Outsystems version 11.8.8

 Hello Ainsworth Grant,


When using other then SQL Server DB, it is recommend to use DB name, try to use below syntax:

[DatabaseName].[Schema].FunctionName 


Thanks,

 Sanjay

Also other link that might help, remarks for the comment:


Solution

Found the solution with the help of OutSystems contracted support.

1) The logical database hint is necessary: /* %LoginalDatabase%...*/ .  Yes, between comments symbols

2) This is the tricky part: You have to compile and publish and test it.  The test button in Advanced Query WILL always report an error so you can't rely on that.   This is what I got stuck on because since it errored when Testing I assumed I did something wrong.  Working implementation below.

Solution

Hi Ainsworth

Exactly!! You need that hint!

I'm not 100% sure, but I think you can test it in advanced query if you wrap your statement in an Execute.


Try to use it like:


EXECUTE SELECT INSERT_LDCONFIG.... etc


or


EXECUTE ('SELECT INSERT_LDCONFIG... etc')


This way you may be able to test it without publishing.