Skip to main content

A sample ADF BC application based on StoredProcedure

Sometimes you may need to use stored procedures/functions to read/write data from underlying tables. You can still leverage the benefits of ADF persistence layer by smartly wrapping the stored procedures using ViewObjects and EnityObjects.
I’m sharing a sample ADF BC application that uses stored procedures for reading and writing to the table.

Solution is simple and straight forward...
1. Create an EntityObject and override the below methods
   protected void doSelect(boolean lock)
   protected void doDML(int operation, TransactionEvent e)
Please refer the below topic in Fusion Developer's Guide to learn more …
38.5 Basing an Entity Object on a PL/SQL Package API

2. Define a ViewObject based on the above EntityObject. Override the below 'life cycle methods' to inject your custom code(for populating data form a REF CURSOR).
  protected void create()
  protected void executeQueryForCollection(Object qc, Object[] params, int numUserParams) 
  protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet rs)
  protected boolean hasNextForCollection(Object qc)
  protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet rs)
  protected void releaseUserDataForCollection(Object qc, Object rs)

Please refer the below topic in Fusion Developer's Guide to learn more…
39.8 Using Programmatic View Objects for Alternative Data Sources

You can download the sample workspace from here.
[Runs with Oracle JDeveloper 11g R1 PS1]
PS: Unzip the attachment and run the script(StoredProcedureExample/script) to set up the required DB objects for running this demo

Learn More ...

There are a lot more points like this. If  you are curious to learn the internals of the ADF Business Components and ADF Binding Layer,  the following book is for you - Oracle ADF Real World Developer’s Guide.
More details about this book can be found in this post- http://jobinesh.blogspot.in/2012/07/pre-order-your-copy-of-oracle-adf-real.html

Comments

JCFlack said…
You and your readers might also want to check out the PL/SQL API extension to ADF BC that was originally written by Avrom Roy-Faderman. It is in the Oracle Samplecode repository site at: https://database-api-based-adf-bc.samplecode.oracle.com/. You'll need an oracle.com id and registration in the samplecode site to see it, but this is free.
Jobinesh said…
Great news JCFlack. Thanks for sharing this information! Seems very promising. Will explore the project in detail, later this week.
Martin said…
Nice working sample, thank you. But I can't sort columns in table based on stored procedure. Is is possible to implement sorting for columns in such a table? Thanks
Jobinesh said…
Martin,
Sure, its possible. Will try to upload a working sample ASAP
zeeshan said…

Hi
This post is very useful. I am sharing a sample code of a procedure to make a problem brief. This procedure is used for insertion of patient's data. This procedure takes collection as IN parameter.

TYPE PATIENT_REC IS RECORD(
MRNO REGISTRATION.PATIENT.MRNO%TYPE,
PATIENT_TYPE_ID REGISTRATION.PATIENT.PATIENT_TYPE_ID%TYPE,
PATIENT_TYPE DEFINITIONS.PATIENT_TYPE.DESCRIPTION%TYPE,
FIRST_NAME REGISTRATION.PATIENT.FIRST_NAME%TYPE,
MIDDLE_NAME REGISTRATION.PATIENT.MIDDLE_NAME%TYPE,
LAST_NAME REGISTRATION.PATIENT.LAST_NAME%TYPE,
FATHER_NAME REGISTRATION.PATIENT.FATHER_NAME%TYPE,
NIC REGISTRATION.PATIENT.NIC%TYPE);


TYPE PATIENT_CUR IS REF CURSOR RETURN PATIENT_REC;

TYPE PATIENT_TAB IS TABLE OF PATIENT_REC INDEX BY BINARY_INTEGER;

PROCEDURE PATIENT_INS(R IN PATIENT_TAB,
P_OUT_MRNO OUT VARCHAR2,
P_ALERT_TEXT OUT VARCHAR2 );

Can we make entities on the basis of such procedures?
Can we pass such collection from front end to this procedure in business layer by identifying the rows with status Status_NEW in ADF?
What could be the best practice for such procedures?
Now please guide me if there is any possibility to call such procedures.

Regards:
Zeeshan Ali Akbar
ADF Development Team
Viral Patel said…
Hi Jobinesh,
Great sample.
I have implemented the same but unfortunately I am not able to sort columns in table based on stored procedure.

As per previous comments it is possible to implement sorting for columns in such a table.

Will you please upload the sample code for the same???

I have refereed your another blog

http://www.jobinesh.com/2013/05/explicitly-enabling-in-memory-sorting.html

but not able to merge both of them. :(

Please upload the sample code with sorting.

- Viral

Popular posts from this blog

How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1"); vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10)); vo.executeQuery();
2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Ma…