26 February, 2012

Calling Stored Procedures or Functions from OAF Page

To invoke a stored procedure or function  from within an entity object or an application module, you need to follow below steps:
1. Create a CallableStatement with the PL/SQL block containing the stored procedure or function invocation
2. Bind any parameters ( IN or OUT )
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters or return value of function
5. Close the statement.

I will illustrate previous steps in below examples

1-Stored Procedure with OUT parameter

 public void testStoredProcedure() {  
   
     OADBTransaction txn = getDBTransaction();  
   
     CallableStatement callableStatement =  
   
       txn.createCallableStatement("begin xxx_procedure(:1, :2); end;",OADBTransaction.DEFAULT);  
   
     try {  
   
       callableStatement.registerOutParameter(2, Types.VARCHAR);  
   
       callableStatement.setString(1, "mahmoud");  
   
       String outParamValue = null;  
   
       callableStatement.execute();  
   
       outParamValue = callableStatement.getString(1);  
   
       callableStatement.close();  
   
     } catch (SQLException sqle) {  
   
       callableStatement.close();  
   
     }  
   }   

2- Stored Function

 public void testFunction() {  
     OADBTransaction txn = getDBTransaction();  
   
     CallableStatement callableStatement =  
   
       txn.createCallableStatement("begin :1 := xx_function(:2); end;",OADBTransaction.DEFAULT);  
   
     try {  
   
       callableStatement.registerOutParameter(1, Types.VARCHAR);  
   
       callableStatement.setString(1, "mahmoud");  
   
       String outParamValue = null;  
   
       callableStatement.execute();  
   
       outParamValue = callableStatement.getString(1);  
   
       callableStatement.close();  
   
     } catch (SQLException sqle) {  
   
       callableStatement.close();  
     }  
   }  
Mahmoud Ahmed El-Sayed

23 February, 2012

Joke with Oracle

We always use to_char(date,'day') to get day name of in_date in oracle PLSQL.

In my work I want to do below task.

If day of given day is friday then don't execute procedure otherwise execute.
So I wrote below code, You can check it and expect result

DECLARE
   LC$CURR_DAY    VARCHAR2 (10);
   LD$CURR_DATE   DATE := TO_DATE ('17-02-2012', 'dd-mm-yyyy'); --this is friday
BEGIN
   LC$CURR_DAY := TO_CHAR (LD$CURR_DATE, 'day');
   DBMS_OUTPUT.PUT_LINE (LC$CURR_DAY);

   IF LC$CURR_DAY= 'friday'
   THEN
      DBMS_OUTPUT.PUT_LINE ('It is weekend !!!, Go to Bed');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('You should do your best at work');
   END IF;
END;

20 February, 2012

Delete Duplicate Rows in Oracle Database


I have a table CMS_TRANS contains a lot of duplicate row.
I should delete duplicated rows from this table.

The structure of table as below

create or replace table CMS_TRANS(
trans_id number ,
trans_code varchar2(10),
trans_date date,
trans_desc varchar2(500) );

This table doesn't has primary key so redundant data is allowed, so I want to delete duplicate data which are repeated in trans_code,trans_date columns

I saw a lot of  solution of other people to do this, but I was surprising as there is easy solution that I will write it and they use complex solution to do this.

My solution is

DELETE FROM CMS_TRANS
      WHERE ROWID IN
               (SELECT ROWID
                  FROM CMS_TRANS T1
                 WHERE EXISTS
                          (SELECT 1
                             FROM CMS_TRANS T2
                            WHERE     T1.TRANS_CODE = T2.TRANS_CODE
                                  AND T1.TRANS_DATE = T2.TRANS_DATE
                                  AND T1.ROWID > T2.ROWID))



Mahmoud Ahmed El-Sayed

18 February, 2012

ADF Integration With LDAP

I received last week in my work new requirement to make signing in application single sign on(sso) through active directory.
So I have a lot of solution
1- Use ADF Security and integrate with active directory LDAP from weblogic server
     This solution is declarative but customization is hard

2- Use custom java code to integrate with LDAP directly.
     This solution is good but what if I change UI development by another tool? I will redevelop again in new   framework
3- Create database package to integrate with LDAP
     I prefer this solution as I use DB to integrate with LDAP and it is unit of later maintenance and applicable for other application.


How to Pass Parameters to ActionListener in ADF

In some cases, it is required to pass a value to ActionListener of ADF Button. The method that can be invoked by actionListeners has only...