Oracle 12.2 and AL32UTF8 character set

[djennings]

Hello,
Needing a bit of help with an Oracle 12.2 install.

We have been using version 4.1.3.5 against Oracle 12.1 databases without any kind of issue.

We recently tried out the components against a 12.2 Oracle database, and are now getting Access Violations.

First, we found that Oracle 12.2 defaults to character set AL32UTF8 when installed. Our Oracle 12.1 databases use WE8MSWIN1252.

The issue we have is when using an Oracle function in a select statement that also uses a variable.
example: select test_function.get_textvar1) as test_text from dual;

Oracle is returning a data size of 32767 instead of 4000 bytes. This is happening inside the InternalDescribe procedure at this line:
OCIAttrGet(paramhp, OCI_DTYPE_PARAM, u2, nil, OCI_ATTR_DATA_SIZE, secerrhp);
dbsize := u2;

 

[Marco Kalter]

The "select test_function.get_textvar1) as test_text from dual" statement will indeed return a 32KB string on Oracle12, which is more than the current limit of 4000 bytes in Direct Oracle Access. For compatibility reasons it is better to use a PL/SQL Block instead:

begin
  :var2 := test_function.get_text(:var1);
end;

If you declare :var2 as a otPLSQLString you can fetch all 32KB of data.

 

[djennings]

Thank you for the response Marco.

However, the queries and datasets we are dealing with are in Reports (QuickReports). Using PL/SQL blocks will not be an option.

We use function calls in our SQL for numerous things. Some for large calculations, others for building up dynamic strings.

Here is an example of a report with the Access Violation. We are using function calls to do Text Translations based on a Hex Language Code (ie 0409)

sql.Add('select d.*');
sql.Add('from (');
sql.Add(' select d.*,');
sql.Add(' t.name_1 as terminal_name,');
sql.Add(' language_manager.lookup(d.terminal_id, :language_id, ''YES_NO'', d.pump_off_flag) as pump_off_text,');
sql.Add(' language_manager.lookup(d.terminal_id, :language_id, ''DRIVER_STATUS'', d.access_status_code) as access_status_code_text,');
sql.Add(' language_manager.lookup(d.terminal_id, :language_id, ''YES_NO'', d.twic_enabled_flag) as twic_enabled_text,');
sql.Add(' language_manager.lookup(d.terminal_id, :language_id, ''YES_NO'', d.twic_hotlist_flag) as twic_hotlist_text,');
sql.Add(' decode(sign(twic_override_date - sysdate), 1, ''Y'', ''N'') as twic_override_flag,');
sql.Add(' language_manager.lookup(d.terminal_id, :language_id, ''YES_NO'', (decode(sign(twic_override_date - sysdate), 1, ''Y'', ''N''))) as twic_override_text,');
sql.Add(' expiration_manager.get_driver_date(d.terminal_id, d.driver_id, ''9'') as driver_license_exp,');
{ card and carrier tables }
sql.Add(' dcard.card_number as driver_card_id,');
sql.Add(' dcarr.carrier_id as driver_carrier_id,');
sql.Add(' c.name_1 as carrier_name,');
sql.Add(' c.tabs_carrier_id as carrier_scac,');
{ expirations }
sql.Add(' decode(el.description, null, e.description, el.description) as description,');
sql.Add(' de.expiration_id,');

Any other suggestions would really help.

 

[Marco Kalter]

We'll check it out. Can you let me know your Oracle Client version?

 

[djennings]

It is the 64 bit Oracle 12.2 client.

We also tried a PC with the 12.1 64-bit Client and it gave us the same Access Violation when connecting to the 12.2 database.