How to call Procedure function in UI page

-

 

Procedure call

First we need to call procedure  query in HR connection we have a column as procedures in that we have create GET_EMPLOYEES in that run this query.


-------------------------- ------------------------------------------------ --------------------------------------------

 

create or replace

PROCEDURE GET_EMPLOYEES

(

  EMPID IN NUMBER

, FNAME OUT VARCHAR2

, LNAME OUT VARCHAR2

, EMAIL OUT VARCHAR2

, DEPTID OUT NUMBER

, SALARY OUT NUMBER

) AS

BEGIN

  select first_name,last_name,email,department_id,salary INTO FNAME,LNAME,EMAIL,DEPTID,SALARY from employees

  where employee_id=EMPID;

  dbms_output.put_line('Hello World 2022!');

  dbms_output.put_line(FNAME);

   dbms_output.put_line(LNAME);

  dbms_output.put_line(EMAIL);

   dbms_output.put_line(DEPTID);

  dbms_output.put_line(SALARY);

END GET_EMPLOYEES;

 

----------------  ------------------------------- ----------------------------------------------------------------------------

We have to create new application

 


We need to create application module in business component table

 


 

Generate classes

 


 

This code for AMimpl class

 

import java.sql.CallableStatement;

import java.sql.SQLException;

import java.sql.Types;

 

import java.util.ArrayList;

 

import model.common.AppModule;

 

import oracle.jbo.JboException;

import oracle.jbo.server.ApplicationModuleImpl;

