This is an automated email from the ASF dual-hosted git repository.
ostinru pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry-pxf.git
The following commit(s) were added to refs/heads/main by this push:
new 3935e9b9 Testcontainers: Add Oracle JDBC test (#104)
3935e9b9 is described below
commit 3935e9b9dd41dd5cc9523cfff16daa23f3f27006
Author: Nikolay Antonov <[email protected]>
AuthorDate: Tue Apr 14 12:12:03 2026 +0500
Testcontainers: Add Oracle JDBC test (#104)
Add new tests to cover Cloudberry -> PXF -> JDBC path. In this test we are
verifying that main data Cloudberry and Oracle types can be converted back and
forth.
Test covers Oracle 23 (only supported by Testcontainers version).
---
automation/pom.xml | 12 ++
.../jdbc/oracle/read_types/expected/query01.ans | 39 ++++
.../jdbc/oracle/read_types/sql/query01.sql | 31 ++++
.../jdbc/oracle/write_types/expected/query01.ans | 57 ++++++
.../jdbc/oracle/write_types/sql/query01.sql | 51 +++++
.../automation/testcontainers/OracleContainer.java | 61 ++++++
.../automation/features/jdbc/JdbcOracleTest.java | 205 +++++++++++++++++++++
docs/content/jdbc_pxf_oracle.html.md.erb | 28 ++-
server/pxf-jdbc-drivers/build.gradle | 5 +
9 files changed, 482 insertions(+), 7 deletions(-)
diff --git a/automation/pom.xml b/automation/pom.xml
index d9632ef7..d4db4177 100644
--- a/automation/pom.xml
+++ b/automation/pom.xml
@@ -258,6 +258,18 @@
<classifier>all</classifier>
</dependency>
+ <dependency>
+ <groupId>com.oracle.database.jdbc</groupId>
+ <artifactId>ojdbc11</artifactId>
+ <version>23.3.0.23.09</version>
+ </dependency>
+
+ <dependency>
+ <groupId>org.testcontainers</groupId>
+ <artifactId>testcontainers-oracle-free</artifactId>
+ <version>2.0.4</version>
+ </dependency>
+
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring</artifactId>
diff --git
a/automation/sqlrepo/features/jdbc/oracle/read_types/expected/query01.ans
b/automation/sqlrepo/features/jdbc/oracle/read_types/expected/query01.ans
new file mode 100644
index 00000000..6ae70447
--- /dev/null
+++ b/automation/sqlrepo/features/jdbc/oracle/read_types/expected/query01.ans
@@ -0,0 +1,39 @@
+-- start_ignore
+-- end_ignore
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- @description Oracle JDBC read: expect one row of primitive types from PXF
+SET timezone='utc';
+SET
+
+SELECT
+ i_int,
+ b_big,
+ f_real,
+ d_double,
+ dec_num,
+ t_text,
+ d_date,
+ d_ts
+FROM pxf_ora_oracle_read_types
+LIMIT 1;
+ i_int | b_big | f_real | d_double | dec_num | t_text | d_date |
d_ts
+-------+-------+--------+-----------+------------------+--------+------------+-------------------------
+ 1 | 3 | 1.25 | 3.1415926 | 12345.6789012345 | hello | 2020-01-02 |
2020-01-02 03:04:05.006
+(1 row)
+
diff --git a/automation/sqlrepo/features/jdbc/oracle/read_types/sql/query01.sql
b/automation/sqlrepo/features/jdbc/oracle/read_types/sql/query01.sql
new file mode 100644
index 00000000..bc36a8f1
--- /dev/null
+++ b/automation/sqlrepo/features/jdbc/oracle/read_types/sql/query01.sql
@@ -0,0 +1,31 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- @description Oracle JDBC read: expect one row of primitive types from PXF
+SET timezone='utc';
+
+SELECT
+ i_int,
+ b_big,
+ f_real,
+ d_double,
+ dec_num,
+ t_text,
+ d_date,
+ d_ts
+FROM pxf_ora_oracle_read_types
+ LIMIT 1;
diff --git
a/automation/sqlrepo/features/jdbc/oracle/write_types/expected/query01.ans
b/automation/sqlrepo/features/jdbc/oracle/write_types/expected/query01.ans
new file mode 100644
index 00000000..ec37f1df
--- /dev/null
+++ b/automation/sqlrepo/features/jdbc/oracle/write_types/expected/query01.ans
@@ -0,0 +1,57 @@
+-- start_ignore
+-- end_ignore
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+-- @description Oracle JDBC write: insert full row then verify via readable
external table/fdw
+SET timezone='utc';
+SET
+INSERT INTO pxf_ora_oracle_write_types (
+ i_int,
+ b_big,
+ f_real,
+ d_double,
+ dec_num,
+ t_text,
+ d_date,
+ d_ts
+) VALUES (
+ 1,
+ 3,
+ 1.25,
+ 3.1415926,
+ CAST('12345.6789012345' AS numeric),
+ 'hello',
+ DATE '2020-01-02',
+ TIMESTAMP '2020-01-02 03:04:05.006'
+);
+INSERT 0 1
+SELECT
+ i_int,
+ b_big,
+ f_real,
+ d_double,
+ dec_num,
+ t_text,
+ d_date,
+ d_ts
+FROM pxf_ora_oracle_write_verify
+LIMIT 1;
+ i_int | b_big | f_real | d_double | dec_num | t_text | d_date |
d_ts
+-------+-------+--------+-----------+------------------+--------+------------+-------------------------
+ 1 | 3 | 1.25 | 3.1415926 | 12345.6789012345 | hello | 2020-01-02 |
2020-01-02 03:04:05.006
+(1 row)
+
diff --git
a/automation/sqlrepo/features/jdbc/oracle/write_types/sql/query01.sql
b/automation/sqlrepo/features/jdbc/oracle/write_types/sql/query01.sql
new file mode 100644
index 00000000..461b272d
--- /dev/null
+++ b/automation/sqlrepo/features/jdbc/oracle/write_types/sql/query01.sql
@@ -0,0 +1,51 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- @description Oracle JDBC write: insert full row then verify via readable
external table/fdw
+SET timezone='utc';
+
+INSERT INTO pxf_ora_oracle_write_types (
+ i_int,
+ b_big,
+ f_real,
+ d_double,
+ dec_num,
+ t_text,
+ d_date,
+ d_ts
+) VALUES (
+ 1,
+ 3,
+ 1.25,
+ 3.1415926,
+ CAST('12345.6789012345' AS numeric),
+ 'hello',
+ DATE '2020-01-02',
+ TIMESTAMP '2020-01-02 03:04:05.006'
+);
+
+SELECT
+ i_int,
+ b_big,
+ f_real,
+ d_double,
+ dec_num,
+ t_text,
+ d_date,
+ d_ts
+FROM pxf_ora_oracle_write_verify
+ LIMIT 1;
diff --git
a/automation/src/main/java/org/apache/cloudberry/pxf/automation/testcontainers/OracleContainer.java
b/automation/src/main/java/org/apache/cloudberry/pxf/automation/testcontainers/OracleContainer.java
new file mode 100644
index 00000000..52dfe9ad
--- /dev/null
+++
b/automation/src/main/java/org/apache/cloudberry/pxf/automation/testcontainers/OracleContainer.java
@@ -0,0 +1,61 @@
+package org.apache.cloudberry.pxf.automation.testcontainers;
+
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+import org.testcontainers.containers.Network;
+import org.testcontainers.utility.DockerImageName;
+
+/**
+ * TestContainers wrapper around Oracle Database Free.
+ *
+ * The container joins a shared Docker network with alias `oracle`,
+ * so PXF inside the Cloudberry container can reach it at `oracle:1521`
+ *
+ */
+public class OracleContainer extends org.testcontainers.oracle.OracleContainer
{
+
+ private static final String DEFAULT_IMAGE = "gvenzl/oracle-free:23-slim";
+ private static final String NETWORK_ALIAS = "oracle";
+
+ public static final int ORACLE_PORT = 1521;
+ public static final String SERVICE_NAME = "FREE";
+
+ public static final String ORACLE_USER = "system";
+ public static final String ORACLE_PASSWORD = "pxf-test";
+
+ public OracleContainer(Network network) {
+ super(DockerImageName.parse(DEFAULT_IMAGE));
+
+ withPassword(ORACLE_PASSWORD);
+ withNetwork(network);
+ withNetworkAliases(NETWORK_ALIAS);
+ }
+
+ /** JDBC URL reachable from the host (mapped `ORACLE_PORT`) */
+ @Override
+ public String getJdbcUrl() {
+ return "jdbc:oracle:thin:@localhost:" + getMappedPort(ORACLE_PORT) +
"/" + SERVICE_NAME;
+ }
+
+ /** JDBC URL for PXF / other containers on the same Docker network */
+ public String getInternalJdbcUrl() {
+ return "jdbc:oracle:thin:@" + NETWORK_ALIAS + ":" + ORACLE_PORT + "/"
+ SERVICE_NAME;
+ }
+}
diff --git
a/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcOracleTest.java
b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcOracleTest.java
new file mode 100644
index 00000000..4d80b4d1
--- /dev/null
+++
b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcOracleTest.java
@@ -0,0 +1,205 @@
+package org.apache.cloudberry.pxf.automation.features.jdbc;
+
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+import annotations.WorksWithFDW;
+import org.apache.cloudberry.pxf.automation.AbstractTestcontainersTest;
+import
org.apache.cloudberry.pxf.automation.structures.tables.pxf.ExternalTable;
+import
org.apache.cloudberry.pxf.automation.structures.tables.utils.TableFactory;
+import org.apache.cloudberry.pxf.automation.testcontainers.OracleContainer;
+import org.testng.Assert;
+import org.testng.annotations.Test;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Timestamp;
+
+@WorksWithFDW
+public class JdbcOracleTest extends AbstractTestcontainersTest {
+
+ private static final String ORACLE_DRIVER =
"oracle.jdbc.driver.OracleDriver";
+
+ private static final String ORACLE_TABLE_READ = "pxf_types_read";
+ private static final String ORACLE_TABLE_WRITE = "pxf_types_write";
+
+ private static final String ORACLE_PXF_PATH_READ = "SYSTEM.pxf_types_read";
+ private static final String ORACLE_PXF_PATH_WRITE =
"SYSTEM.pxf_types_write";
+
+ /** PXF external/foreign table column definitions */
+ private static final String[] ORACLE_PXF_FIELDS = new String[] {
+ "i_int int",
+ "b_big bigint",
+ "f_real real",
+ "d_double double precision",
+ "dec_num numeric",
+ "t_text text",
+ "d_date date",
+ "d_ts timestamp"
+ };
+
+ private static final int V_I_INT = 1;
+ private static final long V_B_BIG = 3L;
+ private static final float V_F_REAL = 1.25f;
+ private static final double V_D_DOUBLE = 3.1415926d;
+ private static final String V_DEC_TEXT = "12345.6789012345";
+ private static final String V_T_TEXT = "hello";
+ private static final String V_D_DATE = "2020-01-02";
+ private static final String V_D_TS = "2020-01-02 03:04:05.006";
+
+ private OracleContainer oracleContainer;
+
+ @Override
+ public void beforeClass() throws Exception {
+ oracleContainer = new OracleContainer(container.getSharedNetwork());
+ oracleContainer.start();
+
+ Assert.assertTrue(container.isRunning(), "PXFCloudberry container
should be running");
+ Assert.assertTrue(oracleContainer.isRunning(), "Oracle container
should be running");
+ }
+
+ @Override
+ public void afterClass() throws Exception {
+ if (oracleContainer != null) {
+ oracleContainer.stop();
+ }
+ }
+
+ @Test(groups = {"testcontainers", "pxf-jdbc"})
+ public void readSupportedTypes() throws Exception {
+ createAndSeedOracleReadTable(oracleContainer.getJdbcUrl());
+
+ ExternalTable pxfRead = TableFactory.getPxfJdbcReadableTable(
+ "pxf_ora_oracle_read_types",
+ ORACLE_PXF_FIELDS,
+ ORACLE_PXF_PATH_READ,
+ ORACLE_DRIVER,
+ oracleContainer.getInternalJdbcUrl(),
+ OracleContainer.ORACLE_USER,
+ "PASS=" + OracleContainer.ORACLE_PASSWORD);
+ pxfRead.setHost(pxfHost);
+ pxfRead.setPort(pxfPort);
+ cloudberry.createTableAndVerify(pxfRead);
+
+ try {
+ regress.runSqlTest("features/jdbc/oracle/read_types");
+ } finally {
+ cloudberry.dropTable(pxfRead, true);
+ }
+ }
+
+ @Test(groups = {"testcontainers", "pxf-jdbc"})
+ public void writeSupportedTypes() throws Exception {
+ createOracleWriteTable(oracleContainer.getJdbcUrl());
+
+ ExternalTable pxfWrite = TableFactory.getPxfJdbcWritableTable(
+ "pxf_ora_oracle_write_types",
+ ORACLE_PXF_FIELDS,
+ ORACLE_PXF_PATH_WRITE,
+ ORACLE_DRIVER,
+ oracleContainer.getInternalJdbcUrl(),
+ OracleContainer.ORACLE_USER,
+ "PASS=" + OracleContainer.ORACLE_PASSWORD);
+ pxfWrite.setHost(pxfHost);
+ pxfWrite.setPort(pxfPort);
+ cloudberry.createTableAndVerify(pxfWrite);
+
+ ExternalTable pxfVerify = TableFactory.getPxfJdbcReadableTable(
+ "pxf_ora_oracle_write_verify",
+ ORACLE_PXF_FIELDS,
+ ORACLE_PXF_PATH_WRITE,
+ ORACLE_DRIVER,
+ oracleContainer.getInternalJdbcUrl(),
+ OracleContainer.ORACLE_USER,
+ "PASS=" + OracleContainer.ORACLE_PASSWORD);
+ pxfVerify.setHost(pxfHost);
+ pxfVerify.setPort(pxfPort);
+ cloudberry.createTableAndVerify(pxfVerify);
+
+ try {
+ regress.runSqlTest("features/jdbc/oracle/write_types");
+ } finally {
+ cloudberry.dropTable(pxfVerify, true);
+ cloudberry.dropTable(pxfWrite, true);
+ }
+ }
+
+ private void createAndSeedOracleReadTable(String jdbcUrl) throws
SQLException {
+ try (Connection conn = openOracleConnection(jdbcUrl)) {
+ createOracleServerTable(conn, ORACLE_TABLE_READ);
+ insertOracleReadFixture(conn);
+ }
+ }
+
+ private void createOracleWriteTable(String jdbcUrl) throws SQLException {
+ try (Connection conn = openOracleConnection(jdbcUrl)) {
+ createOracleServerTable(conn, ORACLE_TABLE_WRITE);
+ }
+ }
+
+ /** Creates Oracle table DROP if exists + CREATE */
+ private void createOracleServerTable(Connection conn, String tableName)
throws SQLException {
+ try (Statement st = conn.createStatement()) {
+ // Oracle does not support DROP TABLE IF EXISTS before 23c; use
PL/SQL block
+ st.execute("BEGIN"
+ + " EXECUTE IMMEDIATE 'DROP TABLE " + tableName + "
PURGE';"
+ + "EXCEPTION WHEN OTHERS THEN"
+ + " IF SQLCODE != -942 THEN RAISE; END IF;"
+ + "END;");
+ st.execute("CREATE TABLE " + tableName + " ("
+ + "i_int NUMBER(10), "
+ + "b_big NUMBER(19), "
+ + "f_real BINARY_FLOAT, "
+ + "d_double BINARY_DOUBLE, "
+ + "dec_num NUMBER(38,10), "
+ + "t_text VARCHAR2(4000), "
+ + "d_date DATE, "
+ + "d_ts TIMESTAMP(3)"
+ + ")");
+ }
+ }
+
+ /** Inserts fixture row into ORACLE_TABLE_READ for the read test */
+ private void insertOracleReadFixture(Connection conn) throws SQLException {
+ String insertSql = "INSERT INTO " + ORACLE_TABLE_READ + " ("
+ + "i_int, b_big, f_real, d_double, dec_num, t_text, d_date,
d_ts"
+ + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
+
+ try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
+ ps.setInt(1, V_I_INT);
+ ps.setLong(2, V_B_BIG);
+ ps.setFloat(3, V_F_REAL);
+ ps.setDouble(4, V_D_DOUBLE);
+ ps.setBigDecimal(5, new BigDecimal(V_DEC_TEXT));
+ ps.setString(6, V_T_TEXT);
+ ps.setDate(7, Date.valueOf(V_D_DATE));
+ ps.setTimestamp(8, Timestamp.valueOf(V_D_TS));
+ ps.executeUpdate();
+ }
+ }
+
+ private Connection openOracleConnection(String jdbcUrl) throws
SQLException {
+ return DriverManager.getConnection(jdbcUrl,
OracleContainer.ORACLE_USER, OracleContainer.ORACLE_PASSWORD);
+ }
+}
diff --git a/docs/content/jdbc_pxf_oracle.html.md.erb
b/docs/content/jdbc_pxf_oracle.html.md.erb
index dc37494b..88925b46 100644
--- a/docs/content/jdbc_pxf_oracle.html.md.erb
+++ b/docs/content/jdbc_pxf_oracle.html.md.erb
@@ -80,20 +80,20 @@ Perform the following steps to create an Oracle table named
`countries` in the s
You must create a JDBC server configuration for Oracle, download the Oracle
driver JAR file to your system, copy the JAR file to the PXF user configuration
directory, synchronize the PXF configuration, and then restart PXF.
-This procedure will typically be performed by the Greenplum Database
administrator.
+This procedure will typically be performed by the Apache Cloudberry
administrator.
-1. Download the Oracle JDBC driver and place it under `$PXF_BASE/lib` of your
Greenplum Database coordinator host. If you [relocated
$PXF_BASE](about_pxf_dir.html#movebase), make sure you use the updated
location. You can download a Oracle JDBC driver from your preferred download
location. The following example places a driver downloaded from Oracle webiste
under `$PXF_BASE/lib` of the Greenplum Database coordinator:
+1. Download the Oracle JDBC driver and place it under `$PXF_BASE/lib` of your
Apache Cloudberry coordinator host. If you [relocated
$PXF_BASE](about_pxf_dir.html#movebase), make sure you use the updated
location. You can download a Oracle JDBC driver from your preferred download
location. The following example places a driver downloaded from Oracle webiste
under `$PXF_BASE/lib` of the Apache Cloudberry coordinator:
- 1. If you did not relocate `$PXF_BASE`, run the following from the
Greenplum coordinator:
+ 1. If you did not relocate `$PXF_BASE`, run the following from the
Cloudberry coordinator:
```shell
- gpadmin@coordinator$ scp ojdbc10.jar
gpadmin@coordinator:/usr/local/pxf-gp<version>/lib/
+ gpadmin@coordinator$ scp ojdbc11.jar
gpadmin@coordinator:/usr/local/pxf-gp<version>/lib/
```
- 2. If you relocated `$PXF_BASE`, run the following from the Greenplum
coordinator:
+ 2. If you relocated `$PXF_BASE`, run the following from the Cloudberry
coordinator:
```shell
- gpadmin@coordinator$ scp ojdbc10.jar gpadmin@coordinator:$PXF_BASE/lib/
+ gpadmin@coordinator$ scp ojdbc11.jar gpadmin@coordinator:$PXF_BASE/lib/
```
1. Synchronize the PXF configuration, and then restart PXF:
@@ -131,7 +131,7 @@ This procedure will typically be performed by the Greenplum
Database administrat
</configuration>
```
-3. Synchronize the PXF server configuration to the Greenplum Database cluster:
+3. Synchronize the PXF server configuration to the Apache Cloudberry cluster:
``` shell
gpadmin@coordinator$ pxf cluster sync
@@ -149,6 +149,18 @@ Perform the following procedure to create a PXF external
table that references t
FORMAT 'CUSTOM' (formatter='pxfwritable_import');
```
+ OR create the PXF foreign table specifying `jdbc_pxf_fdw` Foreign Data
Wrapper. For example:
+
+ ```sql
+ gpadmin=# CREATE SERVER "oracle" FOREIGN DATA WRAPPER jdbc_pxf_fdw;
+ gpadmin=# CREATE USER MAPPING FOR CURRENT_USER SERVER "oracle";
+ gpadmin=# CREATE FOREIGN TABLE oracle_countries (country_id int,
country_name varchar, population float)
+ SERVER "oracle"
+ OPTIONS (
+ resource 'oracleuser.countries'
+ );
+ ```
+
2. Display all rows of the `oracle_countries` table:
``` sql
@@ -172,6 +184,8 @@ Perform the following procedure to insert some data into
the `countries` Oracle
FORMAT 'CUSTOM' (formatter='pxfwritable_export');
```
+ OR reuse PXF foreign table `oracle_countries` created in [Read from the
Oracle Table](#ex_readjdbc).
+
4. Insert some data into the `oracle_countries_write` table. For example:
``` sql
diff --git a/server/pxf-jdbc-drivers/build.gradle
b/server/pxf-jdbc-drivers/build.gradle
index 052eef02..19e2f721 100644
--- a/server/pxf-jdbc-drivers/build.gradle
+++ b/server/pxf-jdbc-drivers/build.gradle
@@ -24,4 +24,9 @@ dependencies {
// Clickhouse
implementation('com.clickhouse:clickhouse-jdbc:0.9.8:all')
{ transitive = false }
+ // Oracle
+ implementation('com.oracle.database.jdbc:ojdbc11:23.3.0.23.09')
+ implementation('com.oracle.database.jdbc:ucp11:23.3.0.23.09')
+ implementation('com.oracle.database.nls:orai18n:23.3.0.23.09')
+
}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
