]
PROCEDURE display_emp (Resultset IN OUT EMP_CURSOR, MYSORTORDER VARCHAR2)
IS

l_sortorder VARCHAR2(20);
l_sql_stmt VARCHAR2(2000);

BEGIN

-- check innput parameters
--IF ( MYSORTORDER IS NULL ) THEN
--l_sortorder := 'EMPNO';
--ELSE
--l_sortorder := MYSORTORDER;
--END IF;

-- static variante without building sql_statement ---
OPEN Resultset
FOR
SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
FROM EMP
ORDER BY  MYSORTORDER;

--- alternative: Resultset with dynamic query ----
-- create dynamic query
--l_sql_stmt := 
--'SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL --SAL
--FROM EMP
--ORDER BY  '||l_sortorder;

--OPEN Resultset
--FOR  l_sql_stmt;

EXCEPTION
WHEN VALUE_ERROR THEN
NULL;
WHEN OTHERS THEN
NULL;

END display_emp;
END EMP_TOOLS;
[