1 Reply Latest reply on Apr 22, 2019 4:38 AM by Manu.

    upper letter Case issue

    G.Y

      I have this SQL Query in ORACLE DATABASE 10G R2

      SELECT COUNT(*) INTO UNAME 
      FROM INV.USERS
      WHERE UPPER(USER_NAME) = UPPER(:LOGIN_BLOCK.USER_NAME);
      

      when i run this query in oracle sql developer it works fine, but in FORMS6I if type upper letter it works, also if i insert user name  in lower case then its not work in forms..

       

      specially user_name cash2 have this issue because it is small case letter..

      please guide sir.

      thanks.

       

       

       

      --------------------------------------------------------

      --  DDL for Table USERS

      --------------------------------------------------------

        CREATE TABLE "INV"."USERS"

         ( "USER_ID" NUMBER(10,0),

      "EMP_CODE" NUMBER(10,0),

      "USER_NAME" VARCHAR2(30 BYTE),

      "PASSWORD" VARCHAR2(50 BYTE),

      "FULL_NAME" VARCHAR2(50 BYTE),

      "TYPE" CHAR(1 BYTE),

      "STATUS" VARCHAR2(10 BYTE),

      "APP_TYPE" VARCHAR2(20 BYTE),

      "DIVISION_ID" NUMBER(3,0)

         ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

        TABLESPACE "INV_DATA" ;

       

      REM INSERTING into INV.USERS

      SET DEFINE OFF;

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (13,null,'SAFEER','1','safeer','A','A',null,null);

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (14,null,'AMMAD','1','ammad ul hassan','A','A',null,null);

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (10,null,'RMS','1','RESTURMENT MANAGEMENT SYSTEM','A','A','RMS',1);

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (11,null,'123','1','321','A','A',null,1);

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (8,null,'VEHICLE','1','VEHICLE SERVIES','A','A','VS',1);

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (2,null,'ADMIN','ADMIN123','ADMINISTRATOR','A','A','INV',1);

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (9,null,'CASH','1','CASH','N','A','INV',1);

      Insert into INV.USERS (USER_ID,EMP_CODE,USER_NAME,PASSWORD,FULL_NAME,TYPE,STATUS,APP_TYPE,DIVISION_ID) values (12,null,'cash2','1','test user','A','A','INV',1);