CREATE TABLE N_AGREEMENT_STATUS   
(
STATUS_ID NUMBER(2) PRIMARY KEY,
STATUS_NAME VARCHAR2(10 BYTE)
);

INSERT ALL
INTO N_AGREEMENT_STATUS VALUES (1, 'INACTIVE')
INTO N_AGREEMENT_STATUS VALUES (2, 'ACTIVE')
INTO N_AGREEMENT_STATUS VALUES (3, 'CLOSED')
INTO N_AGREEMENT_STATUS VALUES (4, 'CANCELLED')
SELECT * FROM DUAL;

ALTER TABLE N_AGREEMENT ADD  STATUS_ID NUMBER(2);

UPDATE N_AGREEMENT SET STATUS_ID = 
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE  N_AGREEMENT.STATUS = STATUS_NAME );

UPDATE N_AGREEMENT SET STATUS_ID = 
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE  N_AGREEMENT.STATUS LIKE  STATUS_NAME || '%'
);

SELECT N_AGREEMENT.STATUS, N_AGREEMENT.STATUS_ID
FROM  N_AGREEMENT
WHERE  N_AGREEMENT.STATUS  =   'ACTIVE';

select dump(mycol) from mytab;

select mycol, dump(mycol) from mytab 
where translate(mycol, '0ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0') is not null;

select STATUS, dump(STATUS) from N_AGREEMENT
where translate(STATUS, '0ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0') is not null ;

ACTIVE	Typ=96 Len=10: 65,67,84,73,86,69,32,32,32,32
CLOSED	Typ=96 Len=10: 67,76,79,83,69,68,32,32,32,32
CLOSED	Typ=96 Len=10: 67,76,79,83,69,68,32,32,32,32
CLOSED	Typ=96 Len=10: 67,76,79,83,69,68,32,32,32,32
CLOSED	Typ=96 Len=10: 67,76,79,83,69,68,32,32,32,32
CLOSED	Typ=96 Len=10: 67,76,79,83,69,68,32,32,32,32
CLOSED	Typ=96 Len=10: 67,76,79,83,69,68,32,32,32,32

select * from agreement where status in
 ('ACTIVE', 'INACTIVE', 'CANCELLED', 'CLOSED');

SQL> select status_id, '"'||status_name||'"' from N_AGREEMENT_STATUS;
 STATUS_ID '"'||STATUS_
---------- ------------
         1 "INACTIVE"
         2 "ACTIVE"
         3 "CLOSED"
         4 "CANCELLED"
        12 "ACTIVE  "
        13 "CLOSED  "

6 rows selected.

SQL> select status_id, '"'||status_name||'"' from N_AGREEMENT_STATUS 
  2  where status_name in ('ACTIVE', 'INACTIVE', 'CANCELLED', 'CLOSED');
 STATUS_ID '"'||STATUS_
---------- ------------
         1 "INACTIVE"
         2 "ACTIVE"
         3 "CLOSED"
         4 "CANCELLED"

4 rows selected.

SQL> col parameter format a20
SQL> col value format a20
SQL> select * from v$nls_parameters where parameter in ('NLS_COMP','NLS_SORT');
PARAMETER            VALUE
-------------------- --------------------
NLS_SORT             BINARY
NLS_COMP             BINARY

SQL> col parameter format a20
SQL> col value format a20
SQL> select * from v$nls_parameters where parameter in ('NLS_COMP','NLS_SORT');

PARAMETER            VALUE
-------------------- --------------------
NLS_SORT             BINARY
NLS_COMP             BINARY

select * from n_agreement where status in
 ('ACTIVE', 'INACTIVE', 'CANCELLED', 'CLOSED');

select STATUS, dump(STATUS) from N_AGREEMENT
where translate(STATUS, '0ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0') is not null ;

-- added space to 2nd parameter
select STATUS, dump(STATUS) from N_AGREEMENT
where 
translate(STATUS, '0ABCDEFGHIJKLMNOPQRSTUVWXYZ ', '0') 
is not null ;

SQL> select count(*) from n_agreement where status in
  2   ('ACTIVE', 'INACTIVE', 'CANCELLED', 'CLOSED');

  COUNT(*)
----------
      1503

SQL> select count(*) from n_agreement;

  COUNT(*)
----------
      1503

SQL> desc n_agreement;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUMBER0                                   NOT NULL NUMBER(4)
 SUB_NUMBER                                NOT NULL CHAR(7)
 TYPE                                      NOT NULL CHAR(10)
 SUPPLEMENT_TYPE                                    CHAR(32)
 EXPIRY_MONTH_COUNT                                 NUMBER(2)
 INTEREST_ANNUAL_RA                                 NUMBER(6,4)
 MATURITY                                  NOT NULL NUMBER(3)
 GRACE_PERIOD                              NOT NULL NUMBER(2)
 ACQUISITION_DAY_CO                                 NUMBER(3)
 STATUS                                    NOT NULL CHAR(10)
 FK_PROJECTNUMBER0                                  NUMBER(4)
 FK_COUNTRYCODE                                     NUMBER(3)
 FK_AGREEMENTNUMBER                                 NUMBER(4)
 FK_AGREEMENTSUB_NU                                 CHAR(7)
 FK_AGREEMENTTYPE                                   CHAR(10)
 FK_DRAFT_AGREEMSER                                 NUMBER(5)
 FK_AGREEMENTNUMBE0                                 NUMBER(4)
 FK_AGREEMENTSUB_N0                                 CHAR(7)
 FK0AGREEMENTTYPE                                   CHAR(10)
 FK_TOPICCODE                                       NUMBER(3)
 TECH_STATUS                                        NUMBER(1)
 DHS_CATEG                                          NUMBER(3)

SQL> create table t1 (v1 char(10));

Table created.

SQL> select * from t1 where v1='X';
V1
----------
X

1 row selected.

SQL> select * from t1 where v1='X                    ';
V1
----------
X

1 row selected.

SQL> create table t2 (v2 char(15));

Table created.

SQL> insert into t2 values ('X');

1 row created.

SQL> select * from t1, t2 where v1=v2;
V1         V2
---------- ---------------
X          X

1 row selected.

create table t3 (v3 varchar(5));

insert into t3 values ('X');

select * from t2, t3 where v2= v3;

select * from t2, t3 where v2= rpad(v3,15, ' ' );