Tuesday, February 1, 2011

Custom Paging And Sorting Using Oracle Stored Procedure.



Here I will explain how to create Oracle stored procedure for custom paging,sorting and base on culture .Normally you should know how many record found in table base on where clause as well as required dynamic sorting and paging.All these things possible using dynamic creation of oracle stored procedure see this give below example.




CREATE OR REPLACE PROCEDURE GET_COUNTRY
(
//-------- Table Paramerters--------------------------------
V_COUNTRY_NO IN TRN_ORG_SYS.COUNTRY.COUNTRY_NO%TYPE DEFAULT NULL
,V_COUNTRY_NAME_AR IN TRN_ORG_SYS.COUNTRY.COUNTRY_NAME_AR%TYPE DEFAULT NULL
,V_COUNTRY_NAME_EN IN TRN_ORG_SYS.COUNTRY.COUNTRY_NAME_EN%TYPE DEFAULT NULL
,V_CREATED_BY IN TRN_ORG_SYS.COUNTRY.CREATED_BY%TYPE DEFAULT NULL
,V_CREATED_ON IN TRN_ORG_SYS.COUNTRY.CREATED_ON%TYPE DEFAULT NULL
,V_MODIFIED_BY IN TRN_ORG_SYS.COUNTRY.MODIFIED_BY%TYPE DEFAULT NULL
,V_MODIFIED_ON IN TRN_ORG_SYS.COUNTRY.MODIFIED_ON%TYPE DEFAULT NULL
,V_ISDELETED IN TRN_ORG_SYS.COUNTRY.ISDELETED%TYPE DEFAULT NULL

//-------- Parameters of Paging Sorting and culture------------
,P_CULTURE IN VARCHAR2 DEFAULT NULL
,P_SORT_ORDER IN VARCHAR2 DEFAULT NULL
,P_SORT_FIELD IN VARCHAR2 DEFAULT NULL
,P_PAGE_NO_NEEDED IN NUMBER DEFAULT NULL
,P_NUM_PER_PAGE IN NUMBER DEFAULT NULL
,P_OUT_TOTAL_RECORDS OUT NUMBER
,ITEMS_CURSOR OUT TRN_ORG_PROC.REF_CURSOR.T_CURSOR
)
IS
--Local variables >>>>>>>
SQL_SELECT CLOB;
SQL_SELONE VARCHAR2(4000);
SQL_SELTWO VARCHAR2(4000);
SQL_COUNTONE VARCHAR2(50) := 'SELECT COUNT(*) FROM ( ';
SQL_COUNTTWO VARCHAR2(50) := ') ';
SQL_ORDER_BY VARCHAR2(100);
FROM_ROWNUM NUMBER;
TO_ROWNUM NUMBER;
V_NUM_PER_PAGE NUMBER := P_NUM_PER_PAGE;
V_PAGE_NO_NEEDED NUMBER := P_PAGE_NO_NEEDED;
V_SORT_FIELD VARCHAR2(30);
V_SORT_ORDER VARCHAR2(30);
NEWLINE VARCHAR2(10) := CHR(13) || CHR(10);



--Local variables <<<<<<<
BEGIN
IF (V_NUM_PER_PAGE IS NULL OR V_NUM_PER_PAGE <= 0) THEN
V_NUM_PER_PAGE := 25;
END IF;
IF (V_PAGE_NO_NEEDED IS NULL OR V_PAGE_NO_NEEDED <= 0) THEN
V_PAGE_NO_NEEDED := 1;
END IF;
IF (P_SORT_FIELD IS NULL) THEN
V_SORT_FIELD := ' COUNTRY_NO';
ELSE
V_SORT_FIELD := P_SORT_FIELD;
END IF;
IF (P_SORT_ORDER IS NULL) THEN
V_SORT_ORDER := 'ASC';
ELSE
V_SORT_ORDER := P_SORT_ORDER;
END IF;
--
FROM_ROWNUM := ((V_PAGE_NO_NEEDED - 1) * V_NUM_PER_PAGE) + 1;
TO_ROWNUM := FROM_ROWNUM -1 + V_NUM_PER_PAGE;
SQL_SELONE := SQL_SELONE || 'SELECT ' || NEWLINE;
SQL_SELONE := SQL_SELONE || ' b.* ' || NEWLINE;
SQL_SELONE := SQL_SELONE || ' FROM ( ' || NEWLINE;
SQL_SELONE := SQL_SELONE || ' SELECT ' || NEWLINE;
SQL_SELONE := SQL_SELONE || ' a.*, ' || NEWLINE;
SQL_SELONE := SQL_SELONE || ' ROWNUM rnum ' || NEWLINE;
SQL_SELONE := SQL_SELONE || ' FROM ( ' || NEWLINE;
--
SQL_SELECT := SQL_SELECT || 'SELECT TRN_ORG_SYS.COUNTRY.COUNTRY_NO, 
DECODE('''|| P_CULTURE ||''''||',
''en-US'',TRN_ORG_SYS.COUNTRY.COUNTRY_NAME_EN ,
''ar-KW'',TRN_ORG_SYS.COUNTRY.COUNTRY_NAME_AR,
TRN_ORG_SYS.COUNTRY.COUNTRY_NAME_EN) COUNTRY_NAME,
TRN_ORG_SYS.COUNTRY.COUNTRY_NAME_AR, 
TRN_ORG_SYS.COUNTRY.COUNTRY_NAME_EN, 
TRN_ORG_SYS.COUNTRY.CREATED_BY, 
TRN_ORG_SYS.COUNTRY.CREATED_ON, 
TRN_ORG_SYS.COUNTRY.MODIFIED_BY, 
TRN_ORG_SYS.COUNTRY.MODIFIED_ON, 
TRN_ORG_SYS.COUNTRY.ISDELETED 
FROM TRN_ORG_SYS.COUNTRY' || NEWLINE;
--Construction of Where clause Starts here
SQL_SELECT := SQL_SELECT || ' WHERE 1 = 1 ' || NEWLINE;

