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.

Advertisements
This entry was posted in Asp.Net, C#, Database and tagged . Bookmark the permalink.

One Response to Custom Paging And Sorting Using Oracle Stored Procedure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s