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

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;

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

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.