API tools faq
paste
Guest User

Untitled

a guest
May 2nd, 2018
107
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.30 KB | None | 0 0
raw download clone embed print report
  1. A StoredProcedure in a PL/SQL package uses a PL/SQL record type for arguments:
  2.  
  3. CREATE OR REPLACE PACKAGE NEST_REC_PACKAGE AS
  4. TYPE TEST_NEST_REC IS RECORD (
  5. VALUE01 VARCHAR2(10),
  6. VALUE02 NUMBER
  7. );
  8. TYPE TEST_REC IS RECORD (
  9. COLUMN01 VARCHAR2(10),
  10. COLUMN02 NUMBER,
  11. NEST01 TEST_NEST_REC
  12. );
  13. PROCEDURE PROCEDURE_RECORD_CALL_NEST(
  14. INPUT IN TEST_REC,
  15. OUTPUT OUT TEST_REC
  16. );
  17. END NEST_REC_PACKAGE;
  18.  
  19. First, we need some model classes for the PL/SQL record types - very simple:
  20. {for demo purposes, I put everything in the package 'test' but that can be changed easily ...}
  21.  
  22. package test;
  23.  
  24. import java.math.BigDecimal;
  25.  
  26. public class NestedRecord {
  27.  
  28. public String value01;
  29. public BigDecimal value02;
  30.  
  31. public NestedRecord() {
  32. }
  33. }
  34. ...
  35. public class Record {
  36.  
  37. public String column01;
  38. public BigDecimal column02;
  39. public NestedRecord nest01;
  40.  
  41. public Record() {
  42. }
  43. }
  44.  
  45. A simple test class shows mappings for each type as well as how to build a query to execute the stored procedure.
  46.  
  47. Some of the key points:
  48.  
  49. - complex types that are defined in a PL/SQL package (records, collections or any combination thereof)
  50. require 'shadow' types that are defined in the 'global' JDBC namespace outside of any PL/SQL package.
  51. Thus for the record "TEST_REC", we need the shadow type "NEST_REC_PACKAGE_TEST_REC". The
  52. shadow type must have the same 'shape' - same number of fields, in the same order, with the same names.
  53. (Note the simple transform from the PL/SQL world to the JDBC world: package name + "_" + record name).
  54.  
  55. - in the case of the stored procedure "PROCEDURE_RECORD_CALL_NEST", the record record "TEST_REC" is used
  56. twice, once for the IN parameter "INPUT" and second for the OUT parameter "OUTPUT". However, EclipseLink
  57. needs separate instances of the PLSQLrecord object
  58.  
  59. {other notes: all Oracle database artifacts - type names, package names, etc. should always be represented in
  60. EclipseLink metadata in UPPERCASE}
  61.  
  62. ...
  63. //javase imports
  64. import java.math.BigDecimal;
  65. import java.util.ArrayList;
  66. import java.util.List;
  67.  
  68. //EclipseLink imports
  69. import org.eclipse.persistence.logging.SessionLog;
  70. import org.eclipse.persistence.mappings.DirectToFieldMapping;
  71. import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;
  72. import org.eclipse.persistence.mappings.structures.StructureMapping;
  73. import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
  74. import org.eclipse.persistence.platform.database.oracle.Oracle11Platform;
  75. import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
  76. import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;
  77. import org.eclipse.persistence.queries.ValueReadQuery;
  78. import org.eclipse.persistence.sessions.DatabaseLogin;
  79. import org.eclipse.persistence.sessions.DatabaseSession;
  80. import org.eclipse.persistence.sessions.DatasourceLogin;
  81. import org.eclipse.persistence.sessions.Project;
  82.  
  83. public class NestedRecordTest {
  84.  
  85. static String username;
  86. static String password;
  87. static String url;
  88. static final String DATABASE_USERNAME_KEY = "db.user";
  89. static final String DATABASE_PASSWORD_KEY = "db.pwd";
  90. static final String DATABASE_URL_KEY = "db.url";
  91. static final String DEFAULT_DATABASE_USERNAME = "scott";
  92. static final String DEFAULT_DATABASE_PASSWORD = "tiger";
  93. static final String DEFAULT_DATABASE_DRIVER = "oracle.jdbc.OracleDriver";
  94. static final String DEFAULT_DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
  95. static final String PACKAGE_NAME = "NEST_REC_PACKAGE";
  96. static final String PROCEDURE_NAME = "PROCEDURE_RECORD_CALL_NEST";
  97. static final String NEST_RECORD_TYPE_NAME = "TEST_NEST_REC";
  98. static final String RECORD_TYPE_NAME = "TEST_REC";
  99. static final String CREATE_PACKAGE =
  100. "CREATE OR REPLACE PACKAGE " + PACKAGE_NAME + " AS\n" +
  101. "TYPE " + NEST_RECORD_TYPE_NAME + " IS RECORD (\n" +
  102. "VALUE01 VARCHAR2(10),\n" +
  103. "VALUE02 NUMBER\n" +
  104. ");\n" +
  105. "TYPE " + RECORD_TYPE_NAME + " IS RECORD (\n" +
  106. "COLUMN01 VARCHAR2(10),\n" +
  107. "COLUMN02 NUMBER,\n" +
  108. "NEST01 " + NEST_RECORD_TYPE_NAME + " \n" +
  109. ");\n" +
  110. "PROCEDURE " + PROCEDURE_NAME + "(\n" +
  111. "INPUT IN " + RECORD_TYPE_NAME + ",\n" +
  112. "OUTPUT OUT " + RECORD_TYPE_NAME + "\n" +
  113. ");\n" +
  114. "END " + PACKAGE_NAME + ";";
  115. static final String CREATE_BODY =
  116. "CREATE OR REPLACE PACKAGE BODY " + PACKAGE_NAME + " AS\n" +
  117. "PROCEDURE " + PROCEDURE_NAME + "(\n" +
  118. "INPUT IN " + RECORD_TYPE_NAME + ",\n" +
  119. "OUTPUT OUT " + RECORD_TYPE_NAME + ") AS\n" +
  120. "BEGIN\n" +
  121. "NULL;\n" +
  122. "END " + PROCEDURE_NAME + ";\n" +
  123. "END " + PACKAGE_NAME + ";";
  124. static final String JDBC_SHADOW_TYPE1 =
  125. "NEST_REC_PACKAGE_TEST_NEST_REC";
  126. static final String JDBC_SHADOW_TYPE2 =
  127. "NEST_REC_PACKAGE_TEST_REC";
  128. static final String CREATE_JDBC_SHADOW_TYPE1 =
  129. "CREATE OR REPLACE TYPE " + JDBC_SHADOW_TYPE1 + " AS OBJECT (\n" +
  130. "VALUE01 VARCHAR2(10),\n" +
  131. "VALUE02 NUMBER\n" +
  132. ")\n";
  133. static final String CREATE_JDBC_SHADOW_TYPE2 =
  134. "CREATE OR REPLACE TYPE " + JDBC_SHADOW_TYPE2 + " AS OBJECT (\n" +
  135. "COLUMN01 VARCHAR2(10),\n" +
  136. "COLUMN02 NUMBER,\n" +
  137. "NEST01 " + JDBC_SHADOW_TYPE1 + " \n" +
  138. ")\n";
  139. static final String DROP_PACKAGE =
  140. "DROP PACKAGE NEST_REC_PACKAGE";
  141. static final String DROP_JDBC_SHADOW_TYPE1 =
  142. "DROP TYPE " + JDBC_SHADOW_TYPE1;
  143. static final String DROP_JDBC_SHADOW_TYPE2 =
  144. "DROP TYPE " + JDBC_SHADOW_TYPE2;
  145.  
  146. static DatabaseSession ds = null;
  147.  
  148. public static void main(String...args) {
  149. username = System.getProperty(DATABASE_USERNAME_KEY, DEFAULT_DATABASE_USERNAME);
  150. password = System.getProperty(DATABASE_PASSWORD_KEY, DEFAULT_DATABASE_PASSWORD);
  151. url = System.getProperty(DATABASE_URL_KEY, DEFAULT_DATABASE_URL);
  152.  
  153. DatasourceLogin login = new DatabaseLogin();
  154. login.setUserName(username);
  155. login.setPassword(password);
  156. ((DatabaseLogin)login).setConnectionString(url);
  157. ((DatabaseLogin)login).setDriverClassName(DEFAULT_DATABASE_DRIVER);
  158. login.setDatasourcePlatform(new Oracle11Platform());
  159. ((DatabaseLogin)login).bindAllParameters();
  160.  
  161. Project p = new Project(login);
  162. ObjectRelationalDataTypeDescriptor recordDescriptor = new ObjectRelationalDataTypeDescriptor();
  163. recordDescriptor.descriptorIsAggregate();
  164. recordDescriptor.setJavaClass(test.Record.class);
  165. recordDescriptor.setAlias("Record");
  166. recordDescriptor.setStructureName(JDBC_SHADOW_TYPE2);
  167. DirectToFieldMapping column01Mapping = new DirectToFieldMapping();
  168. column01Mapping.setAttributeName("column01");
  169. column01Mapping.setFieldName("COLUMN01");
  170. recordDescriptor.addMapping(column01Mapping);
  171. DirectToFieldMapping column02Mapping = new DirectToFieldMapping();
  172. column02Mapping.setAttributeName("column02");
  173. column02Mapping.setFieldName("COLUMN02");
  174. recordDescriptor.addMapping(column02Mapping);
  175. StructureMapping nest01Mapping = new StructureMapping();
  176. nest01Mapping.setAttributeName("nest01");
  177. nest01Mapping.setFieldName("NEST01");
  178. nest01Mapping.setReferenceClass(test.NestedRecord.class);
  179. recordDescriptor.addMapping(nest01Mapping);
  180. p.addDescriptor(recordDescriptor);
  181.  
  182. ObjectRelationalDataTypeDescriptor nestedRecordDesc = new ObjectRelationalDataTypeDescriptor();
  183. nestedRecordDesc.descriptorIsAggregate();
  184. nestedRecordDesc.setJavaClass(test.NestedRecord.class);
  185. nestedRecordDesc.setAlias("NestedRecord");
  186. nestedRecordDesc.setStructureName(JDBC_SHADOW_TYPE1);
  187. DirectToFieldMapping value01Mapping = new DirectToFieldMapping();
  188. value01Mapping.setAttributeName("value01");
  189. value01Mapping.setFieldName("VALUE01");
  190. nestedRecordDesc.addMapping(value01Mapping);
  191. DirectToFieldMapping value02Mapping = new DirectToFieldMapping();
  192. value02Mapping.setAttributeName("value02");
  193. value02Mapping.setFieldName("VALUE02");
  194. nestedRecordDesc.addMapping(value02Mapping);
  195. p.addDescriptor(nestedRecordDesc);
  196.  
  197. ds = p.createDatabaseSession();
  198. ds.setLogLevel(SessionLog.FINE);
  199. ds.login();
  200. try {
  201. ds.executeNonSelectingSQL(CREATE_PACKAGE);
  202. ds.executeNonSelectingSQL(CREATE_BODY);
  203. ds.executeNonSelectingSQL(CREATE_JDBC_SHADOW_TYPE1);
  204. ds.executeNonSelectingSQL(CREATE_JDBC_SHADOW_TYPE2);
  205. }
  206. catch (Exception e) {
  207. // ignore
  208. }
  209.  
  210. testNestedRecordExecution();
  211.  
  212. try {
  213. ds.executeNonSelectingSQL(DROP_PACKAGE);
  214. ds.executeNonSelectingSQL(DROP_JDBC_SHADOW_TYPE2);
  215. ds.executeNonSelectingSQL(DROP_JDBC_SHADOW_TYPE1);
  216. }
  217. catch (Exception e) {
  218. // ignore
  219. }
  220. ds.logout();
  221. }
  222.  
  223. public static void testNestedRecordExecution() {
  224.  
  225. PLSQLrecord recordNest = new PLSQLrecord();
  226. recordNest.setTypeName(PACKAGE_NAME + "." + NEST_RECORD_TYPE_NAME);
  227. recordNest.setCompatibleType(JDBC_SHADOW_TYPE1);
  228. recordNest.setJavaType(NestedRecord.class);
  229. recordNest.addField("VALUE01", JDBCTypes.VARCHAR_TYPE);
  230. recordNest.addField("VALUE02", JDBCTypes.NUMERIC_TYPE);
  231.  
  232. // when a PLSQLrecord (or PLSQLcollection) databaseTypes is re-used across
  233. // the arguments, need separate instances - and that includes nested databaseTypes
  234.  
  235. PLSQLrecord inRecord = new PLSQLrecord();
  236. inRecord.setTypeName(PACKAGE_NAME + "." + RECORD_TYPE_NAME);
  237. inRecord.setCompatibleType(JDBC_SHADOW_TYPE2);
  238. inRecord.setJavaType(Record.class);
  239. inRecord.addField("COLUMN01", JDBCTypes.VARCHAR_TYPE, 10);
  240. inRecord.addField("COLUMN02", JDBCTypes.NUMERIC_TYPE);
  241. inRecord.addField("NEST01", recordNest);
  242.  
  243. PLSQLrecord outRecord = new PLSQLrecord();
  244. outRecord.setTypeName(PACKAGE_NAME + "." + RECORD_TYPE_NAME);
  245. outRecord.setCompatibleType(JDBC_SHADOW_TYPE2);
  246. outRecord.setJavaType(Record.class);
  247. outRecord.addField("COLUMN01", JDBCTypes.VARCHAR_TYPE, 10);
  248. outRecord.addField("COLUMN02", JDBCTypes.NUMERIC_TYPE);
  249. outRecord.addField("NEST01", recordNest.clone());
  250.  
  251. PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
  252. call.setProcedureName(PACKAGE_NAME + "." + PROCEDURE_NAME);
  253. call.addNamedArgument("INPUT", inRecord);
  254. call.addNamedOutputArgument("OUTPUT", outRecord);
  255. ValueReadQuery query = new ValueReadQuery();
  256. query.addArgument("INPUT", Record.class);
  257. query.setCall(call);
  258. query.bindAllParameters();
  259.  
  260. NestedRecord nRec = new NestedRecord();
  261. nRec.value01 = "nestedtest";
  262. nRec.value02 = new BigDecimal("123.456");
  263. Record rec = new Record();
  264. rec.column01 = "test";
  265. rec.column02 = new BigDecimal("789.012");
  266. rec.nest01 = nRec;
  267.  
  268. List<Object> args = new ArrayList<Object>();
  269. args.add(rec);
  270. ds.executeQuery(query, args);
  271. }
  272. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!