I have a Oracle procedure inside a package like this
PROCEDURE getEmployee
(
pinLanguage IN VARCHAR2,
pinPage IN NUMBER,
pinPageSize IN NUMBER,
pinSortColumn IN VARCHAR2,
pinSortOrder IN VARCHAR2,
poutEmployeeCursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN poutEmployeeCursor FOR
SELECT * FROM (
SELECT EMPLOYEE_ID, USERNAME, FULL_NAME, DATE_OF_BIRTH, EMP.GENDER_ID, GEN_TR.GENDER, EMP.WORK_TYPE_ID, WT_TR.WORK_TYPE, SALARY, EMAIL, PROFILE_IMAGE,
ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID ASC) RN
FROM EMPLOYEES EMP
INNER JOIN GENDERS GEN ON EMP.GENDER_ID = GEN.GENDER_ID
LEFT JOIN GENDERS_MLD GEN_TR ON GEN.GENDER_ID = GEN_TR.GENDER_ID AND GEN_TR.LANGUAGE = pinLanguage
INNER JOIN WORK_TYPES WT ON EMP.WORK_TYPE_ID = WT.WORK_TYPE_ID
LEFT JOIN WORK_TYPES_MLD WT_TR ON WT.WORK_TYPE_ID = WT_TR.WORK_TYPE_ID AND WT_TR.LANGUAGE = pinLanguage
)
WHERE RN BETWEEN (((pinPage - 1) * pinPageSize) + 1) AND (pinPage * pinPageSize);
END;
I need to make the sort order of the above query dynamic
If I pass the text FullName to pinSortColumn parameter, it need to sort FULL_NAME column
If I pass the text DateOfBirth to pinSortColumn parameter, it need to sort DATE_OF_BIRTH column
If I pass the text Gender to pinSortColumn parameter, it need to sort GEN_TR.GENDER column
I can pass the text asc or desc to pinSortOrder parameter and the query need to be sorted accordingly. Can you please help me to achieve this?