import oracle.jbo.server.DBTransaction;

 

 

 

 

 

    public ArrayList callStoredProcOutParam(oracle.jbo.domain.Number EMP_ID) {

        ArrayList <String> Emplist = new ArrayList<String>();

    String Fname;

    String Lname;

    String Email;

    String DeptID;

    String Salary;

   

   

    DBTransaction trx =(DBTransaction)getTransaction();

    CallableStatement st = null;

    try  {

    // 1. Define the PL/SQL block for the statement to invoke

    String stmt = "begin GET_EMPLOYEES (?,?,?,?,?,?); end;";

    // 2. Create the CallableStatement for the PL/SQL block

    st = trx.createCallableStatement(stmt,0);

    // 3. Register the positions and types of the OUT parameters

    st.registerOutParameter(2,Types.VARCHAR);

        st.registerOutParameter(3,Types.VARCHAR);

        st.registerOutParameter(4,Types.VARCHAR);

        st.registerOutParameter(5,Types.VARCHAR);

        st.registerOutParameter(6,Types.VARCHAR);

   

    // 4. Set the bind values of the IN parameters

    st.setObject(1,EMP_ID);

   

    // 5. Execute the statement

    st.executeUpdate();

    //6. Set value of _xErrCode  property using first OUT param

    Fname =  st.getString(2);

    Lname =  st.getString(3);

    Email =  st.getString(4);

    DeptID = st.getString(5).toString();

    Salary = st.getString(6).toString();

       

        Emplist.add(Fname);

        Emplist.add(Lname);

        Emplist.add(Email);

        Emplist.add(DeptID);

        Emplist.add(Salary);

       

       

    //7. Set value of _xErrMsg property using 2nd OUT param

   

    // 8. Return the result

        return Emplist;

    } catch (SQLException e)  {

                                 throw new JboException(e);

    } finally  {

    if (st != null) {

    try {

    // 9. Close the JDBC CallableStatement

    st.close();

    }

    catch (SQLException e) {

    e.getStackTrace();

 

    }

    }

    }

 

 

 

Now we need to move this method in client inter face in right side

 


 

 

Move to right side (Array List)

 


Now cerate Method bindings for CallStroedProcOutParam, in Bindings

In UI page right click & select go to Page Definition.

 


 

Select here Method Action

 



Click on ok

Save all then Run App Module


 


Give value here as empid, then hit the Execute button


 

Now it shows the Return value

 


 

Put the input text in our UI page

 


 

Here one in put and one button five out put texts drop in our UI page

 


 


 

Create bean for the Execute button as Action Listener

 


 


 


 

 

 

 

 

 

Code for Button bean

 

 ---------------------- ---------------------------------------------------------

 

   private String EmpId;

    private String fname, lname, email, deptid, salary;

 

    public void buttonClicked(ActionEvent actionEvent) {

        // Add event code here...

        BindingContainer bindings = getBindings();

                        OperationBinding operationBinding =

                        bindings.getOperationBinding("callStoredProcOutParam");

                       operationBinding.getParamsMap().put("EMP_ID",this.EmpId);

                    operationBinding.execute();

        ArrayList<String> Emplist1 = (ArrayList<String>)operationBinding.getResult();

        //                        String[] result = (String[])operationBinding.execute();

       

        this.setFname(Emplist1.get(0));

        this.setLname(Emplist1.get(1));

        this.setEmail(Emplist1.get(2));

        this.setDeptid(Emplist1.get(3));

        this.setSalary(Emplist1.get(4));

        }

        public BindingContainer getBindings() {

        return BindingContext.getCurrent().getCurrentBindingsEntry();

        }

 

        public void setEmpId(String EmpId) {

        this.EmpId = EmpId;

        }

 

        public String getEmpId() {

        return EmpId;

        }

 

        public void setFname(String fname) {

        this.fname = fname;

        }

 

        public String getFname() {

        return fname;

        }

 

        public void setLname(String lname) {

        this.lname = lname;

        }

 

        public String getLname() {

        return lname;

        }

 

        public void setEmail(String email) {

        this.email = email;

        }

 

        public String getEmail() {

        return email;

        }

 

        public void setDeptid(String deptid) {

        this.deptid = deptid;

        }

 

        public String getDeptid() {

        return deptid;

        }

 

        public void setSalary(String salary) {

        this.salary = salary;

        }

 

        public String getSalary() {

        return salary;

        }

----------------------------------------------------- --------------------------


Click on Generate Accessors, & Click on all boxes

Here it creates getter, seters for these values

 


Select input text go to property inspector select value select expression builder and select required column for input text field


 

Now pass the value to all input text in right side


Run the page


Here give empid then hit the Execute button for output value

 


 

FARHATH

 

 

 

 

 

 

 

Comments

Post a Comment

Popular posts from this blog

Programmatically invoke a Popup to create a new row.

-
Image
  Programmatically invoke a Popup to create a new row. Create an Application . Create the model part with Employee Table. Create EmployeeEO, EmployeeVO and AppModule. Now in the View controller project create a page (Employee.jspx). From the Data control drag and drop EmployeeVO1 (EmployeeVO instance) in to the page as Read only table. Surround this table with Panel Box. In the tool bar facet of panel box, drop a command button from the component pallate. Drag and drop Popup on to the page. Inside popup drop a dialog box. Create a ActionListenerEvent for the button "New". And bind RichPopup to the bean. Code for button.............       public void displayPopUp(ActionEvent actionEvent) {         // Add event code here...                  BindingContext bctx=BindingContext.getCurrent();         BindingContainer bctr=bctx.getCurrentBindingsEntry();         Operatio...
Read more

POJO(Plain Old Java Object) - Editable Data - DataControl.dcx

-
Image
POJO(Plain Old Java Object) - Editable Data - DataControl.dcx     Without using EO’s & VO’s we can create a UIPage with Java Class. First we need to create a Java Classes name it as a Entity Java Classes (i,.e. DepartmentsEO, EmployeesEO, LocationsEO.) Then Again create another three java classes for storing data in Department List Java class, Employee list, Location List. Below image for Creating Java classes.  End of the Page have a ZIP file  This JAVA class for creating list of Data (Dept list, Emplye list, location list) Now we have to create Another Java class for calling the All the above created java classes( This Java Class Act like APPMODULE ) Name it as Session Façade. After Creating Session Façade Java Class. Just Right click on the java class, in that options select the Create Data Control. CREATING DATA CONTROL After Creating Data control, All the java classes automatically created .XML files . Java Code for DepartmentsEO,   EmployeesEO, Locatio...
Read more

Dynamic Tabs in ADF

-
Image
First we have to create Departments and Employees(EO's,VO's,AM) Create Two task-flows ( Departments and Employees Creating JSPX page in adfc-config.xml Now Create to take " Oracle Dynamic Tabs Shell " (Max 15 tabs Only) In page template we have navigation facet, in that drag drop two links from component palette , for that link create ActionListener to write the java code in that.                                                            public class launcher {     public void openDepartmentsAsMain(ActionEvent actionEvent){         try{             TabContext.getCurrentInstance().setMainContent("/WEB-INF/dept-task-flow#dept-task-flow");         } catch (TabContext.TabContentAreaDirtyException e) {         }     }     pub...
Read more