I'm looking for a way to dynamically truncate a string to the max size of a varchar2 column.
See example below.
Table definition
CREATE TABLE some_table
(
log varchar2(50)
);
Code
DECLARE
v_str some_table.log%TYPE;
BEGIN
v_str := SUBSTR('a text longer than 50 chars ...', 1, 50 /*HOW TO GET 50 DYNAMICALLY HERE ?*/ );
END;
Any ideas?
select char_length
from user_tab_columns
where table_name = 'SOME_TABLE'
and column_name = 'LOG';
would return a value of 50 (the length of the log column in the some_table table)
result_cache relies_on(user_tab_columns) to cache the return value for each table/column (and dump the cache whenever user_tab_columns changes).relies_on clause in 12g that there were restrictions that prevented the result cache from working with data dictionary tables. I'm not seeing those restrictions in the more recent documentation so I'm guessing those restrictions are no longer there.
CHAR_LENGTH not DATA_LENGTH. And yes, storing the column length in a package variable is exactly what I have done when writing log entries and didn't want the log table column length as a magic number in my code. So this would be my recommendation, too.Getting (and caching) the column length is probably the way to go, but just for fun and riffing from an approach in this discussion:
DECLARE
v_str some_table.log%TYPE;
v_ret pls_integer;
v_len pls_integer;
BEGIN
v_ret := anydata.convertchar('-').getchar(v_str);
v_len := length(v_str);
dbms_output.put_line(v_len);
v_str := substr('a text longer than 50 chars, a text longer than 50 chars, a text longer than 50 chars...', 1, v_len);
dbms_output.put_line(v_str);
END;
/
dbms_output:
50
a text longer than 50 chars, a text longer than 50
Because it's using convertchar and getchar rather than the varchar2 versions, the result is the literal char - padded to its maximum length based on the variable's data type.
You can read more about anydata at Oracle-base or in the documentation.
You could still cache that, or work it out on exception. If you were going to do this often and performance was acceptable without caching/catching, you could possibly wrap it in a procedure:
CREATE PROCEDURE trunc_string(p_str out varchar2, p_val varchar2)
AS
v_ret pls_integer;
v_len pls_integer;
BEGIN
v_ret := anydata.convertchar('-').getchar(p_str);
v_len := length(p_str);
p_str := substr(p_val, 1, v_len);
END;
/
and then call that as:
DECLARE
v_str1 some_table.log%TYPE;
v_str2 varchar2(30);
BEGIN
trunc_string(v_str1, 'a text longer than 50 chars, a text longer than 50 chars, a text longer than 50 chars...');
dbms_output.put_line(v_str1);
trunc_string(v_str2, 'a text longer than 30 chars, a text longer than 30 chars, a text longer than 30 chars...');
dbms_output.put_line(v_str2);
END;
/
dbms_output:
a text longer than 50 chars, a text longer than 50
a text longer than 30 chars, a
But that's probably overkill... though that could be said about using anydata at all.
