Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
what's best practice to call database function in entity impl & SQL value impl

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?
Answers
-
Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,570 Red Diamond
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
-
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
-
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 Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,570 Red Diamond
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
-
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 ?
-
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.
-
thanks for the reply,
where I can write this pl/SQL code?
-
Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,570 Red Diamond
Read the doc at
Here you should find all information needed.
Timo