what's best practice to call database function in entity impl & SQL value impl

Abdelrahman ElGiar

hello,

1.what's the best practice in ADFto call database function in entity impl ? I have 2 ways:

first way: I call the function in the application module and get the am in entity impl then call the function.

second way: to select the function from dual using SQLValueImpl.

2.when I use SQLValueImpl?

3. you suggest any book or other reference to read about ADF best practice?

Timo Hahn

Your question is hard to answer.

In general, you should not call SQL functions at all (to my knowledge this is the best practice).

In the real world, it's sometimes necessary to do so. From my point of view, it depends on the real use case.

What do you want to call the SQL function for?

Timo

Abdelrahman ElGiar

https://community.oracle.com/tech/developers/discussion/comment/16800057#Comment_16800057

I'm working on transfer package from oracle forms to ADF so many business logic functions in the database and I call it in my ADF application to do many complex calculations so in my case it's necessary to use it.

something like I have a function in DB that returns the current balance of item in Store so I need a transient attribute that returns the current balance of this item.

so I call this function and assign its result to this attribute.

and I need to read more about ADF best practice if there's any good book or reference tell me please

thanks

Abdelrahman ElGiar

https://community.oracle.com/tech/developers/discussion/comment/16800057#Comment_16800057

thanks for reply,

I transfer application from oracle forms to ADF so some of business logic stored in DB Procedures and functions so sometimes I need to call functions that make complex calculations.

ex: I have a function that returns the current balance of item in my store. I need to show the balance of each item on the page so I create a transient attribute and assign the return of the function.

Timo Hahn

SQLValueImpl we work for functions only (I guess as I never used it). It's not type safe. This means you cast the return value but don't know if the cast works until you get an error in the running application.

I always use your first method as it allows to call procedures too and is type safe.

The best way, if possible, is to add the function call directly to the SQL of a VO. However, this only works for data already in the DB. You can't calculate the value of unposted data.

Timo

Abdelrahman ElGiar

https://community.oracle.com/tech/developers/discussion/comment/16800480#Comment_16800480

this is a transient attribute (CurrentCost) in Entity impl

I feel this not good practice so I use first method by calling the function in AM and get AM then call the method in the getter of attribute

or you mean to write it in VO query.

what do you mean with add the function call directly to SQL of VO ?

Billy Verreynne

No need to use SQL when PL/SQL does the exact same without having to use the DUAL table hack.

Best practise from an Oracle perspective is to use bind variables - ALWAYS! (of course except in unusual and justified circumstances)

Not using bind variables means hard parsing most of the time. This is anywhere from 20x slower than a soft parse. Best is of course single parse and reuse the SQL cursor statement handle with new bind values.

E.g.

begin
  :1 := nvl( as_get_cost( :2, to_date(:3,'yyyy-mm-dd') );
end;

where bind variable 1 is the output result, and variables 2 and 3 are the input values supplied by your Java methods/functions.

Abdelrahman ElGiar

https://community.oracle.com/tech/developers/discussion/comment/16800622#Comment_16800622

thanks for the reply,

where I can write this pl/SQL code?

Timo Hahn

Read the doc at

https://docs.oracle.com/en/middleware/developer-tools/adf/12.2.1.4/develop/extending-business-components-functionality1.html#GUID-B93C7B79-73C9-4434-B12E-A7E23479969A

Here you should find all information needed.

Timo