[Oracle][PL/SQL][Example] Oracle stored procedure with dynamic-SQL and cursor

Create Statement : 

 CREATE OR REPLACE PROCEDURE "SP_GET_LIB_BOOK"

  in_column_name IN VARCHAR2,

  in_column_value IN VARCHAR2,

  in_sort_column IN VARCHAR2,

  in_sort_order IN VARCHAR2,

  out_int_count OUT DECIMAL,

  out_cursor OUT TYPES.CURSOR_TYPE,

  out_err_msg OUT VARCHAR2)

AS

  dynSQL VARCHAR2(4000)

BEGIN

  out_err_msg := '';

  dynSQL := 'SELECT * FROM LIB_ASSET';

  IF LENGTH(in_sort_column) > 0 THEN

    dySQL := dySQL || 'ORDER BY' || in_sort_column || ' ';

  ELSE

    dySQL := dySQL || 'ORDER BY ASSET_ID ';

  END IF

  IF LENGTH(in_sort_order) > 0 THEN

    dySQL := dySQL || in_sort_order;

  ELSE

    dySQL := dySQL || 'ASC ';

  END IF

 

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || dynSQL || ') INTO out_int_count';

  OPEN out_cursor FOR dynSQL;

EXCEPTION

  WHEN OTHERS THEN

    out_cursors := SQLERRM || '(Code:' || SQLCODE || ')';

END