ANYDATA in Oracle Database

SYS.ANYDATE (Generic Data Type)  is an object type that  used to store different data types in Table, or create variable that can setted by any data-type.

For example I will create new table below

CREATE TABLE ANYDATA_TABLE
(
   ID           NUMBER NOT NULL,
   ADT_COLUMN   SYS.ANYDATA
);

Now I will try to insert different data types in col2

insert into anydata_table values(1,sys.anydata.convertnumber(1))
/
insert into anydata_table values(2,sys.anydata.convertdate(sysdate))
/
insert into anydata_table values(3,sys.anydata.convertvarchar2('Mahmoud Ahmed El-Sayed'))
/

Note that I use anydata.convertnumber, anydata.convertdate and anydata.convertvarchar2 to specify data type of inserted data

let's now query data from table

SELECT * FROM ANYDATA_TABLE;

The data of ADT_COULMN is unreadable in result set as below picture

So I need to retrieve readable value of  ADT_COLUMN from query
We can do this by below steps
1-Determine data type of inserted data in ADT_COLUMN
2-Create different functions to return equivalent data type for every inserted data in ADT_COLUMN
    I will create these functions in package called ANYDATA_PKG
3-Use ANYDATA built-in member functions to do equivalent of ANYDATA_PKG in #2

List Contents of Directory from Oracle SQL

One of my colleagues asked me how to list all content of directory from Oracle SQL query.
I think UTL_FILE built-in package allow this, but I discovered It doesn't support this.

So I decide to develop java class for listing contents of directory, then use it in Oracle Database.

I will list steps I will do briefly 
1-Create collection to store list of files in it.
2-Develop Java Class for List contents of Directory
3-Add Java Class to Database
4-Create PLSQL wrapper function to Java method
5-Grant Read on Directory to Java
6-Call PLSQL Function

Date Classes Conversion in ADF

In ADF we can use three data types classes of Date

oracle.jbo.domain.Date

java.util.Date

java.sql.Date

Note oracle.jbo.domain.Date class extends  java.sql.Date class 

Many ADF developers sometimes face exception when assigning or casting different Date classes to another, It will raise exception ClassCastException

So I will present a methods to convert between different Date classes
I add main method to test Date Conversion between different class.
I add an object for every class and assign it to current date of system.

Get Rows count in Whole Schema

Today I will produce function which returns count of rows in whole schema.

I can do this task using two solutions

Log DDL Changes in Your Schema

A lot of developers or DBA do a lot of  DDL operations on same schema
So we should create logging for this DDL operations in database table.

At first this post depends on my previous post Oracle System Events and Client Events

I should store all below data in logging

I will create table to store data of logging and I suggested below attributes 
1- DDL operation date
2- DB Username
3- Operating System Username
4- Machine that command executed from it.
5- Terminal that command executed from it.
6- DDL Operation Type (Create,Drop,Alter,...)
7- Database Object Type (Table, View, ......)
8- Database Object Name

Oracle Forms 11g Release 2 (11.1.2) New Features

I will list important  new features that are supported in Oracle Forms 11g release 2 (11.1.2)

#1 Integration with Oracle Access Manager
Oracle Access Manager is an identity management solution that provides centralized
authentication, policy based authorizations and auditing. With Oracle Forms 11g Release 2
you can use Oracle Access Manager for authentication and authorization of your Oracle Forms
application.

#2 Reduced Installation Footprint
In order to reduce the resource requirements on development machines, you can perform an
installation specifically tailored for development. This will limit the number of software products
and servers installed on the machine whilst still allowing a developer to build, run and test their
Forms application.

#3 Performance and Monitoring
Oracle Forms 11g Release 2 provides more dynamic capabilities to pre-start Forms runtime
processing. For example, an administrator can define a set number of runtime engines to be
started at 9am if it is known that most users log onto the system at that time. This will result in
improved start up times for users.

#4 Real User Experience Interaction
Oracle Real User Experience Interaction (RUEI) is a feature of Oracle Fusion Middleware that
provides non-intrusive monitoring, giving insight into how a user is interacting with an
application.

