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.
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.
