How to install Oracle Database 12C Enterprise Edition on Windows

In this section, I explained how to install Oracle database 12C on Windows machine 

Install Oracle Database12C

 1. Go to the Oracle Database Download page, Accept the License Agreement, Download files for Windows

2. Unzip the downloaded zip file into a folder

3. Go into the unzipped folder and Double-click on the setup.exe

4. The installer opens on the screen. Input Oracle Support credentials if available, or select the check box ‘I wish to receive security updates’ and Click Next

5. Click Yes for the pop-up. This popup prompt If the check box was selected above

6. Select the option Create and Configure a Database and click Next

7. Choose the option you want and click Next

8. Select the option you want. I’ve created a Windows user account 

9. This screen is important. Input the details corresponding to your database

• Oracle Base: Input the location where you want to install the database
• Character Set: We8MSWIN1252/AL32UTF8
• Global Database Name: Service Name
• Administrative Password: SYSDBA Password
• Create as Container Database: Yes/No

10. Click on the button Install

11. The installation is in progress. It would take 30 minutes depends on the system performance. Click Allow Access if it prompts any

12. The configuration assistant wizard opens on another screen and will progress

13. Click OK once it completes 100% 

14. We completed installing Oracle Database 12c. Click on the button Close

 

Oracle DB Installation Directories

15. The Oracle Database in installed now. The database configuration files can be view inside the Windows start Menu. Click Start >> Oracle – OraDB12Home

16. The Database is installed C:\app directory by default. Check the location mentioned in step 8

17. There are Windows services installed for Oracle Database. Go to Windows services; The services similar like below

 

Connect to your database

1. Open a command prompt and connect to the database 

1 2 3

Sqlplus sys/<Password>@orcl as sysdba

How to set up a Data Source in BI Publisher

Before start developing a report, the data source must be setup in BI Publisher. The data source is simply a database connection the report is looking for data to generate reports. For example, a user wants to generate a report that displays the list of countries. Here the list of countries may come from a database table.

In this section, we detailed how to set up a data source in BI Publisher 12C.

Version User: Bi Publisher 12.2.1

Setup Data Source in BI Publisher

1. Login into XMLPSERVER as WebLogic admin. The default user is http://HOST:PORT/xmlpserver

2. Click on the link Administrator on the top right corner

3. Click JDBC Connection under Data Sources

4. Click the button Add Data Sources. You can create multiple data sources 

5. Enter your database connection details that you are going to generate a report. Also, input pre/post process function if any. That’s it.We are done. 

Pre Process Function: Enter a PL/SQL function that you want to execute when a database connection is created. The function must return a Boolean

Post Process Function: Enter a PL/SQL function that you want to execute when a database connection is closed. The function must return a Boolean

Note: I have entered the HR schema here which is a demo schema bundled with Oracle database. By default this is locked, to unlock it, log in to your database as system user and issue the query

1 2 3

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

How to create report in bi publisher

This section we walk through on create report in BI Publisher. The following are the summary of steps needed to create a Oracle BI report

1. Create a Data Model in BI Publisher

2. Create a Report in BI Publisher

Pre-Requisite

 A data source must be established before creating a data model or report. Click on the link to create an HR Data source connection in BI Publisher

 1. Create a Data Model in BI Publisher

A data model is a collection of object work together to produce data for a report to generate.A data model requires data to read; it might be a database table, excel file, LDAP, etc.  For this sample report creation, we have used HR schema COUNTRIES table to pull the country names. Let’s begin to create a Data Model in BI Publisher 

1. Login into BI Publisher as BI Administrator

2. Click New and select Data Model from the drop down

3. Click on the Save icon to save the data model, create a folder and input a folder name for where you want to save the data model, input a data model name and Click Save

4. Now click Add and select SQL Query

5. Input a name, Select the data source as HR (See Prerequisite), Click On the Query Builder, Select Country table, Select the field Country Name. Click Save and Ok again. The BI Publisher wrote the query for you.

6. Click on the DATA tab, Click View and & Click Save a Sample Data

7. Click on the properties, ensure the sample.xml will be present, and click Save

We are done creating a data model. 

 

Create a Report in BI Publisher

A report is simply read data from a data model and visualize in a  graphical report. BI Publisher supports many visual formats like chart, graphs, table etc. For this demo purpose, we read the country names from the data model and display it in a table. 

Let’s begin to create a BI Publisher report  

8. Click New and select Report

