Hi,

We have an issue in production where one of the developer has released a Java code without checking the data type of the column of the table. He is equating a string literal to a column which stores as number. So naturally it is giving an invalid number error. This is an easier fix but it is easier said than done. Since there are lot of places in the application, and also depending upon predicate clause of the SQL, the database may or may not return this error. ( And the developer is blaming Oracle for that saying that why can't Oracle check the column data type before equating it to the literal value).

Here's the tricky part of the question : Is there a way to change the predicate clause of the SQL by any means? I tried the FGAC to do this but unfortunately FGAC is not able to handle the literal values passed in the predicate clause equating to the column value.

Here are my scripts and some FGAC solution I tried :

CREATE TABLE TEST_CONFIG
(
  EQ_ORG_ID                   NUMBER(10)        NOT NULL,
  EQ_LEGAL_ENTITY_ID          NUMBER(10),
  EQ_FUNCTION_CD              VARCHAR2(16 BYTE),
  TRANSFORMATION_SET_NAME     VARCHAR2(32 BYTE),
  PASS_THROUGH                CHAR(1 BYTE)      NOT NULL,
  XSLT_FILE_NAME              VARCHAR2(64 BYTE),
  COMMUNICATION_TYPE_CD       CHAR(2 BYTE),
  SOURCE_CD                   CHAR(1 BYTE)      NOT NULL,
  USER_ID                     VARCHAR2(10 BYTE),
  PASSWORD                    VARCHAR2(10 BYTE),
  FUNCTION_VALUE              NUMBER(10),
  PREPROCESSING_INSTRUCTIONS  VARCHAR2(1000 BYTE),
  PREPROCESSING_CLASS         VARCHAR2(1000 BYTE)
) ;


SET DEFINE OFF;
Insert into TEST_CONFIG
   (EQ_ORG_ID, EQ_LEGAL_ENTITY_ID, EQ_FUNCTION_CD, TRANSFORMATION_SET_NAME, PASS_THROUGH, 
    XSLT_FILE_NAME, COMMUNICATION_TYPE_CD, SOURCE_CD, FUNCTION_VALUE)
 Values
   (232, 776, 'AVPO-PO', '232DB_LE776_ECOM_AVPO', 'N', 
    'AvailabilityCToE.xsl', 'MQ', 'C', 500);
Insert into TEST_CONFIG
   (EQ_ORG_ID, EQ_LEGAL_ENTITY_ID, EQ_FUNCTION_CD, TRANSFORMATION_SET_NAME, PASS_THROUGH, 
    XSLT_FILE_NAME, COMMUNICATION_TYPE_CD, SOURCE_CD)
 Values
   (232, 776, 'AVPO-PO', 'ABX_AVPO', 'N', 
    'AvailabilityCToE.xsl', 'MQ', 'C');
Insert into TEST_CONFIG
   (EQ_ORG_ID, EQ_LEGAL_ENTITY_ID, EQ_FUNCTION_CD, TRANSFORMATION_SET_NAME, PASS_THROUGH, 
    XSLT_FILE_NAME, COMMUNICATION_TYPE_CD, SOURCE_CD)
 Values
   (18, 1818, 'AVPO-IN', 'IN_AVPO', 'N', 
    'AB_INMSG.xsl', 'MQ', 'M');

COMMIT;

If I use the following SQL statement, it produces an error of INVALID NUMBER" as follows :

select * from test_config
where eq_org_id = 232 and
      (eq_legal_entity_id = 776 OR eq_legal_entity_id IS NULL)
      and eq_function_cd = 'AVPO-PO'
      and ( function_value = 'L' OR function_value IS NULL)
      ;

and ( function_value = 'L' OR function_value IS NULL)
*
ERROR at line 5:
ORA-01722: invalid number



If I use the same SQL statement for which the values are null in the number column, then SQL doesn't error out as follows :

select * from test_config
where eq_org_id = 18 and
      (eq_legal_entity_id = 1818 OR eq_legal_entity_id IS NULL)
      and eq_function_cd = 'AVPO-IN'
      and ( function_value = '5000' OR function_value IS NULL)
      and source_cd = 'M'
      ;

EQ_ORG_ID EQ_LEGAL_ENTITY_ID EQ_FUNCTION_CD TRANSFORMATION_SET_NAME P XSLT_FILE_NAME CO S USER_ID PASSWORD
---------- ------------------ ---------------- -------------------------------- - ---------------------------------------------------------------- -- - ---------- ----------
FUNCTION_VALUE
--------------
PREPROCESSING_INSTRUCTIONS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PREPROCESSING_CLASS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
18 1818 AVPO-IN IN_AVPO N AB_INMSG.xsl MQ M



1 row selected.


To handle this exception I'm trying to see if FGAC can be used to not return errors to the user. I did the following :

create table fgac_trk ( 
  sdt   timestamp  ,
  sql_stmt  varchar2(3050) ,
  chk_flg  number(10) ) compress ;

begin 
 dbms_rls.add_policy  ( 
  object_schema => 'EQDEVDBA', 
  object_name => 'TEST_CONFIG', 
  policy_name => 'CI_ORGCONF_FUNC_VAL_CONV_POL', 
  policy_function => 'FUNC_CI_ORG_CONF_CONV_NUM2VAL', 
  function_schema => 'EQDEVDBA', 
  statement_types => 'SELECT', 
  update_check => true, 
  enable => true 
   ); 
end;
/

begin 
    dbms_rls.enable_policy  ( 
  object_schema => 'EQDEVDBA', 
  object_name => 'TEST_CONFIG', 
  policy_name => 'CI_ORGCONF_FUNC_VAL_CONV_POL', 
  ENABLE => TRUE ) ;
END ;
/


create or replace function FUNC_CI_ORG_CONF_CONV_NUM2VAL ( p_schema_name in varchar2, p_table_name in varchar2 ) 
return varchar2 
is 
 whoami   varchar2(30) ;
 S_stmt   varchar2(3000) ;
 lp_slot_fmt     varchar2(50) ; 
 chkd_flg  pls_integer ;
 err_msg  varchar2(150) ;
begin 

 select sys_context('userenv', 'session_user')
 INTO whoami
 FROM dual;

Begin 
 
 IF whoami IN ('EQDEV_APP') THEN
 
            chkd_flg:=0 ;
 
  select sys_context('userenv', 'current_sql',3000) into S_stmt
  from dual ;


  IF instr(S_stmt, 'function_value = ''L''')> 0 and          
                   instr(S_stmt, 'eq_function_cd = ''AVPO-PO''')>0 and 
                    instr(S_stmt, 'source_cd = ''C''')>0 then
   lp_slot_fmt := ' to_char(function_value) = ''L''' ;
   chkd_flg:=1 ;
  end if ;
  
  insert /*+ append */ 
  into fgac_trk values (systimestamp, S_stmt||lp_slot_fmt, chkd_flg) ;
  
  commit ;
  
 end if ;

 
EXCEPTION
 WHEN OTHERS THEN
  ERR_MSG := substr(sqlerrm,1,150) ;
   insert /*+ append */ 
   into fgac_trk values (systimestamp, err_msg, -1) ;

end ;
   
 return lp_slot_fmt ; 

end; 
/

Once the policy is created and enabled along with the function, I tried to execute the query. The policy does kick in but the If statement is not evaluated if I use the "instr(S_stmt, 'function_value = ''L''')> 0 and ....." as mentioned above. Now if switch the if-end if statment to the following the policy does kicks in.

IF instr(S_stmt, 'function_value = :"SYS_B_3"')> 0 and 
   instr(S_stmt, 'eq_function_cd = :"SYS_B_2"')>0 and 
   instr(S_stmt, 'source_cd = :"SYS_B_4"')>0 then
 lp_slot_fmt := 'to_char(function_value) = ''L''' ;
 chkd_flg:=1 ;
end if ;

The problem here is that all SQL statements with the above predicate clause gets evaluated even if it should work for only "eq_function_cd = 'AVPO-PO'". How do I tell the function that the value passed in :"SYS_B_2" is "AVPO-IN" and that it should not apply the policy to add the predicate clause. The following Query should work because my eq_function_cd <> 'AVPO-PO'.

select * from test_config
where eq_org_id = 18 and
      (eq_legal_entity_id = 1818 OR eq_legal_entity_id IS NULL)
      and eq_function_cd = 'AVPO-IN'
      and ( function_value = '5000' OR function_value IS NULL)
      ;

Is there a way to make this approach work by using FGAC ? Any thoughts?


Just a side note :
One more thing I've observed is that the predicate clause gets appended to the query even if there is an Order by at the end. Even if this is only an observation, I'm sure Oracle must be evaluating the query correctly by encapsulating the whole query and then applying the predicate over that. Is that correct ? See below :

select * from test_config
where eq_org_id = :"SYS_B_0" and
(eq_legal_entity_id = :"SYS_B_1" OR eq_legal_entity_id IS NULL)
and eq_function_cd = :"SYS_B_2"
and ( function_value = :"SYS_B_3" OR function_value IS NULL)
and function_value IS NULL
and source_cd = :"SYS_B_4"
order by eq_legal_entity_id, function_value to_char(function_value) = 'L'

You can see from above that to_char(function_value) = 'L' is appended to the Query. This got inserted into fgac_trk table.


Thanks
Pramod.