/******************************************************************************* | |
* Copyright (c) 2011, 2013 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 v1.0 and Eclipse Distribution License v. 1.0 | |
* which accompanies this distribution. | |
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html | |
* and the Eclipse Distribution License is available at | |
* http://www.eclipse.org/org/documents/edl-v10.php. | |
* | |
* Contributors: | |
* David McCann - September 08, 2011 - 2.4 - Initial implementation | |
******************************************************************************/ | |
package dbws.testing.plsqlrecord; | |
//javase imports | |
import java.io.StringReader; | |
import java.sql.SQLException; | |
import java.sql.Statement; | |
import java.util.ArrayList; | |
import java.util.List; | |
import org.w3c.dom.Document; | |
//java eXtension imports | |
import javax.wsdl.WSDLException; | |
//JUnit4 imports | |
import org.junit.AfterClass; | |
import org.junit.BeforeClass; | |
import org.junit.Test; | |
import static org.junit.Assert.assertNotNull; | |
import static org.junit.Assert.assertTrue; | |
//EclipseLink imports | |
import org.eclipse.persistence.internal.xr.Invocation; | |
import org.eclipse.persistence.internal.xr.Operation; | |
import org.eclipse.persistence.oxm.XMLMarshaller; | |
import org.eclipse.persistence.oxm.XMLUnmarshaller; | |
//test imports | |
import dbws.testing.DBWSTestSuite; | |
/** | |
* Tests PL/SQL procedures with simple and complex arguments. | |
* | |
*/ | |
public class PLSQLRecordTestSuite extends DBWSTestSuite { | |
static final String MTAB1_TYPE = "TYPE PACKAGE1_MTAB1"; | |
static final String NRECORD_TYPE = "TYPE PACKAGE1_NRECORD"; | |
static final String MRECORD_TYPE = "TYPE PACKAGE1_MRECORD"; | |
static final String EMPREC_TYPE = "TYPE EMP_RECORD_PACKAGE_EMPREC"; | |
static final String CREATE_EMPTYPE_TABLE = | |
"CREATE TABLE EMPTYPEX (" + | |
"\nEMPNO NUMERIC(4) NOT NULL," + | |
"\nENAME VARCHAR(25)," + | |
"\nPRIMARY KEY (EMPNO)" + | |
"\n)"; | |
static final String[] POPULATE_EMPTYPE_TABLE = new String[] { | |
"INSERT INTO EMPTYPEX (EMPNO, ENAME) VALUES (69, 'Holly')", | |
"INSERT INTO EMPTYPEX (EMPNO, ENAME) VALUES (70, 'Brooke')", | |
"INSERT INTO EMPTYPEX (EMPNO, ENAME) VALUES (71, 'Patty')" | |
}; | |
static final String DROP_EMPTYPE_TABLE = | |
"DROP TABLE EMPTYPEX"; | |
static final String CREATE_EMP_RECORD_PACKAGE = | |
"create or replace PACKAGE EMP_RECORD_PACKAGE AS\n" + | |
"type EmpRec is record (" + | |
"emp_id EMPTYPEX.EMPNO%TYPE,\n" + | |
"emp_name EMPTYPEX.ENAME%TYPE\n" + | |
");\n" + | |
"function get_emp_record (pId in number) return EmpRec;\n" + | |
"END EMP_RECORD_PACKAGE;"; | |
static final String DROP_EMP_RECORD_PACKAGE = | |
"DROP PACKAGE EMP_RECORD_PACKAGE"; | |
static final String CREATE_EMP_RECORD_PACKAGE_BODY = | |
"create or replace PACKAGE BODY EMP_RECORD_PACKAGE AS\n" + | |
"function get_emp_record (pId in number) return EmpRec AS\n" + | |
"myEmp EmpRec;\n" + | |
"l_empno EMPTYPEX.EMPNO%TYPE;\n" + | |
"l_ename EMPTYPEX.ENAME%TYPE;\n" + | |
"cursor c_emp is select empno, ename from EMPTYPEX where empno = pId;\n" + | |
"BEGIN\n" + | |
"open c_emp;\n" + | |
"fetch c_emp into l_empno, l_ename;\n" + | |
"close c_emp;\n" + | |
"myEmp.emp_id := l_empno;\n" + | |
"myEmp.emp_name := l_ename;\n" + | |
"return myEmp;\n" + | |
"END get_emp_record;\n" + | |
"END EMP_RECORD_PACKAGE;"; | |
static final String DROP_EMP_RECORD_PACKAGE_BODY = | |
"DROP PACKAGE BODY EMP_RECORD_PACKAGE"; | |
static final String CREATE_PACKAGE1_PACKAGE = | |
"CREATE OR REPLACE PACKAGE PACKAGE1 AS" + | |
"\nTYPE MTAB1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;" + | |
"\nTYPE NRECORD IS RECORD (" + | |
"\nN1 VARCHAR2(10)," + | |
"\nN2 DECIMAL(7,2)" + | |
"\n);" + | |
"\nTYPE MRECORD IS RECORD (" + | |
"\nM1 MTAB1" + | |
"\n);" + | |
"\nPROCEDURE GETNEWREC(NEWREC OUT NRECORD);" + | |
"\nPROCEDURE COPYREC(ORIGINALREC IN NRECORD, NEWREC OUT NRECORD, SUFFIX IN VARCHAR2);" + | |
"\nPROCEDURE GETRECWITHTABLE(ORIGINALTAB IN MTAB1, NEWREC OUT MRECORD);" + | |
"\nFUNCTION COPYREC2(ORIGINALREC IN NRECORD, SUFFIX IN VARCHAR2) RETURN NRECORD;" + | |
"\nFUNCTION GETRECWITHTABLE2(ORIGINALTAB IN MTAB1) RETURN MRECORD;" + | |
"\nEND PACKAGE1;"; | |
static final String CREATE_PACKAGE1_BODY = | |
"CREATE OR REPLACE PACKAGE BODY PACKAGE1 AS" + | |
"\nPROCEDURE GETNEWREC(NEWREC OUT NRECORD) AS" + | |
"\nBEGIN" + | |
"\nNEWREC.N1 := 'new record';" + | |
"\nNEWREC.N2 := 100.11;" + | |
"\nEND GETNEWREC;" + | |
"\nPROCEDURE COPYREC(ORIGINALREC IN NRECORD, NEWREC OUT NRECORD, SUFFIX IN VARCHAR2) AS" + | |
"\nBEGIN" + | |
"\nNEWREC.N1 := CONCAT(ORIGINALREC.N1, SUFFIX);" + | |
"\nNEWREC.N2 := ORIGINALREC.N2 + 0.1;" + | |
"\nEND COPYREC;" + | |
"\nPROCEDURE GETRECWITHTABLE(ORIGINALTAB IN MTAB1, NEWREC OUT MRECORD) AS" + | |
"\nBEGIN" + | |
"\nNEWREC.M1 := ORIGINALTAB;" + | |
"\nEND GETRECWITHTABLE;" + | |
"\nFUNCTION COPYREC2(ORIGINALREC IN NRECORD, SUFFIX IN VARCHAR2) RETURN NRECORD IS" + | |
"\nnewrec NRECORD;" + | |
"\nBEGIN" + | |
"\nnewrec.N1 := CONCAT(ORIGINALREC.N1, SUFFIX);" + | |
"\nnewrec.N2 := ORIGINALREC.N2 + 0.1;" + | |
"\nRETURN newrec;" + | |
"\nEND COPYREC2;" + | |
"\nFUNCTION GETRECWITHTABLE2(ORIGINALTAB IN MTAB1) RETURN MRECORD IS" + | |
"\nNEWREC MRECORD;" + | |
"\nBEGIN" + | |
"\nNEWREC.M1 := ORIGINALTAB;" + | |
"\nRETURN NEWREC;" + | |
"\nEND GETRECWITHTABLE2;" + | |
"\nEND PACKAGE1;"; | |
static final String DROP_PACKAGE1_PACKAGE = | |
"DROP PACKAGE PACKAGE1"; | |
static final String DROP_PACKAGE1_PACKAGE_BODY = | |
"DROP PACKAGE BODY PACKAGE1"; | |
static boolean ddlCreate = false; | |
static boolean ddlDrop = false; | |
static boolean ddlDebug = false; | |
@BeforeClass | |
public static void setUp() throws WSDLException { | |
if (conn == null) { | |
try { | |
conn = buildConnection(); | |
} | |
catch (Exception e) { | |
e.printStackTrace(); | |
} | |
} | |
String ddlCreateProp = System.getProperty(DATABASE_DDL_CREATE_KEY, DEFAULT_DATABASE_DDL_CREATE); | |
if ("true".equalsIgnoreCase(ddlCreateProp)) { | |
ddlCreate = true; | |
} | |
String ddlDropProp = System.getProperty(DATABASE_DDL_DROP_KEY, DEFAULT_DATABASE_DDL_DROP); | |
if ("true".equalsIgnoreCase(ddlDropProp)) { | |
ddlDrop = true; | |
} | |
String ddlDebugProp = System.getProperty(DATABASE_DDL_DEBUG_KEY, DEFAULT_DATABASE_DDL_DEBUG); | |
if ("true".equalsIgnoreCase(ddlDebugProp)) { | |
ddlDebug = true; | |
} | |
if (ddlCreate) { | |
runDdl(conn, CREATE_PACKAGE1_PACKAGE, ddlDebug); | |
runDdl(conn, CREATE_PACKAGE1_BODY, ddlDebug); | |
runDdl(conn, CREATE_EMPTYPE_TABLE, ddlDebug); | |
try { | |
Statement stmt = conn.createStatement(); | |
for (int i = 0; i < POPULATE_EMPTYPE_TABLE.length; i++) { | |
stmt.addBatch(POPULATE_EMPTYPE_TABLE[i]); | |
} | |
stmt.executeBatch(); | |
} catch (SQLException e) { | |
if (ddlDebug) { | |
e.printStackTrace(); | |
} | |
} | |
runDdl(conn, CREATE_EMP_RECORD_PACKAGE, ddlDebug); | |
runDdl(conn, CREATE_EMP_RECORD_PACKAGE_BODY, ddlDebug); | |
} | |
DBWS_BUILDER_XML_USERNAME = | |
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + | |
"<dbws-builder xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + | |
"<properties>" + | |
"<property name=\"projectName\">PLSQLRecord</property>" + | |
"<property name=\"logLevel\">off</property>" + | |
"<property name=\"username\">"; | |
DBWS_BUILDER_XML_PASSWORD = | |
"</property><property name=\"password\">"; | |
DBWS_BUILDER_XML_URL = | |
"</property><property name=\"url\">"; | |
DBWS_BUILDER_XML_DRIVER = | |
"</property><property name=\"driver\">"; | |
DBWS_BUILDER_XML_PLATFORM = | |
"</property><property name=\"platformClassname\">"; | |
DBWS_BUILDER_XML_MAIN = | |
"</property>" + | |
"</properties>" + | |
"<plsql-procedure " + | |
"name=\"GetNewRecordTest\" " + | |
"catalogPattern=\"PACKAGE1\" " + | |
"procedurePattern=\"GETNEWREC\" " + | |
"/>" + | |
"<plsql-procedure " + | |
"name=\"CopyRecordTest\" " + | |
"catalogPattern=\"PACKAGE1\" " + | |
"procedurePattern=\"COPYREC\" " + | |
"/>" + | |
"<plsql-procedure " + | |
"name=\"CopyRecordTest2\" " + | |
"catalogPattern=\"PACKAGE1\" " + | |
"procedurePattern=\"COPYREC2\" " + | |
"/>" + | |
"<plsql-procedure " + | |
"name=\"GetRecordWithTableTest\" " + | |
"catalogPattern=\"PACKAGE1\" " + | |
"procedurePattern=\"GETRECWITHTABLE\" " + | |
"/>" + | |
"<plsql-procedure " + | |
"name=\"GetRecordWithTableTest2\" " + | |
"catalogPattern=\"PACKAGE1\" " + | |
"procedurePattern=\"GETRECWITHTABLE2\" " + | |
"/>" + | |
"<plsql-procedure " + | |
"name=\"TestRecWithPercentTypeField\" " + | |
"catalogPattern=\"EMP_RECORD_PACKAGE\" " + | |
"procedurePattern=\"get_emp_record\" " + | |
"/>" + | |
"</dbws-builder>"; | |
builder = null; | |
DBWSTestSuite.setUp("."); | |
// execute shadow type ddl to generate JDBC equivalents of PL/SQL types | |
ArrayList<String> ddls = new ArrayList<String>(); | |
for (String ddl : builder.getTypeDDL()) { | |
ddls.add(ddl); | |
} | |
// execute the DDLs in order to avoid dependency issues | |
for (int j = 0; j < 4; j++) { | |
switch (j) { | |
case 0: | |
executeDDLForString(ddls, MTAB1_TYPE); | |
break; | |
case 1: | |
executeDDLForString(ddls, NRECORD_TYPE); | |
break; | |
case 2: | |
executeDDLForString(ddls, MRECORD_TYPE); | |
break; | |
default: | |
executeDDLForString(ddls, EMPREC_TYPE); | |
break; | |
} | |
} | |
} | |
/** | |
* Execute the DDL in the provided list containing the given DDL string. | |
* | |
*/ | |
protected static void executeDDLForString(List<String> ddls, String ddlString) { | |
for (int i = 0; i < ddls.size(); i++) { | |
String ddl = ddls.get(i); | |
if (ddl.contains(ddlString)) { | |
runDdl(conn, ddl, ddlDebug); | |
break; | |
} | |
} | |
} | |
@AfterClass | |
public static void tearDown() { | |
if (ddlDrop) { | |
runDdl(conn, DROP_PACKAGE1_PACKAGE_BODY, ddlDebug); | |
runDdl(conn, DROP_PACKAGE1_PACKAGE, ddlDebug); | |
runDdl(conn, DROP_EMP_RECORD_PACKAGE_BODY, ddlDebug); | |
runDdl(conn, DROP_EMP_RECORD_PACKAGE, ddlDebug); | |
runDdl(conn, DROP_EMPTYPE_TABLE, ddlDebug); | |
// drop shadow type ddl | |
for (String ddl : builder.getTypeDropDDL()) { | |
// may need to strip off trailing ';' | |
try { | |
int lastIdx = ddl.lastIndexOf(";"); | |
if (lastIdx == (ddl.length() - 1)) { | |
ddl = ddl.substring(0, ddl.length() - 1); | |
} | |
} catch (Exception xxx) {} | |
runDdl(conn, ddl, ddlDebug); | |
} | |
} | |
} | |
@Test | |
public void getNewRecordTest() { | |
Invocation invocation = new Invocation("GetNewRecordTest"); | |
Operation op = xrService.getOperation(invocation.getName()); | |
Object result = op.invoke(xrService, invocation); | |
assertNotNull("result is null", result); | |
Document doc = xmlPlatform.createDocument(); | |
XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller(); | |
marshaller.marshal(result, doc); | |
Document controlDoc = xmlParser.parse(new StringReader(RECORD_XML)); | |
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc)); | |
} | |
public static final String RECORD_XML = | |
STANDALONE_XML_HEADER + | |
"<package1_nrecordType xmlns=\"urn:PLSQLRecord\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" + | |
"<n1>new record</n1>" + | |
"<n2>100.11</n2>" + | |
"</package1_nrecordType>"; | |
/** | |
* StoredProcedure test. | |
* Copies n1 to new record.n1 appending '.copy'. | |
* Copies n2 to new record.n2 adding 0.1 to the amount. | |
*/ | |
@Test | |
public void copyRecordTest() { | |
XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller(); | |
Object inputRec = unmarshaller.unmarshal(new StringReader(INPUTRECORD_XML)); | |
Invocation invocation = new Invocation("CopyRecordTest"); | |
invocation.setParameter("ORIGINALREC", inputRec); | |
invocation.setParameter("SUFFIX", ".copy"); | |
Operation op = xrService.getOperation(invocation.getName()); | |
Object result = op.invoke(xrService, invocation); | |
assertNotNull("result is null", result); | |
Document doc = xmlPlatform.createDocument(); | |
XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller(); | |
marshaller.marshal(result, doc); | |
Document controlDoc = xmlParser.parse(new StringReader(OUTPUTRECORD_XML)); | |
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc)); | |
} | |
public static final String INPUTRECORD_XML = | |
STANDALONE_XML_HEADER + | |
"<package1_nrecordType xmlns=\"urn:PLSQLRecord\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" + | |
"<n1>data</n1>" + | |
"<n2>100.00</n2>" + | |
"</package1_nrecordType>"; | |
public static final String OUTPUTRECORD_XML = | |
STANDALONE_XML_HEADER + | |
"<package1_nrecordType xmlns=\"urn:PLSQLRecord\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" + | |
"<n1>data.copy</n1>" + | |
"<n2>100.1</n2>" + | |
"</package1_nrecordType>"; | |
/** | |
* StoredFunction test. | |
* Copies n1 to new record.n1 appending '.copy'. | |
* Copies n2 to new record.n2 adding 0.1 to the amount. | |
*/ | |
@Test | |
public void copyRecordTest2() { | |
XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller(); | |
Object inputRec = unmarshaller.unmarshal(new StringReader(INPUTRECORD_XML)); | |
Invocation invocation = new Invocation("CopyRecordTest2"); | |
invocation.setParameter("ORIGINALREC", inputRec); | |
invocation.setParameter("SUFFIX", ".copy"); | |
Operation op = xrService.getOperation(invocation.getName()); | |
Object result = op.invoke(xrService, invocation); | |
assertNotNull("result is null", result); | |
Document doc = xmlPlatform.createDocument(); | |
XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller(); | |
marshaller.marshal(result, doc); | |
Document controlDoc = xmlParser.parse(new StringReader(OUTPUTRECORD_XML)); | |
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc)); | |
} | |
/** | |
* StoredProcedure test. | |
*/ | |
@Test | |
public void getRecordWithTableTest() { | |
XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller(); | |
Object inputTable = unmarshaller.unmarshal(new StringReader(TABLE_XML)); | |
Invocation invocation = new Invocation("GetRecordWithTableTest"); | |
invocation.setParameter("ORIGINALTAB", inputTable); | |
Operation op = xrService.getOperation(invocation.getName()); | |
Object result = op.invoke(xrService, invocation); | |
assertNotNull("result is null", result); | |
Document doc = xmlPlatform.createDocument(); | |
XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller(); | |
marshaller.marshal(result, doc); | |
Document controlDoc = xmlParser.parse(new StringReader(OUTPUTRECORDWITHTABLE_XML)); | |
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc)); | |
} | |
public static final String TABLE_XML = | |
STANDALONE_XML_HEADER + | |
"<package1_mtab1Type xmlns=\"urn:PLSQLRecord\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" + | |
"<item>666</item>" + | |
"</package1_mtab1Type>"; | |
public static final String OUTPUTRECORDWITHTABLE_XML = | |
STANDALONE_XML_HEADER + | |
"<package1_mrecordType xmlns=\"urn:PLSQLRecord\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" + | |
"<m1>" + | |
"<item>666</item>" + | |
"</m1>" + | |
"</package1_mrecordType>"; | |
/** | |
* StoredFunction test. | |
*/ | |
@Test | |
public void getRecordWithTableTest2() { | |
XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller(); | |
Object inputTable = unmarshaller.unmarshal(new StringReader(TABLE_XML)); | |
Invocation invocation = new Invocation("GetRecordWithTableTest2"); | |
invocation.setParameter("ORIGINALTAB", inputTable); | |
Operation op = xrService.getOperation(invocation.getName()); | |
Object result = op.invoke(xrService, invocation); | |
assertNotNull("result is null", result); | |
Document doc = xmlPlatform.createDocument(); | |
XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller(); | |
marshaller.marshal(result, doc); | |
Document controlDoc = xmlParser.parse(new StringReader(OUTPUTRECORDWITHTABLE_XML)); | |
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc)); | |
} | |
@Test | |
public void testRecordWithPercentTypeField() { | |
Invocation invocation = new Invocation("TestRecWithPercentTypeField"); | |
invocation.setParameter("pId", 69); | |
Operation op = xrService.getOperation(invocation.getName()); | |
Object result = op.invoke(xrService, invocation); | |
assertNotNull("result is null", result); | |
Document doc = xmlPlatform.createDocument(); | |
XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller(); | |
marshaller.marshal(result, doc); | |
Document controlDoc = xmlParser.parse(new StringReader(EMPREC_XML)); | |
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc)); | |
} | |
public static final String EMPREC_XML = | |
"<emp_record_package_emprecType xmlns=\"urn:PLSQLRecord\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" + | |
"<emp_id>69</emp_id>" + | |
"<emp_name>Holly</emp_name>" + | |
"</emp_record_package_emprecType>"; | |
} |