9. Choose the Data Model you just created above and open it

10. Select the table and click Next

11. Choose the Country_Name & Click Next

12. Click View Report & Click Finish

13. Save the BI Publisher report with the same name in the same folder where you saved the Data Model

14. The BI Publisher Report run immediately. Change the report output if you want. That’s it we are done creating and running BI Publisher report

15. Click on the Catalog, select the folder where you created the data model and report. Click Open to run the BI Publisher report again and edit to modify it further. J

Table reorder Drag Drop

Drag Drop in same table to reorder rows, Update Attribute value to make it persistent

This post is based on Frank’s article on reordering table rows using drag-drop functionality of ADF Faces, post uses iterator indexing to change the order of rows and it works pretty good

Recently I came across a question on OTN forum asking about making this ordering persistent on base of some attribute value so for that we have to maintain a serial no attribute for each row and when user performs Drag n Drop, serial no. will be swapped and updated ordering of rows will be shown to user and it is persistent as updated attribute’s value is saved in database

So for this use case, I have added a Serial No. column in Departments table of HR Schema

Let’s see how to implement this

• • Create a Fusion Web Application and prepare model using Departments table of HR Schema

• • Drop Departments view object as a table on the page, drop af:dragSource and af:collectionDropTarget as a child of af:table in order to enable Drag n Drop functionality

• • Define Drop Listener for drop target and set Model Name (this will be discriminant of drag source) and actions

Set properties of Drag Source

• • See Managed Bean code, Helper methods and Drop Listener Code

Required Packages

import java.util.Iterator;
import java.util.List;

import oracle.adf.model.BindingContext;
import oracle.adf.view.rich.component.rich.data.RichTable;
import oracle.adf.view.rich.datatransfer.DataFlavor;
import oracle.adf.view.rich.datatransfer.Transferable;
import oracle.adf.view.rich.dnd.DnDAction;
import oracle.adf.view.rich.event.DropEvent;

import oracle.binding.BindingContainer;
import oracle.binding.OperationBinding;

import oracle.jbo.Row;

import org.apache.myfaces.trinidad.model.RowKeySet;

Helper Methods

 /**Method to get BindingContainer of current viewPort
  * @return
  */
 public BindingContainer getBindingsCont() {
     return BindingContext.getCurrent().getCurrentBindingsEntry();
 }

/**
 * Generic Method to execute operation
 * */
public OperationBinding executeOperation(String operation) {
OperationBinding createParam = getBindingsCont().getOperationBinding(operation);
return createParam;
}

Drop Listener (Gets dragged and drop row site and swaps serial no)

    /**Method to handle drop event
     * @param dropEvent
     * @return
     */
    public DnDAction reOrderDropListener(DropEvent dropEvent) {
        //get the table instance. This information is later used

RichTable table = (RichTable) dropEvent.getDragComponent();
List dropRowKey = (List) dropEvent.getDropSite(); //if no dropsite then drop area was not a data area
if (dropRowKey == null) {
return DnDAction.NONE;
}
//The transferable is the payload that contains the dragged row's
//row key that we use to access the dragged row handle in the ADF
//iterator binding
Transferable t = dropEvent.getTransferable();
//here "reorderrow" is the discriminant used in drag source
DataFlavor<RowKeySet> df = DataFlavor.getDataFlavor(RowKeySet.class, "reorderrow");
RowKeySet rks = t.getData(df);
Iterator iter = rks.iterator();

//for this use case the re-order of rows is one-by-one, which means
//that the rowKeySet should only contain a single entry. If it
//contains more then still we only look at a singe (first) row key entry
List draggedRowKey = (List) iter.next();

//Dragged Row
Row dragRow = (Row) table.getRowData(draggedRowKey);

//Drop Row
Row dropRow = (Row) table.getRowData(dropRowKey);

//Get Serial No from dragged row
Integer dragSno = (Integer) dragRow.getAttribute("Sno");
//Get Serial No of dropped row
Integer dropSno = (Integer) dropRow.getAttribute("Sno");

//Swap Values to change order in DB
dragRow.setAttribute("Sno", dropSno);
dropRow.setAttribute("Sno", dragSno);

//Call Commit Operation to save chanes in database
executeOperation("Commit").execute();

//Call Execute operation to see updated records
executeOperation("Execute").execute();
return DnDAction.MOVE;
}

• Now run and check application. Dragged Purchasing department and dropped on Administration

After calling drop listener –