In oracle database, CHAR type max can be of size up to 255 bytes and VARCHAR2 data type can be of size up to 2000 bytes. However, in PL/SQL, maximum size of CHAR and VARCHAR2 data type are 32767. So , how conversion between these data types handled internally -
Consider the following scenario: 1. Read data from database in PL/SQL context 2. Write data from PL/SQL context to database
1. Read data from database in PL/SQL program (Database-to-variable conversion):-
- When we SELECT or FETCH data from a CHAR database column into a VARCHAR2 variable in PL/SQL program, the trailing spaces of CHAR type are retained.
- When we SELECT or FETCH from a VARCHAR2 database column into a CHAR variable, PL/SQL automatically pads the value with spaces out to the maximum length.
2. Write data from PL/SQL program to database column (Variable-to-database conversion): -
- When we INSERT or UPDATE a CHAR variable into a VARCHAR2 database column, the SQL kernel does not trim the trailing blanks before performing the change. Consider following:
After insert of this org_id in database, column will store 'VIOA ', with 6 spaces.
- when we INSERT or UPDATE a VARCHAR2 variable into a CHAR database column, the SQL kernel automatically pads the variable-length string with spaces out to the maximum (fixed) length specified when the table was created, and places that expanded value into the database.
Blank padding comparison is being used when comparison is carried out between two CHAR type. In this approach, PL/SQL appends blanks to smaller of two CHAR type under comparison up to the length of the longer value and then do the comparison.
Note: PL/SQL does not actually modify the variable's value.It make a copy if it and modify this temporary one and do comparison.
Non-blank padding comparison is carried when at least one of the operand under comparison is variable length string. In this approach, any variable is not modified, just each character is compared with other and result is returned.
Try this question, considering above conversion concept:What will be outcome of this PL/SQL anonymous program unit ?
when we execute it, it displays - Partner and parent partner are not same. Reason behind is , sales_partner_name is CHAR type and parent_sales_partner_name is of VARCHAR2 type. sales_partner_name is appended with 5 spaces and parent_sales_partner_name is just five character string, so when comparison happens PL/SQL uses Non-blank padding comparison and matches character by character , so it return FALSE and ELSE block is executed.
How to deal with such problem - To get around problems like this, we should always RTRIM CHAR values when they are involved in any kind of comparison. RTRIM removes trailing spaces.
Similarly, we can use RPAD - add trailing spaces, however it requires knowledge of length.
Now, un-comment the second IF statement in above program and comment first one, execute it again. - Output will be Partner and parent partner are same(IF block executed).
=========End of the post=======