Showing posts from 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(); …

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

   LC$CURR_DAY    VARCHAR2 (10);
   LD$CURR_DATE   DATE := TO_DATE ('17-02-2012', 'dd-mm-yyyy'); --this is friday

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

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

               (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.ROWI…

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.