SQL Struct with @Lob (BLOB, CLOB) used as stored procedure parameter type - fix (#1302)
SQL Struct with @Lob (BLOB, CLOB) used as stored procedure parameter type - fix
Bugfix and unit test.
Signed-off-by: Radek Felcman <radek.felcman@oracle.com>
diff --git a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
index 04b0cff..301862b 100644
--- a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
+++ b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
@@ -3389,6 +3389,16 @@
/**
* INTERNAL:
+ * This method builds a Struct using the unwrapped connection within the session
+ * @return Struct
+ */
+ public Struct createStruct(String structTypeName, Object[] attributes, AbstractRecord row, Vector orderedFields, AbstractSession session, Connection connection) throws SQLException {
+ java.sql.Connection unwrappedConnection = getConnection(session, connection);
+ return createStruct(structTypeName,attributes,unwrappedConnection);
+ }
+
+ /**
+ * INTERNAL:
* Platforms that support java.sql.Array may override this method.
* @return Array
*/
diff --git a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/helper/ComplexDatabaseType.java b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/helper/ComplexDatabaseType.java
index 7feff65..7128406 100644
--- a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/helper/ComplexDatabaseType.java
+++ b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/helper/ComplexDatabaseType.java
@@ -1,5 +1,5 @@
/*
- * Copyright (c) 1998, 2020 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -189,17 +189,17 @@
@Override
public void buildBeginBlock(StringBuilder sb, PLSQLargument arg, PLSQLStoredProcedureCall call) {
- String sql2PlName = call.getSQL2PlName(this);
- if (sql2PlName == null) {
+ String conversionRoutine = ((this.getTypeName().equals(this.getCompatibleType()))) ? call.getPl2SQLName(this) : call.getSQL2PlName(this);
+ if (conversionRoutine == null) {
// TODO exception
- throw new NullPointerException("no SQL2Pl conversion routine for " + typeName);
+ throw new NullPointerException("no SQL2Pl or Pl2SQL conversion routine for " + typeName);
}
String target = databaseTypeHelper.buildTarget(arg);
String compat = databaseTypeHelper.buildCompatible(arg);
sb.append(" ");
sb.append(target);
sb.append(" := ");
- sb.append(sql2PlName);
+ sb.append(conversionRoutine);
sb.append("(");
sb.append(compat);
sb.append(");");
diff --git a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/mappings/structures/ObjectRelationalDataTypeDescriptor.java b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/mappings/structures/ObjectRelationalDataTypeDescriptor.java
index eef5a11..3ab71ea 100644
--- a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/mappings/structures/ObjectRelationalDataTypeDescriptor.java
+++ b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/mappings/structures/ObjectRelationalDataTypeDescriptor.java
@@ -1,5 +1,5 @@
/*
- * Copyright (c) 1998, 2019 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -339,7 +339,7 @@
fields[index] = row.get(field);
}
- structure = session.getPlatform().createStruct(getStructureName(), fields, session, connection);
+ structure = session.getPlatform().createStruct(getStructureName(), fields, row, getOrderedFields(), session, connection);
} catch (java.sql.SQLException exception) {
throw DatabaseException.sqlException(exception, session, false);
} finally {
diff --git a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.java b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.java
index 04d568b..5e8b38e 100644
--- a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.java
+++ b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.java
@@ -670,20 +670,26 @@
if (info == null) {
info = generateNestedFunction(type, isNestedTable);
}
- if (argument.direction == IN) {
- if (!functions.contains(info.sql2PlConv)) {
- functions.add(info.sql2PlConv);
- }
- } else if (argument.direction == INOUT) {
- if (!functions.contains(info.sql2PlConv)) {
- functions.add(info.sql2PlConv);
- }
+ if (type.getTypeName().equals(type.getCompatibleType())) {
if (!functions.contains(info.pl2SqlConv)) {
functions.add(info.pl2SqlConv);
}
- } else if (argument.direction == OUT) {
- if (!functions.contains(info.pl2SqlConv)) {
- functions.add(info.pl2SqlConv);
+ } else {
+ if (argument.direction == IN) {
+ if (!functions.contains(info.sql2PlConv)) {
+ functions.add(info.sql2PlConv);
+ }
+ } else if (argument.direction == INOUT) {
+ if (!functions.contains(info.sql2PlConv)) {
+ functions.add(info.sql2PlConv);
+ }
+ if (!functions.contains(info.pl2SqlConv)) {
+ functions.add(info.pl2SqlConv);
+ }
+ } else if (argument.direction == OUT) {
+ if (!functions.contains(info.pl2SqlConv)) {
+ functions.add(info.pl2SqlConv);
+ }
}
}
}
diff --git a/foundation/org.eclipse.persistence.oracle/src/main/java/org/eclipse/persistence/platform/database/oracle/Oracle12Platform.java b/foundation/org.eclipse.persistence.oracle/src/main/java/org/eclipse/persistence/platform/database/oracle/Oracle12Platform.java
index af6022b..2c70171 100644
--- a/foundation/org.eclipse.persistence.oracle/src/main/java/org/eclipse/persistence/platform/database/oracle/Oracle12Platform.java
+++ b/foundation/org.eclipse.persistence.oracle/src/main/java/org/eclipse/persistence/platform/database/oracle/Oracle12Platform.java
@@ -1,5 +1,5 @@
/*
- * Copyright (c) 2014, 2020 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 2014, 2021 Oracle and/or its affiliates. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -18,12 +18,22 @@
import java.io.IOException;
import java.io.Writer;
+import java.sql.Blob;
+import java.sql.Clob;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Struct;
import java.util.Map;
import java.util.Set;
+import java.util.Vector;
import java.util.concurrent.ConcurrentHashMap;
import org.eclipse.persistence.descriptors.ClassDescriptor;
+import org.eclipse.persistence.exceptions.ConversionException;
import org.eclipse.persistence.exceptions.ValidationException;
+import org.eclipse.persistence.internal.helper.ClassConstants;
+import org.eclipse.persistence.internal.helper.DatabaseField;
+import org.eclipse.persistence.internal.sessions.AbstractRecord;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.internal.sessions.EmptyRecord;
import org.eclipse.persistence.logging.SessionLog;
@@ -151,4 +161,29 @@
}
}
+ /**
+ * INTERNAL:
+ * This method builds a Struct using the unwrapped connection within the session
+ * @return Struct
+ */
+ @Override
+ public Struct createStruct(String structTypeName, Object[] attributes, AbstractRecord row, Vector orderedFields, AbstractSession session, Connection connection) throws SQLException {
+ for (int index = 0; index < orderedFields.size(); index++) {
+ DatabaseField field = (DatabaseField)orderedFields.elementAt(index);
+ if (row.getField(field) != null && row.getField(field).getTypeName() != null) {
+ if (ClassConstants.BLOB.getTypeName().equals(row.getField(field).getTypeName())) {
+ Blob blob = connection.createBlob();
+ blob.setBytes(1L, (byte[]) row.get(field));
+ attributes[index] = blob;
+ } else if (ClassConstants.CLOB.getTypeName().equals(row.getField(field).getTypeName())) {
+ Clob clob = connection.createClob();
+ clob.setString(1L, (String) attributes[index]);
+ attributes[index] = clob;
+ }
+ } else {
+ attributes[index] = row.get(field);
+ }
+ }
+ return super.createStruct(structTypeName, attributes, session, connection);
+ }
}
diff --git a/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/InnerObjBlob.java b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/InnerObjBlob.java
new file mode 100644
index 0000000..16949ab
--- /dev/null
+++ b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/InnerObjBlob.java
@@ -0,0 +1,80 @@
+/*
+ * Copyright (c) 2021 Oracle and/or its affiliates. All rights reserved.
+ *
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v. 2.0 which is available at
+ * http://www.eclipse.org/legal/epl-2.0,
+ * or the Eclipse Distribution License v. 1.0 which is available at
+ * http://www.eclipse.org/org/documents/edl-v10.php.
+ *
+ * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
+ */
+
+// Contributors:
+// Oracle - initial implementation
+package org.eclipse.persistence.testing.models.jpa.plsql;
+
+import jakarta.persistence.Column;
+import jakarta.persistence.Embeddable;
+import jakarta.persistence.Lob;
+import org.eclipse.persistence.annotations.Struct;
+
+import java.util.Arrays;
+
+@Embeddable
+@Struct(name="PLSQL_P_PLSQL_INNER_BLOB_REC", fields={"BLOB_ID", "BLOB_CONTENT", "CLOB_CONTENT"})
+public class InnerObjBlob {
+
+ public InnerObjBlob() {
+ }
+
+ public InnerObjBlob(int blobId, byte[] blobContent, String clobContent) {
+ this.blobId = blobId;
+ this.blobContent = blobContent;
+ this.clobContent = clobContent;
+ }
+
+ @Column(name="BLOB_ID")
+ private int blobId;
+
+ @Column(name="BLOB_CONTENT")
+ @Lob
+ private byte[] blobContent;
+
+ @Column(name="CLOB_CONTENT")
+ @Lob
+ private String clobContent;
+
+ public int getBlobId() {
+ return blobId;
+ }
+
+ public void setBlobId(int blobId) {
+ this.blobId = blobId;
+ }
+
+ public byte[] getBlobContent() {
+ return blobContent;
+ }
+
+ public void setBlobContent(byte[] blobContent) {
+ this.blobContent = blobContent;
+ }
+
+ public String getClobContent() {
+ return clobContent;
+ }
+
+ public void setClobContent(String clobContent) {
+ this.clobContent = clobContent;
+ }
+
+ @Override
+ public String toString() {
+ return "InnerObjBlob{" +
+ "blobId=" + blobId +
+ ", blobContent=" + Arrays.toString(blobContent) +
+ ", clobContent='" + clobContent + '\'' +
+ '}';
+ }
+}
diff --git a/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/OuterObjBlob.java b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/OuterObjBlob.java
new file mode 100644
index 0000000..db45308
--- /dev/null
+++ b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/OuterObjBlob.java
@@ -0,0 +1,62 @@
+/*
+ * Copyright (c) 2021 Oracle and/or its affiliates. All rights reserved.
+ *
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v. 2.0 which is available at
+ * http://www.eclipse.org/legal/epl-2.0,
+ * or the Eclipse Distribution License v. 1.0 which is available at
+ * http://www.eclipse.org/org/documents/edl-v10.php.
+ *
+ * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
+ */
+
+// Contributors:
+// Oracle - initial implementation
+package org.eclipse.persistence.testing.models.jpa.plsql;
+
+import jakarta.persistence.Column;
+import jakarta.persistence.Embeddable;
+import org.eclipse.persistence.annotations.Struct;
+
+@Embeddable
+@Struct(name="PLSQL_P_PLSQL_OUTER_STRUCT_REC", fields={"STRUCT_ID", "STRUCT_CONTENT"})
+public class OuterObjBlob {
+
+ public OuterObjBlob() {
+ }
+
+ public OuterObjBlob(int structId, InnerObjBlob structContent) {
+ this.structId = structId;
+ this.structContent = structContent;
+ }
+
+ @Column(name="STRUCT_ID")
+ private int structId;
+
+ @Column(name="STRUCT_CONTENT")
+ private InnerObjBlob structContent;
+
+ public int getStructId() {
+ return structId;
+ }
+
+ public void setStructId(int structId) {
+ this.structId = structId;
+ }
+
+ public InnerObjBlob getStructContent() {
+ return structContent;
+ }
+
+ public void setStructContent(InnerObjBlob structContent) {
+ this.structContent = structContent;
+ }
+
+ @Override
+ public String toString() {
+ return "OuterObjBBlob{" +
+ "structId=" + structId +
+ ", structContent=" + structContent +
+ '}';
+ }
+}
diff --git a/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/SaveComplexBlob.java b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/SaveComplexBlob.java
new file mode 100644
index 0000000..ad84ea6
--- /dev/null
+++ b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/models/jpa/plsql/SaveComplexBlob.java
@@ -0,0 +1,66 @@
+/*
+ * Copyright (c) 2021 Oracle and/or its affiliates. All rights reserved.
+ *
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v. 2.0 which is available at
+ * http://www.eclipse.org/legal/epl-2.0,
+ * or the Eclipse Distribution License v. 1.0 which is available at
+ * http://www.eclipse.org/org/documents/edl-v10.php.
+ *
+ * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
+ */
+
+// Contributors:
+// Oracle - initial implementation
+package org.eclipse.persistence.testing.models.jpa.plsql;
+
+import jakarta.persistence.Column;
+import jakarta.persistence.Entity;
+import jakarta.persistence.Id;
+import org.eclipse.persistence.annotations.Direction;
+import org.eclipse.persistence.platform.database.oracle.annotations.*;
+
+@Entity
+@NamedPLSQLStoredFunctionQueries(value = {
+ @NamedPLSQLStoredFunctionQuery(name = "PLSQL_BLOB_REC_IN", functionName = "PLSQL_P.PLSQL_BLOB_REC_IN",
+ parameters = {@PLSQLParameter(name = "P_BLOB_REC", direction = Direction.IN, databaseType = "PLSQL_P_PLSQL_INNER_BLOB_REC"),
+ @PLSQLParameter(name = "P_CITY", direction = Direction.IN, databaseType = "VARCHAR_TYPE")},
+ returnParameter = @PLSQLParameter(name = "RESULT", direction = Direction.OUT, databaseType = "VARCHAR_TYPE")),
+ @NamedPLSQLStoredFunctionQuery(name = "PLSQL_STRUCT_INSTRUCT_REC_IN", functionName = "PLSQL_P.PLSQL_STRUCT_INSTRUCT_REC_IN",
+ parameters = {@PLSQLParameter(name = "P_BLOB_REC", databaseType = "PLSQL_P_PLSQL_INNER_BLOB_REC"),
+ @PLSQLParameter(name = "P_STRUCT_REC", databaseType = "PLSQL_P_PLSQL_OUTER_STRUCT_REC"),
+ @PLSQLParameter(name = "P_CITY", databaseType = "VARCHAR_TYPE")},
+ returnParameter = @PLSQLParameter(name = "RESULT", direction = Direction.OUT, databaseType = "VARCHAR_TYPE"))
+ }
+)
+@PLSQLRecords(value = {
+ @PLSQLRecord(name = "PLSQL_P_PLSQL_INNER_BLOB_REC", compatibleType = "PLSQL_P_PLSQL_INNER_BLOB_REC", javaType = InnerObjBlob.class,
+ fields = {
+ @PLSQLParameter(name = "BLOB_ID", databaseType = "NUMERIC_TYPE"),
+ @PLSQLParameter(name = "BLOB_CONTENT", databaseType = "BLOB_TYPE"),
+ @PLSQLParameter(name = "CLOB_CONTENT", databaseType = "CLOB_TYPE")
+ }
+ ),
+ @PLSQLRecord(name = "PLSQL_P_PLSQL_OUTER_STRUCT_REC", compatibleType = "PLSQL_P_PLSQL_OUTER_STRUCT_REC", javaType = OuterObjBlob.class,
+ fields = {
+ @PLSQLParameter(name = "STRUCT_ID", databaseType = "NUMERIC_TYPE"),
+ @PLSQLParameter(name = "STRUCT_CONTENT", databaseType = "PLSQL_P_PLSQL_INNER_BLOB_REC")
+ }
+
+ )
+}
+)
+public class SaveComplexBlob {
+
+ @Id
+ @Column(name = "RESULT")
+ private String result;
+
+ public String getResult() {
+ return result;
+ }
+
+ public void setResult(String result) {
+ this.result = result;
+ }
+}
diff --git a/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/tests/jpa/plsql/PLSQLTestSuite.java b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/tests/jpa/plsql/PLSQLTestSuite.java
index 681fcf2..c58f304 100644
--- a/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/tests/jpa/plsql/PLSQLTestSuite.java
+++ b/jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/tests/jpa/plsql/PLSQLTestSuite.java
@@ -15,6 +15,7 @@
package org.eclipse.persistence.testing.tests.jpa.plsql;
import java.math.BigDecimal;
+import java.util.Base64;
import java.util.List;
import jakarta.persistence.EntityManager;
@@ -26,16 +27,16 @@
import org.eclipse.persistence.internal.helper.DatabaseType;
import org.eclipse.persistence.internal.jpa.EJBQueryImpl;
import org.eclipse.persistence.platform.database.oracle.annotations.OracleArray;
-import org.eclipse.persistence.platform.database.oracle.annotations.PLSQLParameter;
import org.eclipse.persistence.platform.database.oracle.jdbc.OracleArrayType;
import org.eclipse.persistence.platform.database.oracle.jdbc.OracleObjectType;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLargument;
-import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
import org.eclipse.persistence.testing.models.jpa.plsql.Address;
import org.eclipse.persistence.testing.models.jpa.plsql.Employee;
+import org.eclipse.persistence.testing.models.jpa.plsql.InnerObjBlob;
+import org.eclipse.persistence.testing.models.jpa.plsql.OuterObjBlob;
import org.eclipse.persistence.testing.models.jpa.plsql.Phone;
public class PLSQLTestSuite extends JUnitTestCase {
@@ -49,7 +50,9 @@
suite.addTest(new PLSQLTestSuite("testTableOut"));
suite.addTest(new PLSQLTestSuite("testEmpRecordInOut"));
suite.addTest(new PLSQLTestSuite("testConsultant"));
- suite.addTest(new PLSQLTestSuite("testOracleTypeProcessing"));
+ suite.addTest(new PLSQLTestSuite("testOracleTypeProcessing"));
+ suite.addTest(new PLSQLTestSuite("testBlobInStruct"));
+ suite.addTest(new PLSQLTestSuite("testStructInStruct"));
return suite;
}
@@ -136,6 +139,12 @@
// Types
try {
+ session.executeNonSelectingSQL("DROP TYPE PLSQL_P_PLSQL_INNER_BLOB_REC FORCE");
+ } catch (Exception ignore) {}
+ try {
+ session.executeNonSelectingSQL("DROP TYPE PLSQL_P_PLSQL_OUTER_STRUCT_REC FORCE");
+ } catch (Exception ignore) {}
+ try {
session.executeNonSelectingSQL("DROP TYPE PLSQL_P_PLSQL_EMP_REC FORCE");
} catch (Exception ignore) {}
try {
@@ -151,6 +160,10 @@
session.executeNonSelectingSQL("CREATE OR REPLACE TYPE PLSQL_P_PLSQL_PHONE_LIST AS VARRAY(30) OF PLSQL_P_PLSQL_PHONE_REC");
session.executeNonSelectingSQL("CREATE OR REPLACE TYPE PLSQL_P_PLSQL_EMP_REC AS OBJECT ("
+ "EMP_ID NUMBER(10), NAME VARCHAR2(30), ACTIVE NUMBER(1), ADDRESS PLSQL_P_PLSQL_ADDRESS_REC, PHONES PLSQL_P_PLSQL_PHONE_LIST)");
+ session.executeNonSelectingSQL("CREATE OR REPLACE TYPE PLSQL_P_PLSQL_INNER_BLOB_REC AS OBJECT ("
+ + "BLOB_ID NUMBER(10), BLOB_CONTENT BLOB, CLOB_CONTENT CLOB)");
+ session.executeNonSelectingSQL("CREATE OR REPLACE TYPE PLSQL_P_PLSQL_OUTER_STRUCT_REC AS OBJECT ("
+ + "STRUCT_ID NUMBER(10), STRUCT_CONTENT PLSQL_P_PLSQL_INNER_BLOB_REC)");
session.executeNonSelectingSQL("CREATE OR REPLACE TYPE PLSQL_P_PLSQL_CITY_LIST AS VARRAY(255) OF VARCHAR2(100)");
session.executeNonSelectingSQL("CREATE OR REPLACE TYPE PLSQL_P_PLSQL_ADDRESS_LIST AS VARRAY(255) OF PLSQL_P_PLSQL_ADDRESS_REC");
session.executeNonSelectingSQL("CREATE OR REPLACE TYPE PLSQL_P_PLSQL_EMP_LIST AS VARRAY(255) OF PLSQL_P_PLSQL_EMP_REC");
@@ -178,6 +191,8 @@
+ "PROCEDURE PLSQL_EMP_INOUT(P_EMP IN OUT PLSQL_EMP_REC, P_CITY IN OUT VARCHAR2); \n"
+ "PROCEDURE PLSQL_ADDRESS_CUR_OUT(P_ADDRESS OUT PLSQL_ADDRESS_CUR); \n"
+ "PROCEDURE PLSQL_ADDRESS_REC_CUR_OUT(P_ADDRESS OUT PLSQL_ADDRESS_REC_CUR); \n"
+ + "FUNCTION PLSQL_BLOB_REC_IN(P_BLOB_REC IN PLSQL_P_PLSQL_INNER_BLOB_REC, P_CITY IN VARCHAR2) RETURN VARCHAR2; \n"
+ + "FUNCTION PLSQL_STRUCT_INSTRUCT_REC_IN(P_BLOB_REC IN PLSQL_P_PLSQL_INNER_BLOB_REC, P_STRUCT_REC IN PLSQL_P_PLSQL_OUTER_STRUCT_REC, P_CITY IN VARCHAR2) RETURN VARCHAR2; \n"
+ "END PLSQL_P; \n");
session.executeNonSelectingSQL("CREATE OR REPLACE PACKAGE BODY PLSQL_P AS \n"
+ "PROCEDURE PLSQL_CITY_LIST_IN(P_CITY_LIST IN PLSQL_CITY_LIST, P_CITY IN VARCHAR2) AS \n"
@@ -238,7 +253,16 @@
+ "BEGIN \n"
+ "OPEN P_ADDRESS FOR SELECT * FROM PLSQL_ADDRESS; \n"
+ "END PLSQL_ADDRESS_REC_CUR_OUT; \n"
- + "END PLSQL_P; \n");
+ + "FUNCTION PLSQL_BLOB_REC_IN(P_BLOB_REC IN PLSQL_P_PLSQL_INNER_BLOB_REC, P_CITY IN VARCHAR2) RETURN VARCHAR2 IS \n"
+ + "BEGIN \n"
+ + "RETURN P_CITY || TO_CHAR(P_BLOB_REC.BLOB_ID) || TO_CHAR(DBMS_LOB.GETLENGTH(P_BLOB_REC.BLOB_CONTENT)) || TO_CHAR(P_BLOB_REC.CLOB_CONTENT); \n"
+ + "END PLSQL_BLOB_REC_IN; \n"
+ + "FUNCTION PLSQL_STRUCT_INSTRUCT_REC_IN(P_BLOB_REC IN PLSQL_P_PLSQL_INNER_BLOB_REC, P_STRUCT_REC IN PLSQL_P_PLSQL_OUTER_STRUCT_REC, P_CITY IN VARCHAR2) RETURN VARCHAR2 IS \n"
+ + "BEGIN \n"
+ + "RETURN P_CITY || TO_CHAR(P_BLOB_REC.BLOB_ID) || TO_CHAR(DBMS_LOB.GETLENGTH(P_BLOB_REC.BLOB_CONTENT)) || TO_CHAR(P_STRUCT_REC.STRUCT_ID) || TO_CHAR(DBMS_LOB.GETLENGTH(P_STRUCT_REC.STRUCT_CONTENT.BLOB_CONTENT)) || TO_CHAR(P_BLOB_REC.CLOB_CONTENT); \n"
+ + "END PLSQL_STRUCT_INSTRUCT_REC_IN; \n"
+ + "END PLSQL_P; \n"
+ );
session.executeNonSelectingSQL("CREATE TABLE PLSQL_CONSULTANT ("
+ "EMP_ID NUMBER(10), NAME VARCHAR2(30), ACTIVE NUMBER(1), ADDRESS PLSQL_P_PLSQL_ADDRESS_REC, PHONES PLSQL_P_PLSQL_PHONE_LIST, PRIMARY KEY (EMP_ID))");
}
@@ -466,4 +490,53 @@
closeEntityManagerAndTransaction(em);
}
}
+
+ /**
+ * Test Blob data-type in Struct (SQL Object type) which is passed/used as stored procedure/function parameter.
+ */
+ public void testBlobInStruct() {
+ if (!getServerSession().getPlatform().isOracle()) {
+ return;
+ }
+ EntityManager em = createEntityManager();
+ beginTransaction(em);
+ try {
+ Query query = em.createNamedQuery("PLSQL_BLOB_REC_IN");
+ InnerObjBlob innerObjBlob = new InnerObjBlob(1, Base64.getDecoder().decode("AQI="), "abcd");
+ query.setParameter("P_CITY", "Prague");
+ query.setParameter("P_BLOB_REC", innerObjBlob);
+ Object result = query.getSingleResult();
+ if (!"Prague12abcd".equals(result.toString())) {
+ fail("Incorrect result.");
+ }
+ } finally {
+ closeEntityManagerAndTransaction(em);
+ }
+ }
+
+
+ /**
+ * Test Struct (SQL Object type) with Blob data-type in Struct (SQL Object type) which is passed/used as stored procedure/function parameter.
+ */
+ public void testStructInStruct() {
+ if (!getServerSession().getPlatform().isOracle()) {
+ return;
+ }
+ EntityManager em = createEntityManager();
+ beginTransaction(em);
+ try {
+ Query query = em.createNamedQuery("PLSQL_STRUCT_INSTRUCT_REC_IN");
+ InnerObjBlob innerObjBlob = new InnerObjBlob(1, Base64.getDecoder().decode("AQI="), "abcd");
+ OuterObjBlob outerObjBlob = new OuterObjBlob(33, innerObjBlob);
+ query.setParameter("P_CITY", "Prague");
+ query.setParameter("P_BLOB_REC", innerObjBlob);
+ query.setParameter("P_STRUCT_REC", outerObjBlob);
+ Object result = query.getSingleResult();
+ if (!"Prague1233abcd".equals(result.toString())) {
+ fail("Incorrect result.");
+ }
+ } finally {
+ closeEntityManagerAndTransaction(em);
+ }
+ }
}
