In order to execute dynamic SQL in your stored procedure, you need to use the ‘EXECUTE IMMEDIATE’ statement. You can use SQL as shown below to execute it:
EXECUTE IMMEDIATE 'SELECT <COLUMN NAMEs> FROM <TABLE NAME> ' || :<ORDERING COLUMN> || ' DESC';
Another thing to notice over here is that if you are planning to use the table type in the final select statement with dynamic SQL, it will not work. Dynamic SQL does not support table type in select. What you can try as a work around is that you can dump the data in a temporary table before using it in the dynamic SQL and then use a temporary variable in the SQL.