<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:宋体; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-font-kerning:1.0pt;} code {mso-style-noshow:yes; mso-style-priority:99; mso-ansi-font-size:12.0pt; mso-bidi-font-size:12.0pt; font-family:宋体; mso-ascii-font-family:宋体; mso-fareast-font-family:宋体; mso-hansi-font-family:宋体; mso-bidi-font-family:宋体;} p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; text-indent:21.0pt; mso-char-indent-count:2.0; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:宋体; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-font-kerning:1.0pt;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:45766000; mso-list-type:hybrid; mso-list-template-ids:511578070 991301192 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:none; mso-level-number-position:left; margin-left:18.0pt; text-indent:-18.0pt;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} -->
1. 首先我们需要下载到 rosetta 包和相应的 classes12.jar 包进行程序的编译 .
Rosetta is Oracle's internal tool and Oracle does not ship it to customers.
2. 将 rosetta 包解压并将 classes12.jar 放入 rosetta 包中。路径结构如:
D:/rosetta/Rosetta.zip 和 D:/rosetta/ classes12.jar
3. Cmd 进入命令行模式 .cd 到放置 plsql procedure 头定义的相应的包目录下如 :
D:/rosetta procedure 头定义文件为 trn_wip_progress_pkg.pls 。
4. 输入如下命令:
Java –classpath d:/rosetta/classes12.jar;d:/rosetta/Rosetta.zip Rosetta –bc4j trn_wip_progress_pkg.pls –genrecs –package hand.oracle.apps.wip.project.progress.rosetta
首先是加入 classpath, 然后是 mode 我们使用的是 bc4j 然后是文件名
-package <javaPackageName>: defines a Java package name for the generated
-genrecs: in -java and -bc4j mode, forces the generation of record classes
for any records declared in the given PL/SQL packages, even if we don't
see the record being used as a procedure parameter type. This is the default behavior in -bc4j mode.
5. 会生成一下三个文件
trn_wip_progress_pkg_w.pkb , trn_wip_progress_pkg_w.pks , TrnWipProgressPkg.java
6. 将产生的包定义及包实现编译进数据库。导入 java 文件使用方法进行 pl/sql 包中的程序并返回结果 .
动态创建 VO 代码
1. 设置 VO 的 SQL ,及相应的 CLASS
OADBTransaction txn = getOADBTransaction();
OAViewDef viewDef =txn.createViewDef();
// 设置创建 VO 的 SQL
viewDef.setSql(stmt[0]);
// 设置为 true 表示创建 VO 使用的是专家模式 (FULL SQL)VO 的创建完全依赖与 SQL 字符串
If you provide the complete non-empty query string with setSql(String sql) or setQuery(String query) , set Expert Mode to true
.
viewDef.setExpertMode(true);
viewDef.setViewObjectClass("oracle.apps.fnd.framework.server.OAViewObjectImpl");
viewDef.setViewRowClass("oracle.apps.fnd.framework.server.OAViewRowImpl");
2. 使用 addSqlDerivedAttrDef (String voAttrName, String columnName, String javaType, int sqlType, boolean notNull, boolean queriable, byte updateable) 进行 VO 属性与对应 SQL COL 的关联。
3. 创建 VO
OAViewObject vo = (OAViewObject)createViewObject("ProjectProgressVO2",viewDef);
高级 TABLE 的动态添加
OAAdvancedTableBean entityTbl=(OAAdvancedTableBean)webBean.findIndexedChildRecursive("ProjectProgressRN");
entityTbl.setViewUsageName("ProjectProgressVO2");
OAColumnGroupBean
columnGroupBean= (OAColumnGroupBean)entityTbl.findChildRecursive("WipGroup");
for(int i=0;i<column_count[0].intValue();i++)
{
OAColumnBean groupColumn= (OAColumnBean)createWebBean(pageContext, COLUMN_BEAN, null, ("WipQuantityCol"+Integer.toString(i)));
OASortableHeaderBean columnHeader = (OASortableHeaderBean)createWebBean(pageContext, SORTABLE_HEADER_BEAN, null, ("WipQuantity"+Integer.toString(i)+"Hdr"));
columnHeader.setText(columns_tbl[0][i]);
columnHeader.setSortable(true);
groupColumn.setColumnHeader(columnHeader);
columnGroupBean.addIndexedChild(groupColumn);
OAMessageStyledTextBean columnLeaf = (OAMessageStyledTextBean)createWebBean(pageContext, MESSAGE_STYLED_TEXT_BEAN, null, ("WipQuantity"+Integer.toString(i)));
columnLeaf.setViewAttributeName(columns_tbl[0][i]);
groupColumn.addIndexedChild(columnLeaf);
}
Example of call pl/sql in java:
As an example of using Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it:
create or replace function foo (val1 char)
return char as
begin
return val1 || 'suffix';
end;
The function invocation in your JDBC program should look like:
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci:@<hoststring>");
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();
CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);