#5 Forms menu bar and window decoration to be turned of

which allows Forms to be more seamlessly integrated into other technology pages such as HTML pages.

Thanks
Mahmoud Ahmed El-Sayed

Oracle System Events and Client Events

Oracle Events are the events that are executed in oracle instance or user schema (DDL ,DCL Operations)

There are two types of events

A- Oracle System Events

   STARTUP, SHUTDOWN and SERVERERROR

B- Client Events

    LOGON, LOGOFF, DB_ROLE_CHANGE, SUSPEND, ALTER, DROP, CREATE, ANALYZE, AUDIT, NONAUDIT, DDL, GRANT, REVOKE and TRUNCATE.

In any of previous events I can develop database triggers on database or schema  for tracking, monitoring, logging or do any action.

For example( Trigger after startup database)

CREATE OR REPLACE TRIGGER XXX_TRG
   AFTER STARTUP
   ON DATABASE
BEGIN
   NULL;
--Write your code here
END; 

Add UI Components at Runtime In ADF

Sometimes we need to add UI components to page at run-time.
So I developed generic method that we can use it for adding components to page at run-time

Method Code 

    public void addComponent(UIComponent parentUIComponent, UIComponent childUIComponent){
        parentUIComponent.getChildren().add(childUIComponent);
        AdfFacesContext.getCurrentInstance().addPartialTarget(parentUIComponent);
    }

Call Method From Anywhere

    public void addUI(ActionEvent actionEvent) {
        // Add event code here...
        RichInputText ui = new RichInputText();
        ui.setId("rit1");
        ui.setLabel("Label of RichInputText");
        ui.setValue("Value of RichInputText");
        
        addComponent(getPgl1(), ui);
    } 

Note that getPgl1() return component of PanelGroupLayout component
I created ui object of RichInputText, You can create object of any UIComponents subclass like RichInputDate, RichSelectOneChoice, RichDecorativeBox, ...............

Mahmoud Ahmed El-Sayed

Java Decompiler in Jdeveloper

Java Decompiler  is a standalone graphical utility that displays Java source codes of “.class” files.
Below is snapshot of Java Decompiler program

 You can download this program from here

I will illustrate how to use this program as External Tool in Jdeveloper

Plsql Exception Management - Part 1

Achieving ideal error management
You should take care of the following points
1-Define your requirements clearly
2-Understand PL/SQL error management features and make full use of what PL/SQL has to offer
3-When will errors be raised, when handled?
     Do you let errors go unhandled to the host, trap locally, or trap at the top-most level?
4-How should errors be raised and handled?
    Will users do whatever they want or will there be standard approaches that everyone will follow?
5-Useful to conceptualize errors into three categories: Deliberate, unfortunate, unexpected

PL/SQL error management features
1- Defining exceptions
2- Raising exceptions
3- Handing exceptions

We will explain every topic individually

Set Initial Focus on item in OAF Pages

You can set initial focus in page on certain item.

Suppose that need to set focus on item ( XXX_EMP_NAME ), then you should add below code in processRequest() method in Controller class

 public void processRequest(OAPageContext oapagecontext, OAWebBean oawebbean)  
 {  
   super.processRequest(oapagecontext, oawebbean);  
   
   OABodyBean oabean = (OABodyBean)pageContext.getRootWebBean();  
   oabean.setInitialFocusId("XXX_EMP_NAME");  
 }   

Don't forget to add below import to Controller class

import oracle.apps.fnd.framework.webui.beans.OABodyBean;

Mahmoud Ahmed El-Sayed

Insert Rows in ADF View Object Programatically

I will illustrate how to insert new rows in view object programatically.

ٍSuppose that I have EmpVO view object which have below attributes
a-EmpId
b-FirstName
c-LastName

We can do inserting rows by two ways in data model layer (Application Model or ViewObject classes)
I will write my code in ApplicationModuleImpl class

1-createRow() method

2-createAndInitRow() method

 
I prefer using second method  createAndInitRow because it sets default values of attributes in view object but first method insertRow doesn't do this.

Mahmoud Ahmed El-Sayed