Difference between CHAR and VARCHAR2 in PL/SQL function parameters

Question

Let's consider the four functions with these signatures:

FUNCTION my_func_1(pi_param CHAR) RETURN CHAR ...
FUNCTION my_func_2(pi_param CHAR) RETURN VARCHAR2 ...
FUNCTION my_func_3(pi_param VARCHAR2) RETURN VARCHAR2 ...
FUNCTION my_func_4(pi_param VARCHAR2) RETURN CHAR ...

Suppose all they have the same body of your choice.

Could you put an example involving these functions to show any difference in behaviour or advantage of using one of them?

I see almost everywhere in PL/SQL code that programmers prefer to declare string parameters as VARCHAR2, although there's no obvious reason for that mentioned in literature and absence of practical benefits (since CHAR at least has a shorter name).

Please, omit explaining of charater types basic difference in constants or variable declarations - and focus on case of function parameters and return types.

See https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/b_char.htm — user1, Oct 24 '18 at 09:38
@Yellow75, thank you. Actually, that what I pleaded to omit. Unfortunately, the article does not contain any mentions of function parameters. — diziaq, Oct 24 '18 at 09:56

Boneist · Answer 1 · 2018-10-24T13:55:22.123

You could have knocked up your own test case to demonstrate this, it's not hard. However, since you didn't, here's one that shows the difference:

DECLARE
  v_input_str VARCHAR2(4) := 'abcd';
  v_p_char CHAR(10);
  v_p_vchar VARCHAR2(10);
  v_ret_char CHAR(20);
  v_ret_vchar VARCHAR2(20);

  FUNCTION my_func_1(pi_param CHAR) RETURN CHAR
  IS
    v_var CHAR(10);
  BEGIN
    v_var := pi_param;
    dbms_output.put_line('  my_func_1: length(pi_param) = '||LENGTH(pi_param));
    dbms_output.put_line('  my_func_1: length(v_var) = '||LENGTH(v_var));
    RETURN v_var;
  END my_func_1;

  FUNCTION my_func_2(pi_param CHAR) RETURN VARCHAR2
  IS
    v_var VARCHAR2(10);
  BEGIN
    v_var := pi_param;
    dbms_output.put_line('  my_func_2: length(pi_param) = '||LENGTH(pi_param));
    dbms_output.put_line('  my_func_2: length(v_var) = '||LENGTH(v_var));
    RETURN v_var;
  END my_func_2;

  FUNCTION my_func_3(pi_param VARCHAR2) RETURN VARCHAR2
  IS
    v_var VARCHAR2(10);
  BEGIN
    v_var := pi_param;
    dbms_output.put_line('  my_func_3: length(pi_param) = '||LENGTH(pi_param));
    dbms_output.put_line('  my_func_3: length(v_var) = '||LENGTH(v_var));
    RETURN v_var;
  END my_func_3;

  FUNCTION my_func_4(pi_param VARCHAR2) RETURN CHAR
  IS
    v_var CHAR(10);
  BEGIN
    v_var := pi_param;
    dbms_output.put_line('  my_func_4: length(pi_param) = '||LENGTH(pi_param));
    dbms_output.put_line('  my_func_4: length(v_var) = '||LENGTH(v_var));
    RETURN v_var;
  END my_func_4;
