With PL/SQL and LONGs (and PRODUCT_USER_PROFILE)

One use for the 12.1.0.2 addition of PL/SQL functions in the WITH clause is to get the HIGH_VALUE of a partition in a usable column format.

with
 FUNCTION char2000(i_tab in varchar2, i_part in varchar2) 
 RETURN VARCHAR2 IS
   v_char varchar2(2000);
 BEGIN
   select high_value into v_char
   from user_tab_partitions a
   where a.table_name = i_tab
   and a.partition_name = i_part;
   --
   if v_char like 
     'TO_DATE(''%'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')'
   then
      v_char := regexp_substr(v_char,q'{'[^']+'}');
   end if;
   --
   RETURN v_char;
 END;
select table_name, partition_name, 
       char2000(table_name, partition_name) high_val,
       partition_position, tablespace_name, 
       segment_created, num_rows, last_analyzed, 
       global_stats, user_stats
from user_tab_partitions ut
where segment_created='YES'
order by table_name, high_val;
/

Oracle have spent well over a decade telling us that LONG is deprecated, but still persist in using it in their data dictionary. PL/SQL is the only practical way of getting the values into a more usable data type.

You will want the last version of the SQL Plus client. For SQL, sqlplus treats the semi-colon as a "go off and execute this". PL/SQL has traditionally needed a period on an otherwise empty line to switch from the statement editor to the command prompt.

For example:

Having PL/SQL embedded in the SQL statement confuses the older clients, and we get a bout of premature execution.

In the 12.1.0.2 client, a WITH statement is treated as a PL/SQL statement if it contains PL/SQL (ie needing the period statement terminator). If it doesn't contain PL/SQL then it doesn't (so there's no change required for older scripts). That said, I'd recommend consistently using the period terminator for PL/SQL and SQL.  

The SQLcl client (still beta/early adopter) currently manages the straight select okay, but fails if it is part of a CREATE VIEW. 

Tim Hall has already noted that the WITH PL/SQL doesn't currently work when embedded in a PL/SQL block (such as a procedure), but that is expected in a future release. 

Oh, and while it isn't documented in manual, WITH is its own statement for the purposes of PRODUCT_USER_PROFILE. I can't imagine anyone on the planet is still using PRODUCT_USER_PROFILE for security. If they are, they need to rethink in light of WITH statements and result sets being returned by PL/SQL.