Is there a way to automatically declare a varchar2 constant with the needed size?

Question

Is there a way to declare a varchar2 constant with the needed size?

e.g. I would like to have the following definition without having to explicitly declare the size in parenthesis:

c_my_string constant varchar2(3) := 'abc';

Is there a way to do something like:

c_my_string constant varchar2(sizeof('abc')) := 'abc';

Other than that I could of course define the constant as a raw and then convert it to a varchar2 on the fly using UTL_RAW.CAST_TO_VARCHAR2(my_raw);

Not that's shorter/simpler than your first line ... why would you want to make the declaration more complex than it really needs to be ? — Ditto, Jun 30 '15 at 14:01
Only if the constant is related to a table column, and even then you can only set the maximum size, not the actual size. Why is hard-coding the length of a hard-coded string a problem? — Alex Poole, Jun 30 '15 at 14:01
It would be lazy, but you could use `c_my_string long := 'abc';`. Note that `long` in PL/SQL is simply a shorthand for `varchar2(32760)`. — Tony Andrews, Jun 30 '15 at 14:03
@AlexPoole Because of the Character encoding. If you want to make the code safe against changing the character encoding you have some problems. Even if you use CHAR semantics. If you are in an UTF8 db, varchar2(3 char) will use 12 bytes even if 3 would be enough for a string like 'abc'. — Roland, Jun 30 '15 at 14:08
[Even if you specify the size as 4000 or above](http://docs.oracle.com/cd/E24693_01/appdev.11203/e17126/datatypes.htm#CIHEDDFG)? — Alex Poole, Jun 30 '15 at 14:19
That reference seems to have been removed the latest 11g docs, and the 12c ones. [This might be useful though](http://stackoverflow.com/q/24831972/266304); it *seems* like this is no longer an issue from 11gR2, and you won't be 'wasting' the 9 bytes in your example. Results of testing that might be interesting though. — Alex Poole, Jun 30 '15 at 14:41
Do you have memory/performance test cases that shows this is an issue in your application? PL/SQL is a rather high level language and AFAIK the PL/SQL compiler/runtime handles all memory reservations automatically. — user272735, Jun 30 '15 at 15:10
@user272735 I doubt this is a performance issue. Still I wonder why PLSQL doesn't allow something simple as defining a varchar2 without having to explicitly declare the size. Maybe I should use a `clob` type instead of `varchar2`? — Roland, Jun 30 '15 at 15:37

score 0 · Answer 1 · answered Jun 30 '15 at 14:09

0

I suppose you could use a local function instead, and then refer to that as you would a variable:

declare
  function f_my_string return varchar2 is
  begin
    return 'abc';
  end;
begin
  dbms_output.put_line(f_my_string);
end;
/

Not sure why you'd want to make it that complicated though.

answered Jun 30 '15 at 14:09

Alex Poole

183,384
11
179
318

How about using clob instead of varchar2? – Roland Jun 30 '15 at 15:35
@Roland - isn't a CLOB going to be *more* memory-intensive? I would think the overhead would be higher, for a short string anyway, but that's another interesting thing to test I suppose. – Alex Poole Jun 30 '15 at 15:38
I think you are right. I didn't check it but I read one SO post making this point. – Roland Jun 30 '15 at 16:40
Given that the question really seems to be about memory usage and character encoding, and you don't seem to need to worry about it from 11gR2 (not that you really needed to anyway), this is even more pointless and needlessly complicated than I originally thought *8-) – Alex Poole Jul 01 '15 at 08:24
Consider that if your DB is utf8 then varchar2(3 char) will reserve 12 bytes even if you only store the constant 'abc' in it(3 bytes would suffice). – Roland Jul 01 '15 at 09:45
@Roland - yes, you stated that earlier, but from the testing in the linked question it seems that is not true in 11gR2 now? How are you determining how many bytes are actually being used? (And, does it really matter, other than as an intellectual exercise - you said it isn't a performance issue for you?) – Alex Poole Jul 01 '15 at 09:58
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/82081/discussion-between-roland-and-alex-poole). – Roland Jul 01 '15 at 10:43

Is there a way to automatically declare a varchar2 constant with the needed size?

1 Answers1