BEGIN
  v_p_char := v_input_str;
  v_p_vchar := v_input_str;

  dbms_output.put_line('length(v_p_char) = '||length(v_p_char));
  dbms_output.put_line('length(v_p_vchar) = '||length(v_p_vchar));

  v_ret_char := my_func_1(v_p_char);
  dbms_output.put_line('my_func_1, v_p_char input, char output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_1(v_p_char);
  dbms_output.put_line('my_func_1, v_p_char input, char output: length(v_ret_vchar) = '||length(v_ret_vchar));

  v_ret_char := my_func_1(v_p_vchar);
  dbms_output.put_line('my_func_1, v_p_vchar input, char output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_1(v_p_vchar);
  dbms_output.put_line('my_func_1, v_p_vchar input, char output: length(v_ret_vchar) = '||length(v_ret_vchar));

  v_ret_char := my_func_2(v_p_char);
  dbms_output.put_line('my_func_2, v_p_char input, varchar output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_2(v_p_char);
  dbms_output.put_line('my_func_2, v_p_char input, varchar output: length(v_ret_vchar) = '||length(v_ret_vchar));

  v_ret_char := my_func_2(v_p_vchar);
  dbms_output.put_line('my_func_2, v_p_vchar input, varchar output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_2(v_p_vchar);
  dbms_output.put_line('my_func_2, v_p_vchar input, varchar output: length(v_ret_vchar) = '||length(v_ret_vchar));

  v_ret_char := my_func_3(v_p_char);
  dbms_output.put_line('my_func_3, v_p_char input, varchar output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_3(v_p_char);
  dbms_output.put_line('my_func_3, v_p_char input, varchar output: length(v_ret_vchar) = '||length(v_ret_vchar));

  v_ret_char := my_func_3(v_p_vchar);
  dbms_output.put_line('my_func_3, v_p_vchar input, varchar output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_3(v_p_vchar);
  dbms_output.put_line('my_func_3, v_p_vchar input, varchar output: length(v_ret_vchar) = '||length(v_ret_vchar));

  v_ret_char := my_func_4(v_p_char);
  dbms_output.put_line('my_func_4, v_p_char input, char output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_4(v_p_char);
  dbms_output.put_line('my_func_4, v_p_char input, char output: length(v_ret_vchar) = '||length(v_ret_vchar));

  v_ret_char := my_func_4(v_p_vchar);
  dbms_output.put_line('my_func_4, v_p_vchar input, char output: length(v_ret_char) = '||length(v_ret_char));
  v_ret_vchar := my_func_4(v_p_vchar);
  dbms_output.put_line('my_func_4, v_p_vchar input, char output: length(v_ret_vchar) = '||length(v_ret_vchar));

END;
/

and the output is:

length(v_p_char) = 10
length(v_p_vchar) = 4
  my_func_1: length(pi_param) = 10
  my_func_1: length(v_var) = 10
my_func_1, v_p_char input, char output: length(v_ret_char) = 20
  my_func_1: length(pi_param) = 10
  my_func_1: length(v_var) = 10
my_func_1, v_p_char input, char output: length(v_ret_vchar) = 10
  my_func_1: length(pi_param) = 4
  my_func_1: length(v_var) = 10
my_func_1, v_p_vchar input, char output: length(v_ret_char) = 20
  my_func_1: length(pi_param) = 4
  my_func_1: length(v_var) = 10
my_func_1, v_p_vchar input, char output: length(v_ret_vchar) = 10
  my_func_2: length(pi_param) = 10
  my_func_2: length(v_var) = 10
my_func_2, v_p_char input, varchar output: length(v_ret_char) = 20
  my_func_2: length(pi_param) = 10
  my_func_2: length(v_var) = 10
my_func_2, v_p_char input, varchar output: length(v_ret_vchar) = 10
  my_func_2: length(pi_param) = 4
  my_func_2: length(v_var) = 4
my_func_2, v_p_vchar input, varchar output: length(v_ret_char) = 20
  my_func_2: length(pi_param) = 4
  my_func_2: length(v_var) = 4
my_func_2, v_p_vchar input, varchar output: length(v_ret_vchar) = 4
  my_func_3: length(pi_param) = 10
  my_func_3: length(v_var) = 10
my_func_3, v_p_char input, varchar output: length(v_ret_char) = 20
  my_func_3: length(pi_param) = 10
  my_func_3: length(v_var) = 10
my_func_3, v_p_char input, varchar output: length(v_ret_vchar) = 10
  my_func_3: length(pi_param) = 4
  my_func_3: length(v_var) = 4
my_func_3, v_p_vchar input, varchar output: length(v_ret_char) = 20
  my_func_3: length(pi_param) = 4
  my_func_3: length(v_var) = 4
my_func_3, v_p_vchar input, varchar output: length(v_ret_vchar) = 4
  my_func_4: length(pi_param) = 10
  my_func_4: length(v_var) = 10
my_func_4, v_p_char input, char output: length(v_ret_char) = 20
  my_func_4: length(pi_param) = 10
  my_func_4: length(v_var) = 10
my_func_4, v_p_char input, char output: length(v_ret_vchar) = 10
  my_func_4: length(pi_param) = 4
  my_func_4: length(v_var) = 10
my_func_4, v_p_vchar input, char output: length(v_ret_char) = 20
  my_func_4: length(pi_param) = 4
  my_func_4: length(v_var) = 10
my_func_4, v_p_vchar input, char output: length(v_ret_vchar) = 10

The difference between CHAR and VARCHAR is that CHAR will space pad strings to the full width of the variable/column. That may be useful for reporting, but in general, what it means is that you're wasting memory storing all the extra spaces.

From the above output, you can see that when you store a four-character string in a CHAR(20) variable, the length of the variable is 20 - your original four characters, plus 16 spaces. The corresponding string in the VARCHAR2(20) variable has a length of 4.

The functions I have created take the input string (which was either CHAR(10) or VARCHAR2(10)) and puts it into a variable (either CHAR(10) or VARCHAR2(10)) before passing it out to the variables (either CHAR(20) or VARCHAR2(20)).

You can see that at the point the variable is returned from the functions, the length is either 4 or 10, but when the length of the variable holding the return value is either 4, 10 or 20.

You can see that VARCHAR2 variables do not change the length of the variable, but as soon as you add a CHAR variable into the mix, the length increases.

In short, only ever use CHAR if there's no other option. Otherwise, use VARCHAR2.

You say you only care about learning about the function parameter and return types, not variables, but they are one and the same thing!

Thank you, @Boneist for this huge amount of tests. I suppose we both know what is the difference of declaring variables as VARCHAR2 and CHAR, since I mentioned it in the question. In fact these test cases shows only behaviors of `v_ret_char` and `v_ret_vchar` variables themselves. What I meant in my question was could we show the diff bewtween `length(my_func_1('ABC'))` and `length(my_func_2('ABC'))` etc. Without assigning results to variables looks like all the same. — diziaq, Oct 24 '18 at 10:45
I think you're over-thinking things. It really doesn't matter what the output of the function returns, it's the variable it's returned into that determines what the resultant string looks like. The function's return value has a type, but it doesn't have a length associated with it - that's for the return variable to decide. — Boneist, Oct 24 '18 at 10:58
For example, if you pass a 4 character CHAR string into my_func_1, the return value of the function is going to be 4 characters too, since the input string determines the length of the output CHAR value. But you return that value into a CHAR(20) variable, and now the result is going to be 20 characters in length. That means, if you want to know the length of the variable returned by the function, you need to use a VARCHAR2 variable, as that doesn't artificially change the length of the value, unlike CHAR. — Boneist, Oct 24 '18 at 11:00
I have edited my answer with an example that hopefully makes things even clearer. Basically, the function parameters and return values are also variables, so they follow the same rules. — Boneist, Oct 24 '18 at 13:56

Difference between CHAR and VARCHAR2 in PL/SQL function parameters

1 Answers1