Slide 12
Slide 12 text
© 2023 NTT DATA Corporation 12
Considerations in AP development
• Add Java (JDBC) coding conventions and a process to be verified in unit testing
• Ensure SQL bind type is appropriate for DB column type (v$sql_bind_capture)
Development
phase
Viewpoints to be
checked
Counter-measure
Functional design
and coding
DB column type and SQL
bind type must be the same
In Java (JDBC) Coding Conventions
(1)Bind with the same type as the DB column type
e.g., do not bind to DATE type with TIMESTAMP type
(2) Bind with the same type even when null values
Unit test Confirm the above in actual
environment
Check the actual bind type on the DB in the actual
environment, and confirm that the results are in
accordance with the design
SQL> select sql_id, version_count, last_load_time, sql_text from v$sqlarea where sql_id=‘0hrt1s7pkz2qf ';
SQL_ID VERSION_COUNT★ LAST_LOAD_TIME SQL_TEXT
------------- ------------- -------------------- ----------------------------------------
0hrt1s7pkz2qf 1 20200419 21:46:29 UPDATE /* BINDTEST */ emp SET c2 = :1 ,
c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5
SQL> select sql_id, child_number,position,datatype_string,precision,scale,max_length from v$sql_bind_capture where sql_id= ‘0hrt1s7pkz2qf ';
SQL_ID CHILD_NUMBER POSITION DATATYPE_STRING PRECISION SCALE MAX_LENGTH
------------- ------------ ---------- -------------------- ---------- ---------- ----------
0hrt1s7pkz2qf 0 1 VARCHAR2(128) 128
0hrt1s7pkz2qf 0 2 VARCHAR2(128) 128
0hrt1s7pkz2qf 0 3 DATE★ 7
0hrt1s7pkz2qf 0 4 DATE★ 7
0hrt1s7pkz2qf 0 5 NUMBER 22
Confirm with
v$sql_bind_capture that a
column of type DATE is bound
with type DATE
Confirm with v$sqlarea that
VERSION_COUNT is 1