[Bug]: Oracle Plugin issues found during QA

[btsgh]

Is there an existing issue for this?

• I have searched the existing issues

Description

This is a bug with collection of issues found during the testing of Oracle Plugin PR.

Steps To Reproduce

Refer the How To Test Section in this PR
While testing, found the following -

• Change name of plugin from Oracle Plugin to Oracle.

• If I use admin user with blank password, the test passes.

• Database name field should be shown as required field, since, if we leave it blank, it throws an error to the effect - missing database name.

• This message is not seen on the Edit DS page. - Whitelist the IP 18.223.74.85 and 3.131.104.27 on your database instance to connect to it. Learn more

• The Oracle logo image seems a little off - with some whitebackground - this is not consistent with the other logos

While testing this PR, here are a few issues found:

• For the column with data type - timestamp with local time zone, interval year to month, interval day to second, raw(256),clob, nclob and blob - the values are garbled/different.

• The data displayed in the timestamp field is slightly different from what is displayed on the actual DB

• The data displayed in the binary_float column is different from what is displayed in the actual DB

• Oracle logo needs to be changed. (It does not look like Oracle)

• Found an issue of queries giving intermittent older responses/incorrect responses. Following couple of videos show the behavior -https://ddpa7mfuvv.vmaker.com/record/2ZLmcsM93YRDueE7
  https://ddpa7mfuvv.vmaker.com/record/BMOQcZPP7VOkbNVa

• This particular select statement is giving errors, and am not able to select a row along with timestamp specified.
  select * from test_ap_or where c_varchar2={{'7_varchar2'}}
  and c_nvarchar2={{'7_nvarchar2'}}
  and c_number={{6}}
  and c_float={{161.09}}
  and c_date={{'2002-11-04'}}
  and c_binary_float={{161.09}}
  and c_binary_double={{161.09}}
  and c_timestamp=TO_TIMESTAMP({{'04-11-2002 09:26:50.124'}})
  Note: The above select statement returns the correct row without the c_timestamp section added.
  In this regard - we would need to investigate the usage of date time formats and details with regards this should be added to the Oracle documentation so that the users know how to query for date time values.

• At present, the documentation links turn up empty when we access Documentation from Queries page

Public Sample App

No response

Environment

Deploy Preview

Issue video log

No response

Version

DP

[btsgh]

On re-testing, found the following:

• Data in C_BLOB datatype is inserted as oracle.sql.BLOB@1ebc038f, but in the Oracle DB it is stored [BLOB]
• Data in raw datatype field is inserted as [B@60d45cc6 but in the Oracle DB it is stored raw
• In the Edit DS page, the SSL mode is marked as Optional.
  However, the default option is selected as TLS, and using the oracle db details in the notion doc, when I try to test with the default selected, it fails. So I need to set it to Disable and then try Test, it works
  Could the SSL mode be made as Mandatory?
  Or could we change the SSL mode default selected option as Disabled
• The C_Timestamp field is still having the same issue as before. If this is to do with the formatting of the date in the application, this should be ok. Else this might need to be fixed
• For the binary_float and binary_double columns, when I insert the following values - 151.0012214214214134 and 151.00415315356234624
  The values shown on appsmith is still different from the values shown on the database.

Appsmith -

DB -

The insert command used is as below:
Insert into secondround values (
'4_varchar2',
'4_nvarchar2',
5,
151.00,
'04-OCT-02',
151.0012214214214134,
151.00415315356234624,
TIMESTAMP'2000-01-02 09:26:50.124',
TIMESTAMP'2000-01-02 09:26:56.66 +02:00',
TIMESTAMP'2000-04-04 8:00:00 US/Pacific',
INTERVAL '1' YEAR(3),
INTERVAL '1' HOUR,
utl_raw.cast_to_raw('raw'),
'000001F8.0001.0006',
'000001F8.0001.0006',
'char',
'nchar',
'clob',
'nclob',
utl_raw.cast_to_raw('raw')
)

[sumitsum]

Closing this issue as most of the issues are resolved, the pending ones cannot/should not be addressed and the documentation is in progress:

• The datetime display follows the format that comes on official oracle cloud SQL console:
  

• There is a known issue with client approximating very large numbers and deep fractional sequences.

• The raw and blob types display has been fixed, in order to read them properly users need to write command like select utl_raw.cast_to_varchar2(c_raw) as c_raw, utl_raw.cast_to_varchar2(c_blob) as c_blob from TYPESTEST4

• Created another issue to track improved usage for inserting datetime values.