Interconversion of CHAR and VARCHR in PL/SQL and Database - Read and Write with CHAR and VARCHAR2

PL/SQL programs - stored procedure/functions, uses fixed-length (CHAR) and variable-length (VARCHAR2) and does DML operation. However, the maximum size of these data types are not same in oracle database and in PL/SQL context.
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.

It is the type of the variable, not the column, determines the variable's resulting value.

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:
  
   DECLARE
     -- It will create a fixed length string of length 10 by appending 6 spaces .
     org_id   CHAR(10) := 'VIOA'; 
  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.

3. String comparison :- PL/SQL uses two techniques for string comparison : Blank-padding comparison and Non-blank padding comparison.
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 ?

set serveroutput on
DECLARE
   sales_partner_name CHAR (30) DEFAULT 'HOUGH';
   parent_sales_partner_name  VARCHAR2 (25) DEFAULT 'HOUGH';
BEGIN
   IF sales_partner_name = parent_sales_partner_name
   --IF RTRIM(sales_partner_name) = parent_sales_partner_name
   THEN
       DBMS_OUTPUT.PUT_LINE('Partner and parent partner are same');
 ELSE 
      DBMS_OUTPUT.PUT_LINE('Partner and parent partner are not same');
 END IF;
END;
/

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=======