Obviously it could be that the example posted is a huge simplification of the real use case, but as it is posted it looks like Mark is trying to do this inside a script, rather than in e.g. stored procedures.
If the interpretation that this is to be done in scripts is correct, then what you want can be done with substitution variables instead of bind variables:
Running this script:
prompt Create table ero_strings as my source for desired dynamic string lengths
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create table ero_strings
(name varchar2(20) not null
,string_length integer not null
);
prompt Insert string lengths into table
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
insert into ero_strings values ('small' ,25);
insert into ero_strings values ('medium' ,500);
insert into ero_strings values ('large-ish',4000);
insert into ero_strings values ('large' ,32767);
commit;
prompt List possible string lengths
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from ero_strings order by string_length;
prompt Retrieve string length for name='small' into a substitution variable
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- (NOTE: in SQLcl, noprint has NO EFFECT)
column string_length noprint new_value dynamic_length
select string_length
from ero_strings
where name = 'small'
;
prompt Use variable for length-constrained datatype
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select &dynamic_length dynamic_length, cast (rpad('10' ,10 ,'>') as varchar2(&dynamic_length)) cast_varchar from dual;
prompt The same but with a string that is too long
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select &dynamic_length dynamic_length, cast (rpad('100',100,'>') as varchar2(&dynamic_length)) cast_varchar from dual;
prompt Cleanup
prompt ~~~~~~~
drop table ero_strings;
undef dynamic_length
column string_length clear
Will result in:
ERO@EVROCS>@var_as_datatype_length.sql
Create table ero_strings as my source for desired dynamic string lengths
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Table created.
Insert string lengths into table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
List possible string lengths
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NAME STRING_LENGTH
-------------------- ---------------
small 25
medium 500
large-ish 4000
large 32767
4 rows selected.
Retrieve string length for name='small' into a substitution variable
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 row selected.
Use variable for length-constrained datatype
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DYNAMIC_LENGTH CAST_VARCHAR
--------------- -------------------------
25 10>>>>>>>>
1 row selected.
The same but with a string that is too long
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DYNAMIC_LENGTH CAST_VARCHAR
--------------- -------------------------
25 100>>>>>>>>>>>>>>>>>>>>>>
1 row selected.
Cleanup
~~~~~~~
Table dropped.
ERO@EVROCS>