IF(V_COUNTRY_NO IS NOT NULL AND V_COUNTRY_NO > 0 ) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.COUNTRY_NO = ' || V_COUNTRY_NO || '' || NEWLINE;
END IF; 
IF(V_COUNTRY_NAME_AR IS NOT NULL) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.COUNTRY_NAME_AR Like ''%' || V_COUNTRY_NAME_AR || '%''' || NEWLINE;
END IF; 
IF(V_COUNTRY_NAME_EN IS NOT NULL) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.COUNTRY_NAME_EN Like ''%' || V_COUNTRY_NAME_EN || '%''' || NEWLINE;
END IF; 
IF(V_CREATED_BY IS NOT NULL) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.CREATED_BY Like ''%' || V_CREATED_BY || '%''' || NEWLINE;
END IF; 
IF(V_CREATED_ON IS NOT NULL) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.CREATED_ON = ''' || V_CREATED_ON || '''' || NEWLINE;
END IF; 
IF(V_MODIFIED_BY IS NOT NULL) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.MODIFIED_BY Like ''%' || V_MODIFIED_BY || '%''' || NEWLINE;
END IF; 
IF(V_MODIFIED_ON IS NOT NULL) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.MODIFIED_ON = ''' || V_MODIFIED_ON || '''' || NEWLINE;
END IF; 
IF(V_ISDELETED IS NOT NULL AND V_ISDELETED > 0 ) THEN
SQL_SELECT := SQL_SELECT || ' AND COUNTRY.ISDELETED = ' || V_ISDELETED || '' || NEWLINE;
END IF; 
--Construction of Where clause Ends here

SQL_ORDER_BY := SQL_ORDER_BY || ' ORDER BY lower(' || V_SORT_FIELD || ') ' || V_SORT_ORDER || NEWLINE;
--
SQL_SELTWO := SQL_SELTWO || ' ) a ' || NEWLINE;
SQL_SELTWO := SQL_SELTWO || ' WHERE ' || NEWLINE;
SQL_SELTWO := SQL_SELTWO || ' ROWNUM <= :2) b ' || NEWLINE; -- TO_ROWNUM
SQL_SELTWO := SQL_SELTWO || ' WHERE ' || NEWLINE;
SQL_SELTWO := SQL_SELTWO || ' rnum >= :3 ' || NEWLINE; -- FROM_ROWNUM
--
EXECUTE IMMEDIATE SQL_COUNTONE || TO_CHAR(SQL_SELECT) || SQL_COUNTTWO INTO P_OUT_TOTAL_RECORDS;
--
OPEN ITEMS_CURSOR FOR SQL_SELONE || TO_CHAR(SQL_SELECT) || SQL_ORDER_BY || SQL_SELTWO USING TO_ROWNUM, FROM_ROWNUM;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GET_COUNTRY;


Base on above example you can easily create oracle stored procedure for custom paging, sorting,total record found and base on culture.

No comments:

Post a Comment

PDF Arabic watermark using MVC and iTextSharp

PDF full page Arabic watermark using MVC and iTextSharp Download :  Source Code Most of the time we have requirement to  gen...