Posts

Showing posts from March, 2012

ANYDATA in Oracle Database

Image
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 differe…

List Contents of Directory from Oracle SQL

Image
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

Image
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 …

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, ...............

M…

Java Decompiler in Jdeveloper

Image
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 classimport oracle.apps.fnd.framework.webui.beans.OABodyBean; Mahmoud Ahmed El-Sayed

Insert Rows in ADF View Object Programatically

Image
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