Oracle 12.1.0.2

[Juktus]

Hi

I have problem with describe select on Oracle 12.1.0.2 EE and DOA Components.

Example.
I create table
CREATE TABLE SOME_TABLE(
SOME_COLUMN VARCHAR2(3));
I Create DataSet with selects:

1.
SELECT some_column
FROM some_table
WHERE some_column = :variable
GROUP BY some_column

Describe result
some_columne size=4000

2.
For select
SELECT some_column
FROM some_table
WHERE some_column = '12345'
GROUP BY some_column

Describe result
some_columne size=5

3.
For select
SELECT some_column
FROM some_table
WHERE some_column = '12'
GROUP BY some_column

Describe result
some_columne size=2

ETC.

There is problem with describe columne when it it used in where and group by clause. This problem appears only in version 12.1.0.2 EE and probably next versions of Oracle 12c DB versions.

I contacted with Oracle support and wrote them about that problem. They answered that this is effect of optimalization which they did on that version and it is expected behavior.

It is true that all functions which retrive size of column: OCIAttrGet used by DOA, dbms_sql.describe_columns and JAVA getColumnDisplaySize return bad results.

Be warned that switching to a database 12.1.0.2, we are not able to automatically determine the size of the field when we use this type of query or view based on such query. Describing queries in DOA will stop functioning properly.
The effect of such behavior will be able to enter more characters into the field than its actual size, effect ORA-12899.
A further consequence of DOA can not properly reserve memory for data record, because the field sizes are incorrect.

My question to the developers of DOA, do you have an idea how to solve this problem. I personally think it is a error ORACLE not DOA.

Best regards
Grzegorz Chromik

ps.
Examples:
PLSQL

create table t1(id varchar2(3));

set serveroutput on

declare
c integer;
col_cnt integer;
desc_t dbms_sql.desc_tab;
qry varchar2(100);
begin
c := dbms_sql.open_cursor();
qry := 'select id from t1 where id = ''1234'' group by id';
dbms_sql.parse(c, qry, dbms_sql.NATIVE);
dbms_sql.describe_columns(c, col_cnt, desc_t);
dbms_output.put_line('Query: ' || qry || chr(10) ||' colCnt='||col_cnt|| '' ||desc_t(1).col_name|| '(col_max_len=' || desc_t(1).col_max_len ||')');
dbms_sql.close_cursor(c);
qry := 'select id from t1 where id = ''123456'' group by id';
c := dbms_sql.open_cursor();
dbms_sql.parse(c, 'select id from t1 where id = ''123456'' group by id',dbms_sql.NATIVE);
dbms_sql.describe_columns(c, col_cnt, desc_t);
dbms_output.put_line('Query: ' || qry || chr(10) ||' colCnt='||col_cnt|| ' ' ||desc_t(1).col_name|| '(col_max_len=' || desc_t(1).col_max_len||')');
dbms_sql.close_cursor(c);
end;
/

JAVA:
package com.bpsc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class DescribeQuery {

public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbcracle:thin::";
String username = "";
String password = "";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}

public static void getColumnNames(ResultSet rs, String query) throws SQLException {
if (rs == null) {
return;
}
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
System.out.println("Query: " + query);
System.out.println(" column name=" + columnName + " size=" + rsMetaData.getColumnDisplaySize(i));
}
}

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
String query;
// create table t1(id varchar2(3));
//
// works ok
query = "select id from t1 where id = '1234'";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
getColumnNames(rs, query);
//
// doesn't work - look at the column size!
query = "select id from t1 where id = '1234' group by id";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
getColumnNames(rs, query);
//
// doesn't work - look at the column size!
query = "select id from t1 where id = '1234567' group by id";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
getColumnNames(rs, query);
//Results for Oracle 12.1.0.2:
//Query: select id from t1 where id = '1234'
// column name=ID size=3
//Query: select id from t1 where id = '1234' group by id
// column name=ID size=4
//Query: select id from t1 where id = '1234567' group by id
// column name=ID size=7
//
//
// the same effect exists in non-java applications!!!
//
} catch (Exception e) {
e.printStackTrace();
} finally {
// release database resources
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}