Oracle

Step 1: Create a data source

There are two ways to create a data source: click the "+" button next to Data Source in the sidebar, or click the Create a Data Source button on the Overview page.

Step 2: Configure the connection

In the dialog form, fill in the connection information and click Submit.

The connection fields are described below.

Basic

• Name: The display name of the database in Canner Enterprise. You can change it later.
• Data Source Type: The database type. Select Oracle.

Data Source Properties

• URL: The database address, in the format <hostname>:<port>.

• Username: The username used to connect to this Oracle database.

• Password: The password used to connect to this Oracle database.

• Connect Type: Connect using Oracle's SID or SERVICE_NAME.

• SID / Service Name: The SID or SERVICE_NAME, depending on the Connect Type you chose.

• Case Sensitive: Table names in Canner Enterprise data sources are displayed in lowercase, and case is not distinguished. Choose how to handle multiple tables whose names differ only in case.

  1. One Only (default): Multiple tables whose names differ only in case are not supported, so the system shows a connection error after you submit.
  2. First Result: The first table scanned is kept; other tables with the same name are not connected.
  3. Lowercase First: The first table scanned with an all-lowercase name is kept; other tables with the same name are not connected (if none is all lowercase, the first table scanned is kept).
  4. Uppercase First: The first table scanned with an all-uppercase name is kept; other tables with the same name are not connected (if none is all uppercase, the first table scanned is kept).

  Example: the database you connect to has three tables named Table, table, and TABLE
  1. One Only - after submitting, the connection fails.
  2. First Result - the Table table is kept.
  3. Lowercase First - the table table is kept.
  4. Uppercase First - the TABLE table is kept.

• Table Pattern: A Java regular expression to connect only the tables that match. We recommend setting this in the following three situations:

  1. You only want to connect tables matching a specific pattern to Canner Enterprise.
  2. You want to manage one source database as multiple data sources in Canner Enterprise.
  3. The number of tables in the database exceeds the system limit, so you need to split it into multiple data sources.

  Three Table Pattern examples:
  Example 1: ^p.*\..* connects only the tables in schemas starting with p.
  Example 2: public\..* connects only the tables in schemas starting with public.
  Example 3: (public\..*)|(testing\..*) connects the tables in schemas starting with public or testing.

info

Table names in Canner Enterprise data sources are displayed in lowercase and case is not distinguished, so Table Pattern only supports lowercase conditions. If your Java regular expression contains uppercase characters, they are automatically converted to lowercase.

• Join Pushdown (off by default): Pushes JOIN conditions in SQL down to the source Oracle database for execution.

info

Enabling Join Pushdown can improve SQL query performance, but note that:

1. Whether it improves performance depends on the JOIN conditions in your queries
2. It uses more resources on the source Oracle database than when it is off

We recommend considering both points before enabling it

• Number rounding mode: When a SQL query uses a table from this data source and a numeric column has more decimal places than the Default Scale, choose how the system handles it (this database has Default Scale = 0, so this setting decides how decimals in numeric columns are rounded to integers).

  1. Unnecessary (default): The query fails with an error message.
  2. Up: Round away from zero.
  3. Down: Round towards zero.
  4. Ceiling: Round towards positive infinity.
  5. Floor: Round towards negative infinity.
  6. Half Up: Round towards the nearest neighbor, unless both neighbors are equidistant, in which case round up.
  7. Half Down: Round towards the nearest neighbor, unless both neighbors are equidistant, in which case round down.
  8. Half Even: Round towards the nearest neighbor, unless both neighbors are equidistant, in which case round towards the even neighbor.

  Rounding examples:

Input Number	UP	DOWN	CEILING	FLOOR	HALF_UP	HALF_DOWN	HALF_EVEN	UNNECESSARY
5.5	6	5	6	5	6	5	6	throw ArithmeticException
2.5	3	2	3	2	3	2	2	throw ArithmeticException
1.6	2	1	2	1	2	2	2	throw ArithmeticException
1.1	2	1	2	1	1	1	1	throw ArithmeticException
1.0	1	1	1	1	1	1	1	1
-1.0	-1	-1	-1	-1	-1	-1	-1	-1
-1.1	-2	-1	-1	-2	-1	-1	-1	throw ArithmeticException
-1.6	-2	-1	-1	-2	-2	-2	-2	throw ArithmeticException
-2.5	-3	-2	-2	-3	-3	-2	-2	throw ArithmeticException
-5.5	-6	-5	-5	-6	-6	-5	-6	throw ArithmeticException

• Default Scale: The number of decimal places kept in numeric columns. For this database the default is 0, i.e. rounded to integers.
• Explore Timeout: The timeout for connecting to the data source. If connecting takes longer than this, the system stops and shows a connection error. The default timeout is 60000 ms. If the database you are connecting to has many tables, consider increasing this timeout.

Step 3: Finish

After you submit, the Oracle data source appears in the sidebar shortly. Click it to open the data source details page.