Oracle EBS Form开发基础


ORACLE ERP 开发基础 前言 4 EBS 入门一周之口水贴 4 EBS 入门二周之口水贴 5 EBS 入门三周之口水贴 5 EBS 入门四周之口水贴 6 Oracle Forms 基础 6 Forms 设置部份 6 设置 ITEM 为必填项 6 设置 ITEM 的初始值为当前日期 7 实现按“ENTER”自动跳至下一条记录 7 使用堆叠画布 7 深入了解 Forms 的事务触发机制 9 创建 FORMS 数据块的主从关系 9 FORMS 代码部份 11 FORMS 触发器的执行顺序 11 FORMS 变量类型 13 判断数据块的项是否为空 14 信息提示框之基本用法 14 用代码控制 ITEM 属性 15 用代码控制 ITEM 的焦点转移 17 用代码控制 WINDOW 属性 17 判断当前 FORM 的状态 17 在 when-new-record-instance 控制 BLOCK 的可用状态 17 实现将 LOV 可以自行录入内容 18 在 FORMS 调用 WEB 页面 18 Name_In()\COPY()函数 18 Forms 数据提交的方式 19 在一个 FORM 中调用不同的 WINDOW 19 Form 假死锁问题之初步解决 20 FORM 死锁解决 22 FORM6i 引入 JAVA 类 22 FORMS 之列表项动态赋值 23 FORMS 之 LOV 动态赋给记录组 24 屏蔽 FORM 系统提示信息 25 FORM 之间的调用 25 Tree-层次树之使用 25 遍历数据块 26 LOV 动态确保从块记录唯一 26 Form 实现复制上条记录 27 控制删除条件的触发器 27 实现进度条 28 Form 实现唯一性检查 28 Oracle Report 基础 29 Report 页面设置 29 数据模型、固定框、循环框 29 实现记录汇总行动态伸缩 31 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 2 查询视图无数据 31 Report 数据域 31 Report 汇总项 32 Report 动态条件查询 33 矩阵报表 33 Report 编译出错 33 Oracle 数据库基础 34 常用函数 34 substr 函数 34 Lpad/Rpad 函数 34 日期函数 34 随机取前 10 条不同的记录 35 TRUNC 函数 35 修改表的一些常用语法 36 舍入函数 36 实现类似 BREAK 语句 36 Oracle 定时器 37 Over 分析查询 38 Oracle 层次树查询 40 Merge into 应用 42 Truncate table 42 绑定变量与非绑定变量之比较 42 取不重复的记录行 44 查询具有相同薪水的职工 45 统计 EBS 表数量及 Oracle 概念 45 Oracle 死锁之解决方法 46 PL/SQL 过程调用表示法 47 PL/SQL 调用 Java 类 47 PL/SQL 操作 EXCEL 51 PL/SQL 异常机制 55 PL/SQL 面向对象编程 56 PL/SQL 实现字符串转成数组 57 PowerDesigner 58 ORA-01403 未找到数据之解决方法 59 PL/SQL 细粒度事务控制 60 物化视图 61 通过游标更新数据 62 给 PACKAGE 加密 62 Oracle Advanced Queue 63 跨数据库的 SQL 65 ORACLE 高级复制 66 查询某一表或视图中的触发器 68 EBS 开发基础 68 开发工具安装 68 Oracle EBS 工具集 69 EBS 二次开发包注册 72 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 3 利用 TEMPLATE.fmb 模板来开发 Form 73 注册表单 FORM 78 注册请求 80 注册职责 82 注册值集 84 注册主从值集 87 通过应用开发员取得系统管理员职责 89 查询视图时没有显示数据 90 注册报表(report) 90 请求的输出及日志 93 实现手动提交请求 94 手动提交请求示例 95 客制化菜单 97 客制化右键菜单 99 客制化工具栏 99 调用 EBS 日期控件 100 查询数据时限定语言环境 101 实现历史记录查询 101 实现文件夹功能 102 实现手电筒查找的功能 107 记录指示器切换标签页 110 EBS 的条件查询方法 111 自定义代码 112 通过科目编码取科目描述 115 Fnd_Profile 之使用 116 Form 之间共享参数组织 ID 116 弹性域 117 注册关键性弹性域 117 注册说明性弹性域 118 条件控制说明性弹性域 120 PO 行界面启用弹性域 121 调用会计科目弹性域 123 EBS11i Form 开发实现 MVC 模式 124 EBS 预警功能自定义开发 125 EBS 自定义邮件通知 128 EBS 配置文件(Profile)常用设置 132 设置菜单导出的文件格式 132 IE 打不开 EBS 133 一个职责下打开多个 FORM 界面 133 获取 EBS 的查询语句 133 EBS 调用 JSP 页面 134 OAF 页面查看历史信息 136 定义文件夹的管理权限 136 判断 EBS 提交请求是否成功 139 FORM 更新多表视图 140 WorkFlow 143 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 4 EBS WorkFlow 143 WorkFow builder 安装 143 Workflow 查看工作流权限 143 Workflow Builder 打开 PO 审批流时报错 144 Workflow 启动报错 145 Oracle BPM 145 BPM 安装 145 BPM 基本使用 146 Discoverer 工具 146 Discoverer 安装设置 146 Discoverer 快速入门示例 147 Discoverer 工作表数值型显示 150 通过工作簿找到对应的 Discoverer 职责 151 Discoverer JOIN 之使用 151 Discoverer 数值型无法正确显示 153 Discoverer 上钻/下钻 154 Discoverer 条件 LOV 155 Discoverer 分类汇总求 AP 供应商余额 156 Oracle DBI 工具 157 DBI 工具之配置 158 DBI 之报表开发 160 DBI 简单报表开发 160 DBI 维度报表开发 165 Oracle BIEE 工具 171 BIEE 工具之安装 171 BIEE 工具之体念 172 BI Publisher 工具之体念 173 数据导入工具 174 Dataload 174 Loadrunner 175 Oracle ADI 178 GL 日记帐导入 179 Oracle ADI-客户化开发 180 Other Notes 180 前言 偶是 2008 年才开始接触,这个传说中的 Oracle ERP。而在此前四年间,特别是在做 JAVA 程序员二 年多的时间中,养成了一种对架构、模式、面向对象的狂热追求,导致对 EBS 亦常失望。因为 EBS11i 没 有软件模式、设计、分层等,只有象面条般的 PL/SQL 语句。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 5 EBS 入门一周之口水贴 Oracle ERP-ebs11i。近一个周的摸索,算是对这个号称全球 ERP 第二的东东有点“表面”认识了。 在 这个领域,虽然 ORACLE FORMS 也是使用 JVM 来运行,但跟 JAVA 搭不上一点边。官方语言是 PL /SQL。开发工具相当古老 FORMS6i,要靠补丁才能在 XP 下运行。JAVA 当中的模式、设计、分层等等, 根本使用不上。只是拖拉一下控件就好,就算你不想拖拉也不行。因为 ORACLE FORMS 根本不支持,只 允许拖拉式编程。比 VB/DELPHI 这类语言还死心眼。唯一要编程的就是写些 SQL 代码,也谈不上什么分 层,表现层与 SQL 混 在一起。大多数的函数,ORACLE 都内置了,没有内置的,你也非常难去实现。 Forms 的拥护者说是 Oracle 的目的是要减少代码。这种理由根本站不住脚,通过属性设置来实现编程, 最终也是要生成代码的。只是这些代码 Oracle 不让你看到而已。于是 Forms 的拥护者又说 FORMS 封装的 好。晕倒!看来是没有使用过 VB 了,VB 是所有语言当中封装最好的了,但照样要被面临淘汰。Why? 再晕?看来 Forms 的拥护者软件工程也没学好。快速开发只是软件一个要求而已,开发工期短嘛,人力成 本就少啦。但软件的可维护性呢?没有一行代码,全部通过设置开发出来的软件,叫人怎么维护。修改一 个地方,从后台到前台全部的修改。这也叫可维护性? 再说说这个 EBS11i,操作非常不人性化。看个报表什么的,都是通过“请求”来实现,终端客户抱怨 操作步骤太多,点来点去的,点到最后都快忘了要做什么!ORACLE 的官方顾问却说,这是国际标准流程。 一说到 ORACLE 的缺点,就拿“国际”两字来压人。报表呢?没一份符合国人习惯。也不知道 ORACLE ERP 凭什么在国内站住脚? EBS 安装包相当大-32G,数据表的冗余量也非常大。一份采购单,在 PO 模块中有,在 PO 接口模块 中也有,另外在 AP 模块也存一份。也就是说一份相同的数据存了三次。 唯一值得一看的亮点就是跟财务管理模块高度集成。 EBS 入门二周之口水贴 调试,Oracle EBS 二次开发的调试,简直要将人送下地狱。记得刚接触 JAVA 时,还说调试不如 VB 方便。那现在看来,做 Oracle 二次开发简直就是地狱,而且还是十八层的那种。每修改一个东西,都要上 传到 SERVER 上调试。事实这也不叫调试,叫测试比较准确。总之就是对 Oracle EBS 恶心之极,搞不懂 还是有那么多人在追棒。有点可笑的是,在ERP100论坛上居然有人说,学EBS而不学SAP,是因为ORACLE 公司大。真是要晕倒。照这种逻辑,那么 Linux 根本上不应该存在,因为 Linux 远没有 MS 大。什么逻辑? EBS 入门三周之口水贴 PL/SQL 在语法上与 VB 相似,都有类似“IF…THEN…END IF”,VB 当中的 Event(事件)等同于 Forms 的触发器(triiger),只是概念不同而已。对于有过 VB 项目开发经验的我来说,刚接触 Oracle Forms 感觉就 是 VB。二年前在“Think in Java”的影响下,毫不忧郁地从 VB 转到 JAVA 阵营。两年过去了,在 JAVA 阵营,做纯 JSP 的 OA 项目,也做 STRUTS+HIBERNATE 的 MIS 项目,却始终无法逃离“增改删查”这 种技术含量不高的应用程序。从 ERP 的角度来看,我所做过的项目,抛开技术构架来看,只能算是 MIS, 严格上说连 MRPII 都算不了,更别说什么 ERP 了。现在接触的 Oracle EBS 应该算是 ERP 吧。但应用效果 呢?最多也只是 MRPII 的水平。 什么叫 MRP,也就是一些试算物料排程+库存的程序。也叫 MIS。 什么叫 MRPII 呢?MRP+财务管理系统。 什么叫 ERP 呢?MRPII+分销系统+供应链系统+CRM 系统+HR 系统+知识库等等。总之,ERP 有点无 所不含了,以致于 ERP 的失败率很高。大多数的公司虽然号称运用的是 ERP,其实质应用呢?最多是 MRP 或 MRPII,甚至只是一个功能好一点的记账软件。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 6 04 年 IBM 推出了 SOA 的概念后,ERP 遭到了前所未有的危机。Service Oriented Architecture 其思想 就是保护原有的 IT 投资,以此为基础进行软件功能的扩充。技术核心就是 Web Service。而 ERP 思想呢? ERP 的思想就是想用一套软件从制造-财务-分销-商业智能全盘搞定企业信息化。这种大而全的做法, 正在遭到 CIO 们的怀疑。 Oracle 也有推出自己的 SOA-(以 JDEVELOPER 为核心的组件化应用),这是 否也在预示 PL/SQL 要被 JAVA 代替呢?^_^ EBS 入门四周之口水贴 除了在 FORM 中使用属性窗口进行对象属性设置外,Oracle 也有提供函数来实现对 FORMS 对象的控 制。但总体上来说,给人的感觉就是不直觉,完全的黑箱操作。难道这就是 Oracle 所说的减少代码的编写? 事实上 VB 饱受批评,除了不支持全部的 Win API 外,就是封装的太死,连继承都不让使用。但用过 Oracle Forms,就会挺同情 VB 的做法。Oracle 的做法更绝,试图用 PL/SQL 统一前台与后台编程。连面向对象的 概念都没有,更别谈什么其他的了。尽管 Forms 最终是要生成 JAVA 的 APLLET 代码进行执行。还有 Forms 不支持类似于“.”的联想操作,也是让人想不通。如果哪个 C++或是 JAVA 开发工具不支持”.”操作,那么 肯定会进博物馆的。 OVER,即来之,则安之。停止比较,重新学习吧。 Oracle Forms 基础 Forms 设置部份 Forms 基本对象概念 FORMS 的基本对象说明 FORM: 可以有多个 Window。 Window: 只能有一个 CANVAS,但是还可以包括其他的类型的 CANVAS, 比如 CANVAS_STACKED,这种 Canvas 类型将在后面的章节中讲到。 Canvas: Canvas is a surface where visual objects,such as graphics and items,are arranged. 和 Block 没有直接的对应关系,而是直接和 Item 对应。一个 Canvas 上面可以有来自不同的 Block 的多 个 Item.,是我们对 Item 进行位置调整的平台。 Block: Logical owner of items,items in one block are logically related.There are two main tupes of blocks:data block and control block Item: Form 中最基本的组织元素,我们设计的最小单元 Parameter:相当于全局变量,其作用是传递参数,Oracle 标准的 Form 也有单独建一个 Block(多个 I tem)来 传递参数的例子。 Record Group: 主要应用是可以提供给 LOV 与 list item,ftree 作为数据源 A record group is internal Form Builder data structure that has a column/row.framework similar to a database table LOV:List Of Value,会直接对应到 Text Item 上面,点击后会弹出给用户选择数据。 Attached Libraries:存放的是 Oracle 标准的一些 PLL 文件。 Property Classes:Oracle 标准的一些属性类,我们在建立 Window、Canvas、Data Block 等都会选择一个属性类 以继承一些属性和方法 明白了上面的基本概念,就可以开工了。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 7 设置 ITEM 为必填项 Setup: Effect: 此效果与是 set_item_property('test.l_test',required,property_true)一样的。 设置 ITEM 的初始值为当前日期 实现按“ENTER”自动跳至下一条记录 设置 BLOCK 属性:导航器风格:改变记录。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 8 使用堆叠画布 Effect: 1.先将数据块、画布布局好(用向导的方式就可以了,具体操作就不用讲了吧)。 2.在画布中创建一个堆叠画布。 3.将项的画出属性更为堆叠画布(这一步最关键了)。 4.调整后得到下面这效果了。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 9 深入了解 Forms 的事务触发机制 编写一个健壮的 FORMS 应用程序,免不了要做各种数据的检验动作。所以必须了解 FORMS 事务触发器 的工作原理。其他类型的触发器相对来说比较好理解,就不详说了。 ① FORMS 处理事务分成发送(POST)和提交(COMMIT)两个阶段。这个跟 JAVA 中的事务操作类似, 也就是 Statement 和 Commit 两个阶段。但 FORMS 一些规则比较死,也就是说规定好 POST 之前会触 发 PRE 触发器之类等等。 ② 事务触发器分成三类,PRE-XXX、ON-XXX、POST-XXX,它们的执行顺序可以直接从其英文缩写得 出。 例:执行 INSERT 操作,会按以下顺序进行。 1.1 从数据项复制数据。 1.2 触发 PER-INSERT 触发器。 1.3 检查记录的惟一性。 1.4 插入行到其表或者触发 ON-INSERT 触发器。 1.5 触发 POST-INSERT 触发器。 创建 FORMS 数据块的主从关系 1、先创建两个数据块,当然这两个数据块在 DB 应该有外键的关系(不是一定要创建数据库外键关联哦?)。 2、选择从表的数据块,然后点击菜单数据块向导。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 10 3、选择关联属性。 非孤立:表示当有从记录时,不允许删除主记录(默认)。 级联:当删除主记录时,从记录也一起删除(推荐使用)。 孤立:当删除主记录时,从记录保留不变(不推荐使用)。 事实上,当选择不同的属性时,FORMS 会在主数据块中,自动创建两个触发器 ON-POPULATE-DETAILS:当插入主数据块记录时,自动将主键赋值给从数据块外键字段。 ON-CHECK-DELETE-MASTER:实现从删除主数据块记录时,从数据块要执行何种操作。 4、设置主键及报 FRM-30100 错误。 ①如果数据块属性设成使用主键,那么该数据块的 ITEM 必须有一个设成主键,否则报FRM-30100 错误。 ②即使数据块设成不使用主键。但该块的查询数据源名称与 DML 数据目标名称不一致时,也是必须设 至少一个 ITEM 为主键。否则报 FRM-30100 错误。如图: 注:查询数据源与 DML 数据目标不一致时,数据 INSERT、UPDATE、DELETE 都会发生在 DML 数据 目标那张表。 例如:以视图为查询数据源,而在数据表中仅保留 ID。而查询时,自动匹配查询到相应的 number、 description 将视图的字段设置如下,否则会报无法找到这个字段之错误。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 11 5、主键赋值。 按数据库设计模式范式要求,每张表都应该有一个 PRIMARY KEY。而且此字段一般不让用户操作,由 sequence 自动维护。我们可以数据块的 PRE-INSERT 触发器中加入代码: select hek_test_sq.nextval into :hek_test_headers.hid from dual; FORMS 代码部份 FORMS 触发器的执行顺序 针对我们所看的 FORM,我们总结了 FORM 的一些操作中 TRIGGER 的执行顺序。其中还有很多触发器没 有被提到,这是因为程序中没有用到。但是在 FORM 运行的过程中,这些触发器的执行过程都是设置好了 的,没有用到的触发器也一样会经过已经设置好的那个时间段,只是什么动作都没有做罢了。 触发器执行顺序: 1. 当打开 FORM 时: (1) PRE-FORM (2) PRE-BLOCK(BLOCK 级) (3) WHEN-NEW-FORM-INSTANCE (4) WHEN-NEW-BLOCK-INSTANCE (5) WHEN-NEW-RECORD-INSTANCE (6) WHEN-NEW-ITEM-INSTANCE 2. 当填写一行记录完成后,光标移动到下一条记录的时候: (1) WHEN-VALIDATE-RECORD (只将填写的记录与数据库中已存在的记录作唯一性的验证,如果只是页面上的数据重复而数据库中没有与 其重复的值则不会报错.) (2) WHEN-NEW-RECORD-INSTANCE (3) WHEN-NEW-ITEM-INSTANCE 3. 当点击“保存”时 (1) WHEN-VALIDATE-RECORD (将页面上的所有数据提交到数据库,若页面上有重复的数据,则提交第一次时成功但只是将数据先写到 数据库中一类似临时表的地方,在提交第二条重复记录的时候报错,执行事务回滚,原来执行成功的指令 也将被撤消) (2) PRE-INSERT (3) ON-INSERT (4) POST-INSERT (5) POST-FORMS-COMMIT (6) PRE-BLOCK(BLOCK 级) (7) KEY-COMMIT (8) WHEN-NEW-ITEM-INSTANCE 4. 当光标移动到当前数据块中已经显示的行上时: (1) WHEN-REMOVE-RECORD (2) WHEN-NEW-RECORD-INSTANCE (3) WHEN-NEW-ITEM-INSTANCE 当在该行上的不同 ITEM 移动时: (4)WHEN-NEW-ITEM-INSTANCE 5. 当要进行修改时(在记录中的某个项上进行了 修改时): Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 12 (1)ON-LOCK 6. 在修改完成后进行保存时: (1) WHEN-VALIDATE-RECORD (2) PRE-UPDATE (3) ON-UPDATE (4) POST-FORMS-COMMIT (5) PRE-BLOCK(BLOCK 级) (6) KEY-COMMIT (7) WHEN-NEW-ITEM-INSTANCE 7. 删除一条记录时: (1) ON-LOCK (2) WHEN-REMOVE-RECORD (3) KEY-DELREC (4) WHEN-NEW-RECORD-INSTANCE (5) WHEN-NEW-ITEM-INSTANCE 8. F11 查询过程: (1) WHEN-CLEAR-BLOCK (2) WHEN-NEW-RECORD-INSTANCE (3) WHEN-NEW-ITEM-INSTANCE 在输入查询条件后点 CTRL+F11: (4) PRE-QUERY (5) WHEN-CLEAR-BLOCK (6) POST-QUERY (7) WHEN-NEW-RECORD-INSTANCE (8) WHEN-NEW-ITEM-INSTANCE 9. CRRL+F11: (1) WHEN-CLEAR-BLOCK (2) PRE-QUERY (3) WHEN-CLEAR-BLOCK (4) POST-QUERY(每查一条记录,触发一次) (5) WHEN-NEW-RECORD-INSTANCE (6) WHEN-NEW-ITEM-INSTANCE 10.从查询状态(F11)转为输入状态(F4)时: (1) WHEN-CLEAR-BLOCK (2) KEY-EXIT (3) WHEN-NEW-RECORD-INSTANCE (4) WHEN-NEW-ITEM-INSTANCE 11.手电筒查询过程: (1) QUERY_FIND(BLOCK 级) 输入查询条件后,点击“查询”按钮: (2) WHEN-CLEAR-BLOCK (3) PRE-QUERY (4) WHEN-CLEAR-BLOCK (5) POST-QUERY (6) WHEN-NEW-RECORD-INSTANCE (7) WHEN-NEW-ITEM-INSTANCE Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 13 12.点击“New”时: (1) WHEN-NEW-RECORD-INSTANCE (2) WHEN-NEW-ITEM-INSTANCE 13.点击“Edit Field”时: (1)KEY-EDIT 14.点击“Window Help”时: (1)KEY-HELP 15.点击“Clear Record”时: (1) WHEN-REMOVE-RECORD (2) POST-QUERY (3) WHEN-NEW-RECORD-INSTANCE (4) WHEN-NEW-ITEM-INSTANCE 16.点击 F4 关闭时: (1) KEY-EXIT (2) POST-FORM 17.点击“Close Form”按钮关闭时: (1) KEY-EXIT (2) POST-FORM 18.点击“Translations”按钮时: (1)TRANSLATIONS 19.点击小叉号关闭时: (1) WHEN-WINDOW-CLOSED (2) CLOSE-WINDOW (3) KEY-EXIT (4) POST-FORM 20.选中 LOV 列表: (1) KEY-LISTVAL (2) WHEN-NEW-ITEM-INSTANCE 21.选中记录前面的小条时: (1) WHEN-NEW-RECORD-INSTANCE (2) WHEN-NEW-ITEM-INSTANCE(数据项级) (3) WHEN-NEW-ITEM-INSTANCE 22.光标上下移动时 (1) WHEN-NEW-RECORD-INSTANCE (2) WHEN-NEW-ITEM-INSTANCE 22. Form 切换到当前窗体时 (1) WHEN-FORM-NAVIGATE (2) WHEN-NEW-ITEM-INSTANCE FORMS 变量类型 FORMS 有提供多种变量,提供给 PL/SQL 使用,不同的变量,生存周期是不一致的。 1.项变量:只能在当前的确表单内引用。 语法::blockname.itemname Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 14 2.全局变量:只能存储字符型数据,可以在当前会话的所有表单内引用。 语法::global.name 例:可以 when-new-form-instance 中声明一个全局变量 Global.name:=’this is a global’; 然后在 when-button-pressed 中引用 Message(:Global.name); 3.参数(Parameter):在 Forms 中直接创建一个参数。 语法::Parameter.parameter1 := ‘test’; 另外:在 EBS 中,可以通过在注册功能时,给 Parameter 赋值(注意:字符类型赋值是加””双引号)。 4.系统变量:这个是 FORMS 预定义的,直接使用即可 例::SYSTEM.CURRENT_DATETIME 判断数据块的项是否为空 IF :BlockName.Item1 is null THEN message(‘Item1 为空’); END IF; 这个和一般的编程语言使用字符串比较很不一致,但从 PL/SQL 的语句来看,却也是正常的。 信息提示框之基本用法 1、message Usage:message('提示信息'); Effect:在 FORMS 左下角会出现这个提示信息。 2、fnd_message 用法大全 2.1 FND_Message.Debug Usage::fnd_message.debug('提示信息'); Effect:会直接弹出一个对话框,与 vb 中的 msgbox(“提示信息“)类似。 2.2、fnd_message.question . Usage: ----------------------------------------------------------------------------- declare v_num number; begin FND_MESSAGE.SET_STRING('确要执行此操作吗?'); v_num := FND_MESSAGE.QUESTION('否', '是',NULL, 1,2); if v_num=2 then fnd_message.debug('选择了是'); elsif v_num=1 then Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 15 fnd_message.debug('选择了否'); end if; end; --------------------------------------------------------------------------------- Effect: Effect:会直接弹出一个选择框,与 vb 中的 msgbox(“提示信息“,vbokcancel)类似。 2.3、FND_MESSAGE.show 这是一个 Procedure,把信息以最基本的方式显示给用户,和 FND_MESSAGE.DEBUG 一样的效 果。但分成了两步来写。 begin fnd_message.set_string('show a string!); fnd_message.show; end; 2.4、FND_MESSAGE.hint 这是一个 Procedure 这种方式不会弹出对话框给用户,而是显示在左下脚的状态栏上面。 2.5、FND_MESSAGE.error 这是一个 Procedure 以 Error 信息的方式显示给用户 用代码控制 ITEM 属性 1、SET_ITEM_PROPERTY 和 SET_ITEM_INSTANCE_PROPERTY 区别: 如果是控制单行记录或者多行记录中的全部记录:SET_ITEM_PROPERTY 如果是控制多行记录中的单一行记录:SET_ITEM_INSTANCE_PROPERTY 理解它们的最好区别就是亲自动手写一例子。 ①控制数据块 T_TEST(多条记录)的 ITEM 的某一条记录是否可更改。 在 post-query 触发中加入: SET_ITEM_INSTANCE_PROPERTY(‘T_TEST.TID’ Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 16 SET_ITEM_INSTANCE_PROPERTY('T_TEST.TID',CURRENT_RECORD,INSERT_ALLOWED,PROPERTY _FALSE); Effect: ②控制数据块 T_TEST(多条记录)的单个 ITEM 的全部记录是否可更改。 SET_ITEM_PROPERTY('T_TEST.TID',ENABLED,PROPERTY_FALSE);--ITEM不可获得焦点 或: SET_ITEM_PROPERTY('T_TEST.TID’,INSERT_ALLOWED,PROPERTY_FALSE);-- ITEM可以获得焦点 SET_ITEM_PROPERTY('T_TEST.TID',UPDATE_ALLOWED,PROPERTY_FALSE); 提示: 如果此时使用 SET_ITEM_INSTANCE_PROPERTY 进行更改可用状态,必须先使用 SET_ITEM_PROPERTY 改 回可写属性,之后方可使用 SET_ITEM_INSTANCE_PROPERTY。 Effect: ③控制数据块某个 ITEM 只允许 insert,不允许 delete. 在 when-new-form-instance 触发器中加入 --先将数据块设为不可删除 set_block_property('T_TEST ',delete_allowed,property_false); --然后对ITEM设为不可update set_item_property(' T_TEST .TID',update_allowed,property_false); 2、用代码控制 ITEM 的可见性 SET_ITEM_PROPERTY('CONTROL.ITEM1', ENABLED, PROPERTY_FALSE); 3、用代码控制 ITEM 必填 Set_Item_Property(‘CONTROL.ITEM1’,REQUIRED,Property_True); 4、用代码控制 ITEM 不可更新 Set_Item_Property(' CONTROL.ITEM1',INSERT_ALLOWED ,Property_false); Set_Item_Property(' CONTROL.ITEM1',UPDATE_ALLOWED ,Property_false); 提示:与 enable 属性最大的区别就是该 ITEM 框是否允许获得焦点。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 17 用代码控制 ITEM 的焦点转移 此功能,如果不使用代码。可以直接的 block 中拖动 ITEM 的顺序,从而达到 ITEM 获取焦点的顺序。 但如果想做一些特殊的控制,就种方法就办不到了。例:有两个 ITEM,ITEM1 输入省份,ITEM2 输入市 级。在省份 ITEM 为空时,市级 ITEM 是不允许输入了。 在 ITEM2 的的 WHEN-NEW-ITEM-INSTANCE 加入 if :block.item1 is null then fnd_message.debug('年度不能为空'); Go_Item(‘block.item1'); end if; 用代码控制 WINDOW 属性 在 pre-form 触发器下,加入: BEGIN SET_WINDOW_PROPERTY('MAIN_WINDOW',TITLE,'HEK_工单自动生成'); --设置标题 END; 判断当前 FORM 的状态 判断当前 FORM 的状态,如:修改、新增、查询状态。 :SYSTEM.Form_Status 返回三种值:CHANGED、NEW、QUERY。 在 when-new-record-instance 控制 BLOCK 的可用状态 这个触发器很好用,例如:可以根据主块的某个项的值,来控制子块是否可操作。 Begin If :blockname.test = ‘Y’ then set_block_property('blockname',DELETE_ALLOWED,PROPERTY_FALSE); set_block_property('blockname',INSERT_ALLOWED,PROPERTY_FALSE); set_block_property('blockname',UPDATE_ALLOWED,PROPERTY_FALSE); end if; end; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 18 实现将 LOV 可以自行录入内容 在 WHEN-NEW-ITEM-INSTANCE 触发器加入 begin set_item_property('block.item1', VALIDATE_FROM_LIST, property_false); end; 在 FORMS 调用 WEB 页面 web.show_document('http://www.sina.com.cn','_blank'); 第二参数为页面的加载方式,有四种选择。 _SELF _PARENT _TOP _BLANK Effect:自己可以去 show 一下,印象会比较深。 Name_In()\COPY()函数 1、Name_In()函数有点奇怪,丢进去是字符串,返回也是字符串。Oracle 官方文档说是为了实现 indirect reference。 例: IF :emp.ename = 'smith' -- direct reference IF NAME_IN('emp.ename') = 'smith' -- indirect reference 另 外 : Name_In()可 以 用 来 间 接 引 用 一 些 系 统 函 数 。 例 : var t_form varchar2(50):=Name_In( ‘system.current_form’); 2、Copy()函数呢?也是一样,也是为了实现 indirect reference。 例: :emp.ename := 'smith'; -- direct reference Copy('smith','emp.ename'); -- indirect reference 3、后来在 ITPUB 上面看到,说此两个函数是为了实现类似于 C++中宏定义。 3.1 NAME_IN 实现宏定义的例子 FUNCTION FUN_JF (PRE_BLOCK_NAME IN VARCHAR) RETURN BOOLEAN IS BEGIN if name_in(pre_block_name'sj.dwjtc')>name_in(pre_block_name'yj.dwjtc') then message(' 测试!'); return false; end if; return true; END; -- PRE_BLOCK_NAME 是形参,实际调用时,通过 Name_in()函数来实现变量宏替换成 FORMS.ITEM。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 19 3.2 COPY 实现宏定义的例子: BEGIN DELCATE strItemName varchar2(20); BEGIN for I in 1 …6 loop strItemName:=':b.l“to_char(i); copy(null,strItemName); --strItemName 由变量宏替换成 Forms 中 Item。 end loop; END; 4、通过上面两个例子,应该可以明白 indirect reference 与 direct reference 的区别。但我个人觉得 C++的宏 定义很少这样作用,一般都是起常量的作用。例:#difned BUFFER 100。而且 ANSI C++推荐使用 const 来代 替宏定义。 Forms 数据提交的方式 1、commit_form 先针对 form 上面的数据变动进行 commit,然后对于代码中的类似 DML 语句也进行提交; 如果 form 上面的数据变动和代码中的数据变动有冲突,最后以 FORM 上的为准。 适用:一般来在直接修改 Form 上的数据时,就使用 commit_form。 2、commit 对 form 和数据库进行提交。如果 form 上面的数据和代码中的数据变动有冲突,最后以 FORM 上的 为准。 适用:一般来在直接使用 DML 代码修改数据时,就使用 commit。 3、do_key('commit_form') 会首先寻找 form 下的 triggers 中的 KEY-COMMIT 这个 trigger,并执行 KEY-COMMIT 中所写的代码。 如果没有 KEY-COMMIT 这个 trigger,则会针对 form 和代码一起提交。 如果 form 上面的数据变动和代码中的数据变动有冲突,最后以界面上的为准。 适用:只是更改了一下代码执行的任先顺序,没有什么实质区别。 4、forms_ddl('commit') 只针对代码中的 update,insert,delete 语句进行提交。如果 form 上面的数据有变动,是不会提交的。 适用:一般不用。 在一个 FORM 中调用不同的 WINDOW 1、基础概念: ① 一个 FORM 是可以包含多个 WINDOW,一个 WINDOW 可以切换不同的 CANVAS。 ②对于包含有多个 WINDOW 的 FORM,FORM 的“第一个导航块”属性,决定了首先打开的 WINDOW。 ③另外在 EBS 开发中,template 模板中 FORM-LEVEL 的 PRE-FORM 触发器,会有如下代码: app_standard.event('PRE-FORM'); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 20 app_window.set_window_position('MAIN_W', 'FIRST_WINDOW'); 上述代码也会决定,加载 FORM 时首先要打开的 WINDOW 2、FORM 调用 WINDOW 的例子: 对于包含有多个 WINDOW 的 FORM,在已打开的 WINDOW 上,通过按钮打开另一个 WINDOW。 BEGIN SHOW_WINDOW('Test_W',2,2); SET_WINDOW_PROPERTY('Test_W',TITLE,'标题'); END; Effect: 3.错误: 关闭窗口时出现以下提示 解决:在该画布上必须要有一个可以导航的 item。该 ITEM 必须满足以下其中一个条件: 1.该 item 是可用的,并且该 ITEM 所属的块必须是数据块。 2.该 item 是失效的,即 enabed 为 property_false。 Form 假死锁问题之初步解决 1、错误提示: 2、原理知识: 2.1 当我们在 Forms 中,试图更改 block 中数据的时候,Forms 先发出一个对该行数据的 select ... for update nowait 查询,希望锁定该行(该锁是 ORACLE 行级 X 锁)。如果不能锁定,Forms 提示 Could not reserve records (2 trys). Keep trying?。如果用户选择No,Forms报告FRM-40510 错误:ORACLE error: unable to reserve record for update or delete。 2.2 block 加锁的模式。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 21 block 的锁定模式如果为 automatic 或 immediate,会在修改记录时 Forms 会立即锁定数据库记录;如 果设为 delayed,在保存时 Forms 才尝试锁定记录。也就是说这个属性不管如何设置,都不会影响锁的 模式,有影响的只是什么时候加锁而已。 2.4 上面已经说过了,Forms 在更数据行时,会先对该数据行加行级 X 锁。这个是 Oracle 写死在 Forms 中的,我们无法手工修改。你想不加锁也不行!Oracle 够野蛮吧!^_^。不要拿 Oracle 推荐的 transaction 来压我,要明白这个世界上,不是全部的业务应用都需要严格的 transaction。 2.5 即使前面的查询锁定步骤成功,Forms 还要比较查询结果和当前行的值是否一致。如果两者不完全 相同,Forms 抛出 FRM-40654(记录已经被另一个用户更新,重新查询以查看修改)错误。这么做主 要是为了防止 lost update 的情形,不让用户根据过时的数据来做出修改。这一点请认真理解哦? 3、解决方法: 3.1 避免在 TRIGGER 中直接使用 UPDATE 语句,这样会造成 Forms 上的数据与 Oracle 数据库的数据不 一致,从而造成 FRM-40654 的错误。 3.2 要是问题都是由方法 3.1 造成的,那么它就不是问题了。 3.2.1 下面细讲造成这次造成 Form 假死锁的根本原因。被锁的记录-订单编号为 2000000747: 3.2.2 注意认真查看这条记录,我们注意到 3967.8 这个字段。^_^,至于为什么会注意到这个字段。我 折腾了一周多,并且在 ITPUB 上反复认真学习 FORM 底层数据操作的原理,再加 N*N 次方 TEST, 最后才锁定这个字段。3967.8 是“供方承担”的金额字段。经查数据字典又得知数据库字段名为 “supply_pay” 3.2.3 我们直接从数据库 SELECT 这个记录出来看看。 select hm.oe_head_number,hm.supply_pay from hek_ods_th_fee_m hm where hm.oe_head_number='2000000747' 看清楚了吗?supply_pay 数据库的值为 3967.799796,而 FORM 界面上的值却为 3967.8。也就是说 FORM 自动帮我们四舍五入了。这样我们在 FORMS 上 MODIFY 这条记录时,FORM 就会判断 3967.8 <>3967.799796,从而报 FRM-40654 的错误。而我们呢?却被误导一直在数据库锁方面找问题。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 22 于是,我们总结出,可恶的 ORACLE 自作聪明,帮我们四舍五入了。可是我们真需要四舍五入吗? 就算是要,自已加个 ROUND 不就得了吗?这不能不说是 FORMS 一个 BUG!这也再次提醒我们不能 对 ORACLE 太崇拜了哦? FORM 死锁解决 关于在 Form 死锁的产生的原因,有很多种。但主要的是主外键导致,如:允许修改主键的值,外键 未加索引等。例如:在 Form 修改任何一笔记录,即使只修改了一列。Form 也会生成诸如 update t set c =:x1,c2=x2….这样的语句,也就是说,只要修改了一列,但 Form 就会提交修改全部的列,即使其他列并 未修改。 下面这个属性可以设置只更新修改列。这个属性很好用,但很多开发人员并不知道。 FORM6i 引入 JAVA 类 看到 FORM 中有一个导入 JAVA 类的菜单,就特亲切。^_^,至少不会让我学的 JAVA 知识白费。事实 上 ORACLE 很多产品都是要么是用 JAVA 开发,要么提供了 JAVA 编程接口。所以 JAVA 程序员转到 ORACLE 阵容,还是比较容易的。 1. 安装 JDK,FORM6i 最好安装 1.4 的版本,太高版本怕支持不了,比如泛型类。 2. 设置环境变量。 2.1 首先设置 JAVA 基本环境变量。 JAVA_HOME:C:\j2sdk1.4.2_17 Classpath:C:\j2sdk1.4.2_17\lib\tools.jar; Path:C:\j2sdk1.4.2_17\bin; 2.2 设置 FORM 有关 JAVA 的环境变量。 Classpath: .; C:\orant\TOOLS\COMMON60\JAVA\IMPORTER.JAR;C:\orant\TOOLS\COMMON60\JAVA Path::C:\Program Files\Java\j2re1.4.2_17\bin;C:\j2sdk1.4.2_17\jre\bin\client Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 23 3.引入 JAVA 类。 3.1 首先,写一个 Test 的 JAVA 类。然后将这个类的目录加入 Classpath 环境变量中。 Classpath:D:\JAVA_FORM\Socket\classes; 3.2 导入类。 导入成功后,FORMS 会将 JAVA 类转成 PL/SQL 包。 3.3 调用 Test 类。 declare obj ORA_JAVA.JOBJECT; a varchar2(50); begin obj := Test.new(); a:=TEST.GETTEST(obj); message(a); end; FORMS 之列表项动态赋值 1.将数据块 d_test 的 l_test 项 ITEM TYPE 修改为 LIST、Data Type 修改为 Char。 2.在 when-new-form-instance 触发器中加入。 2.1 实现 LIST 的值来源为固定的值 declare list_id Item; begin list_id := Find_Item(' d_test.l_test '); Clear_List(list_id); Add_List_Element(list_id,1,'批',0); Add_List_Element(list_id,2,'固定计划单',11); end; 2.2 实现 LIST 的值来源为数据表 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 24 declare rg_name_test varchar2(40) :='test_name'; v_sql_test varchar2(300); rg_id_test recordgroup; v_status number; begin clear_list('d_test.l_test'); rg_id_test := find_group(rg_name_test); if id_null(rg_id_test) then --记录组要求有两个为varchar2的列 v_sql_test := ' select hname,hmark from hek_test_headers'; rg_id_test := create_group_from_query(rg_name_test,v_sql_test); end if; v_status := populate_group(rg_id_test); populate_list(' d_test.l_test ', rg_id_test); end; 如果报错:FRM-41337 错误。可能是 LIST 的数据类型与查询记录组的类别不同。 FORMS 之 LOV 动态赋给记录组 1.在数据块 d_test,增加一文本项 l_test、LOV 项 lov_test、两个记录组 Rec_test1、Rec_test2。 2.分别给给记录组 Rec_test1、Rec_test2 赋 SQL 语句,并将项 Lov_test 的记录组设成 Rec_test1。 3.在 WHEN-BUTTON-PRESS 触发器中加入 Declare Lov_id LOV; Begin Lov_id := Find_Lov('lov_test'); if Get_Lov_Property(Lov_id,Group_Name)=' Rec_test1' then Set_Lov_Property(kpi_lov_xsbb,Group_Name,' Rec_test2'); end if; End; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 25 屏蔽 FORM 系统提示信息 1.1 直接将 SYSTEM.MESSAGE_LEVEL 设成 25,这样大于 25 的信息提示就不会提示了。 Declare t_l number := :SYSTEM.MESSAGE_LEVEL; Begin :SYSTEM.MESSAGE_LEVEL :=25; :SYSTEM.MESSAGE_LEVEL := t_l; End; 1.2 上述方法只适用于小于 25 的信息提示,无法屏蔽错误提示屏蔽错误提示可以 FORM 的 ON-ERROR 或 ON-MESSAGE 中加入拦截代码(不提倡屏蔽错误提示。)。 declare t varchar2(10) := error_type; begin if (error_code=40202 or error_code=40401) or t='FRM' then NULL; end if; end; FORM 之间的调用 ORACLE FORM 提供多种方法来实现不同 FORM 之间的调用。 1.CALL_FORM 或 OPEN_FORM 这种方法比较直观,但此种方法需要给出 FORM 的详细路径。如: call_form('/data/deve/deveappl/au/11.5.0/forms/ZHS/HEK_DISCOUNT_PERIOD_NEW.fmx'); 其中:call_form 与 open_form 的区别:open_form 可以保留原表单。 2. APP_NAVIGATE.EXECUTE 或 FND_FUNCTION.EXECUTE 例:APP_NAVIGATE.EXECUTE('HEK_DISCOUNT_PERIOD_NEW', 'Y', 'Y', null); 其中 APP_NAVIGATE.EXECUTE 与 FND_FUNCTION.EXECUTE 的区别: APP_NAVIGATE.EXECUTE 只打一个 FORM,而 FND_FUNCTION.EXECUTE 调用多少次,就打开多少 个。 Tree-层次树之使用 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 26 遍历数据块 go_block('BLOCKNAME'); first_record; IF :SYSTEM.block_status <> 'NEW ' THEN Loop if :system.last_record = 'TRUE' then exit; else next_record; end if; end loop; end if; LOV 动态确保从块记录唯一 所谓的动态确保从块记录唯一,就是指在从块记录表实现类似 unique key 的作用。那么为什么不直接 使用 unique key。这是因为 unique key 只能实现整张表的唯一性,而我们在这要实现的功能是从块与主块 关联时,该字段才是唯一的。如:一张采购单可以有多个 ITEM,但一张采购单不允许出现相同的 ITEM。 下边只大概说明一下程序的思路。FORM 的主从关系开发就省略了吧。 1.创建一个 Oracle 临时表,用来 FORM 尚未 COMMIT 时的临时 ITEM。 注:这边最好 Oracle 临时表,这样没有并发处理的压力,也不会造成死锁。 create GLOBAL temporary table HEK_TEMP( item_id int ) ON COMMIT DELETE ROWS 2.修改 LOV 记录组的 SQL 语句: 修改前: select ITEM_NO, ITEM_DESC1, ITEM_UM, iim.ITEM_IDfrom apps.IC_ITEM_MST_VL iim 修改后: select ITEM_NO, ITEM_DESC1, ITEM_UM, iim.ITEM_ID from apps.IC_ITEM_MST_VL iim where not exists (select lt.item_id from HEK_DISCOUNT_ITEM_LINES_TEMP lt where lt.item_id =iim.item_id) 3.在 FORM 的从块中添加两个触发器。 WHEN-VALIDATE-RECORD 加入: if :block2.item_id is not null then insert into HEK_TEMP (item_id)values(:block2.item_id); end if; WHEN-REMOVE-RECORD 加入: if :block2.item_id is not null then delete from HEK_TEMP t where t.item_id =:block2.item_id: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 27 end if; 5.实现效果: 说明:尽管尚未 commit,但 2101005001 仍不能输入两次。 Form 实现复制上条记录 此功能就是要实现,同时按下 shift+F6 自动复制上条记录。在数据块的 KEY-DUPREC 触发器中加入 begin duplicate_record; end; 效果如下: 控制删除条件的触发器 在相应数据块的 KEY_DELREC 加入条件控制参数。 if :parameter.P_APPROVE_FLAG = 'Y' then fnd_message.debug('您不能删除此记录,只能由录入员删除。'); raise form_trigger_failure; end IF; 实现进度条 Declare Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 28 V_all_count number :=10000; V_current_count number:=0 Begin For v_current_count in 1..10000 Loop APP_WINDOW.PROGRESS(V_current_count /v_all_count); End loop; End; 效果如下: Form 实现唯一性检查 本文所谓的唯一性检查指:组合主键或组合唯一键的检查。当然,如果是新建的表,我们可以通过添加唯 一索引来实现。 例:针对表 TABLE1 的 c1、c2、c3 列建唯一索引键。 alter table TABLE1 add constraint HEK_UNIUE unique(C1,C2,C3) 但上述方法对于已存在的表,并且 C1、C2、C3 列已经存在冗余数据,就无能为力啦。事实在 FORM 中解 决此问题,也是很简单。只有些开发人员偏要将简单的问题复杂化。 解决方法: 在 post-insert、post-update 的 trigger 加入判断代码: declare l_cou number; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 29 begin select count(*) into l_cou from TABLE1 where c1=:block.c1 and c2= :block.c2 and c3=:block.c3 if l_cou > 1 then fnd_message.debug('此数据存在冗余'||:hek_ods_th_price_m_v.price_name); raise form_trigger_failure; end if; end; Oracle Report 基础 前言 Oracle 的东西总是要“标新立异”,report 也不列外,非得搞个什么循环框、固定框之类专有概念。外 面大多数报表工具,如 crystal report、Java Birt、VB ActiveReport 等报表工具,其基本概念是一致的。而 Oracle Report 就不一样了。什么循环框、固定框,不就是几 group 么?好像换个概念,技术含量就比较高 似得?还有 Report 的撤销重做功能,居然只能有一次。Faint! Report 页面设置 数据模型、固定框、循环框 1.0 数据模型与循环框的层次 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 30 1.1 布局原则 一般布局方法是先画固定框,然后在其内套入:1.表头固定框,2.表数据源循环框。 最外层的固定框属性设置: 注:最外层固定框的“打印对象在”必须设置为“第一页”。 表头固定框属性设置: 注:表头的“打印对象在”必须设置为“全部页”。 表数据源属性设置: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 31 1.2 实现自动分页。 当记录总数不只一页时,要实现自动分页,必须将最外层的固定框或循环框“打印对象在”设置为“第 一页”。 实现记录汇总行动态伸缩 查询视图无数据 如果确认 SQL 语句确有数据,则有可能是 OU 的设置的问题。 在报表的 Before Parameter Form 触发器加入初始化条件。 function BeforePForm return boolean is begin APPS.FND_GLOBAL.apps_initialize( user_id =>APPS.FND_GLOBAL.user_id, resp_id =>APPS.FND_GLOBAL.resp_id, resp_appl_id =>APPS.FND_GLOBAL.resp_appl_id ); return (TRUE); end; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 32 Report 数据域 Oracle Report 引用数据模型的数据源,有二种方式。 1.较常规的做法。直接使用工具栏上的“域”标签,然后设置相应数据源 2.使用“文本”标签,然后用“&”进行引用。 3.在 PL/SQL 中引用报表域文本的值,只需在 PL/SQL 直接引用域对应源字段名称即可(无须加组名)。 Report 汇总项 Report 的汇总项比较灵活,在不同的组其汇总规则是可以多变的。这也会隐藏一些不易发现的问题。 比如,下面这个数据源分为三个组。 这个数据源大量使用了汇总项,但汇总项的默认汇总规则是基于整个数据源的,也是就说汇总项是基于整 个 REPORT 的。如图所示: 这也是会造成错误的原因,因为我们在使用汇总项,会自以认为 REPROT 会帮我们处理好这个汇总规则。 可事实却相反。这个汇总项的“重设于”属性就是汇总规则。显然处于分组的汇总“重设于”应该基于相 应的组。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 33 Report 动态条件查询 如果一条 SQL 的查询条件是动态的,SQL 语句本身是无法做到这个功能。在 JAVA 中常见的做法进行这字 符串拼接来达到这一功能。在 Report 也差不多,在触发器事件中进行字符串拼接。 1、 增加一个报表用户参数 p_batch_id_char 、p_batch_id_temp 2、 在报表 After Parameter Form 触发器,加入字符串拼接条件 function AfterPForm return boolean is begin if nvl(:p_batch_id_char,'A')='A' then –通过条件,来进行字符串拼接。 :p_batch_id_temp := 'and 1=1' ; else :p_batch_id_temp := 'and to_char(m.fee_batch_id) in ('|| :p_batch_id_char ||')' ; end if; return (TRUE); end; 3、 在数据模型的 SQL 语句中加入刚才拼接的字符串。 Select * from table_name where &p_batch_id_temp 注:是用“&”,不是 PL/SQL 中的“||”。 矩阵报表 矩阵报表,不要被 Oracle 的概念给忽悠了。其实就是 crystalreport 中是交叉报表。 Report 编译出错 在本机上可以执行成功,但上传在 EBS 服务器时,执行报错。 错误提示: Spawned Process 15601 REP-0736: 此处存在未编译的程序单元. REP-1247: 报表包含未编译的 PL/SQL. 原因分析:很有可能是程序包、视图、表等对象,没有授权或是失效了。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 34 Oracle 数据库基础 常用函数 substr 函数 Example:SELECT substr('teach',0,3) FROM DUAL Effect: tea Example: SELECT substr('teach',3,3) FROM DUAL Effect: ach Lpad/Rpad 函数 Example: SELECT LPAD('TEACH',4) FROM DUAL Effect: teac Example: SELECT LPAD('TEACH',10,'5') FROM DUAL Effect: 55555TEACH 日期函数 Oracle 的日期函数比较丰富,不象 SQL SERVER 只有 dateadd、datediff 之类。 1.系统时间 SQL:select getdate() value Oracle:select sysdate value from dual 2.前后几日 都支持直接与整数相加减 3.求日期 SQL:select convert(char(10),getdate(),20) value Oracle:select trunc(sysdate) value from dual select to_char(sysdate,'yyyy-mm-dd') value from dual 4.求时间 SQL:select convert(char(8),getdate(),108) value Oracle:select to_char(sysdate,'hh24:mm:ss') value from dual 5.取日期时间的其他部分 SQL:DATEPART 和 DATENAME 函数 (第一个参数决定) Oracle:to_char 函数 第二个参数决定 下表补充说明 SQL 与 ORACLE 在取参数时的区别 年度:year、yy、y、yyyy 季度:quarter 、qq、q 月度:month、mon、mm、m (m 参数 Oracle 无效) 星期:dayofyear (dayofyear 参数 Oracle 无效) 、dy, 日:day、 dd Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 35 一年中的第几周:week(week 参数 Oracle 无效)、wk(wk 参数 Oracle 无效)、 ww 一周中的第几天:weekday(此参数 Oracle 无效) 、dw(dw 参数 Oracle 无效)、d 时:Hour hh,hh12,hh24 (hh12,hh24 参数 SQL 无效) 分:minute mi, n (n 参数 Oracle 无效) second ss, s (s 参数 Oracle 无效) millisecond ms (ms 参数 Oracle 无效) 6.当月最后一天 SQL:比较烦琐,先求当月的第一天,然后求得下月的第一天,最后减一得到当月最后一天。 Oracle:select LAST_DAY(sysdate) value from dual 7.本星期的某一天(比如星期日) SQL:week 函数 Oracle:SELECT Next_day(sysdate,7) vaule FROM DUAL; 8.字符串转时间 SQL:可以直接转或者 select cast('2004-09-08'as datetime) value O:SELECT To_date('2008-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL; 9.求两日期某一部分的差(比如秒) SQL:select datediff(ss,getdate(),getdate()+12.3) value Oracle:直接用两个日期相减(比如 d1-d2=12.3) SELECT (d1-d2)*24*60*60 vaule FROM DUAL; 10.根据差值求新的日期(比如分钟) SQL:select dateadd(mi,8,getdate()) value Oracle:SELECT sysdate+8/60/24 vaule FROM DUAL; 11.当月第一天 SQL:select dateadd(getdate,-day) O: select trunc(sysdate,'mm') from dual; 随机取前 10 条不同的记录 Oracle 有提供一个函数来实现取随机数:DBMS_RANDOM SELECT DBMS_RANDOM.VALUE FROM DUAL; 返回 0--1 之间的随机数,因为 DBMS_RANDOM 是默认使用时钟作为种子,来实现取随机数的。 select * from(select * from hek_test_tb order by dbms_random.value(1,10)) where rownum<10 TRUNC 函数 Oracle 与 SQL SERVER 在日期比较方面有重大区别。 例:含有日期+时间的字段 BEGINDATE 与仅含有日期的字段在比较时 ENDDATE。 SQL SERVER:BEGINDATE<=ENDDATE ORACLE:TRUNC(BEGINDATE,’DD’)<=ENDDATE ORACLE 必须先截断时间,然后再进行比较。如果没有这样做,这将会是一个巨大的 BUG。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 36 修改表的一些常用语法 添加列:alter table hek_test_headers add col_test number; 修改列:alter table hek_test_lines modify litem varchar(40) not null; 删除列:alter table hek_test_lines drop columns col_test; 重命名列:alter table hek_test_lines rename column col_test to col_test2; 添加主键:alter table HEK_TEST_LINES add constraint pk_test primary key (LINEID); 添 加 外 键 : alter table hek_test_lines add constraint fk_test foreign key(hid) references hek_test_headers(hid) 失效主键:alter table hek_test_lines disable constraint pk_test; 失效外键:alter table hek_test_lines disable constraint fk_test; 删除主键:alter table hek_test_lines drop constraint pk_test cascade; 删除外键:alter table HEK_TEST_LINES drop constraint fk_test; 舍入函数 三个舍入函数:round()、floor()、ceil() Round():实现四舍五入,允许设置保留的位数,这个也最常用的四舍五入函数。 Floor():实现取整。一般的程序语言是整数除以整数,返回的仍是整数。PL/SQL 想得比较多。 Ceil():实现近似值。Ceil 会直接近似取整,如果想保留小数,就要自己动手写个函数了。 select 9/4 from dual; select CEIL(9/4) from dual; select round(9/4,0) from dual; 实现类似 BREAK 语句 在没有 LOOP…END LOOP 时,是不能使用 EXIT 的。但可以通过 GOTO 语句实现。 declare t integer; begin t:=&t; DBMS_OUTPUT.PUT_LINE('T='||t); if t=1 then DBMS_OUTPUT.PUT_LINE('Goto!'); goto GOTOS; else DBMS_OUTPUT.PUT_LINE('NO Goto!'); goto NoGoto; end if; <> for i in 1..10 loop DBMS_OUTPUT.put_line('i='||i); end loop test; <> Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 37 NULL; end; Oracle 定时器 ORACLE 9i 及 其 以 前 版 本 , 都 是 使 用 DBMS_JOB 来 实 现 任 务 调 度 。 10g 官 方 推 荐 使 用 DBMS_SCHEDULER。 1.1 JOB 创建 1.1.1 先创建一个存储过程 create or replace procedure usp_test_pr is BEGIN update t_test set tname='test' where tid =110; END; 1.1.2 在 pl/sql developer 中创建 job declare v_job number; begin sys.dbms_job.submit(job=>v_job, what => 'usp_test_pr;', next_date => to_date('22-12-2008', 'dd-mm-yyyy'), interval => 'sysdate+1/1440'); --每隔一分钟执行一次 commit; end; 1.2 删除 JOB exec DBMS_JOB.remove(JOB=>&job_number);--输入 job_number commit; 1.3 查询所有的 JOB select * from dba_jobs j order by j.JOB desc Over 分析查询 Over 函数,其实也可以转换成嵌套查询来实现。 准备测试数据 create table t_test( tid int, tname varchar2(20), tsalary number(8,2), tdeptno int, primary key(tid) ); begin insert into t_test values(1,'小王',4500.21,3); insert into t_test values(2,'小张',4200,3); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 38 insert into t_test values(3,'小 K',3000,3); insert into t_test values(4,'小 Q',8500.5,4); insert into t_test values(5,'小 T',1520.5,4); insert into t_test values(6,'小丁',3000,5); insert into t_test values(7,'小李',3000,5); insert into t_test values(8,'小 KK',3000,5); end; SELECT * FROM T_TEST; ----------------------------------------------------------------------------------------------- 1 1 小王 4500.21 3 2 2 小张 4200.00 3 3 3 小 K 3000.00 3 4 4 小 Q 8500.50 4 5 5 小 T 1520.50 4 6 6 小丁 3000.00 5 7 7 小李 3000.00 5 8 8 小 KK 3000.00 5 --求员工工资占该部门总工资额的比率 select tname, tsalary,tsalary/sum(tsalary)over(partition by tdeptno order by tdeptno) per from t_test --partition by 实现记录分组,order by 实现组内排序后连接求和 ------------------------------------------------------------------------------------------- 1 小王 4500.21 0.38462642978203 2 小张 4200.00 0.358967915960483 3 小 K 3000.00 0.256405654257488 4 小 Q 8500.50 0.848268635864684 5 小 T 1520.50 0.151731364135316 6 小丁 3000.00 0.333333333333333 7 小李 3000.00 0.333333333333333 8 小 KK 3000.00 0.333333333333333 --当然也可以不使用 over,实现同样的效果 select a.tname,a.tsalary,a.tsalary/b.ttl per,a.tdeptno from t_test a,(select tdeptno,sum(tsalary) ttl from t_test group by tdeptno) b where a.tdeptno=b.tdeptno ------------------------------------------------------------------------------------------------------- 1 小王 4500.21 0.38462642978203 3 2 小张 4200.00 0.358967915960483 3 3 小 K 3000.00 0.256405654257488 3 4 小 Q 8500.50 0.848268635864684 4 5 小 T 1520.50 0.151731364135316 4 6 小丁 3000.00 0.333333333333333 5 7 小李 3000.00 0.333333333333333 5 8 小 KK 3000.00 0.333333333333333 5 --求工资排名 SELECT ROWNUM ser ,TNAME,TSALARY,TDEPTNO Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 39 FROM(SELECT * FROM t_test order by tsalary desc) --注意这样求出的排名,有点问题。就是工资一样的没有处于第一排名,这是由 rownum 的性质决定。 ---------------------------------------------------------------------------------------------------- 1 小 Q 8500.50 4 2 小王 4500.21 3 3 小张 4200.00 3 4 小 K 3000.00 3 5 小 KK 3000.00 5 6 小李 3000.00 5 7 小丁 3000.00 5 8 小 T 1520.50 4 --要实现真正的排名,应该使用 rank 或者 dense_rank -- rank()和 dense_rank()的区别是: --rank()是跳跃排序,有两个第二名时接下来就是第四名 --dense_rank()l 是连续排序,有两个第二名时仍然跟着第三名 select dense_rank()over(order by tsalary desc) ser,tname,tsalary,tdeptno from t_test ------------------------------------------------------------------------ 1 小 Q 8500.50 4 2 小王 4500.21 3 3 小张 4200.00 3 4 小 K 3000.00 3 4 小 KK 3000.00 5 4 小李 3000.00 5 4 小丁 3000.00 5 5 小 T 1520.50 4 --上面是工资在全体部门的排名,如果要求部门排名的话。 select tname,tsalary,tdeptno,dense_rank()over(partition by tdeptno order by tsalary desc ) ser from t_test ------------------------------------------------------------------------ 1 小王 4500.21 3 1 2 小张 4200.00 3 2 3 小 K 3000.00 3 3 4 小 Q 8500.50 4 1 5 小 T 1520.50 4 2 6 小丁 3000.00 5 1 7 小李 3000.00 5 1 8 小 KK 3000.00 5 1 --直接实现行汇总 select tname,tsalary,tdeptno,sum(tsalary)over(partition by null) ttl from t_test 如下: 1 小王 4500.21 3 30721.21 2 小张 4200.00 3 30721.21 3 小 K 3000.00 3 30721.21 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 40 4 小 Q 8500.50 4 30721.21 5 小 T 1520.50 4 30721.21 6 小丁 3000.00 5 30721.21 7 小李 3000.00 5 30721.21 8 小 KK 3000.00 5 30721.21 Oracle 层次树查询 Oracle 层次树是通过 Connect by [条件] Start with [条件] 来实现。这一功能非常好用,比如 ERP 中 的 BOM、HR 中的组织架构,就算是这类的典型应用了。不过,Oracle EBS11i 中好象没实现,BOM 也没 有使用树这种组件。 下面就做一个简单的 MRP 试算过程,来说明层次树的应用。 1.1 建一个简单 BOM 表。 create table hek_bom( master_id varchar2(20), master_name varchar2(50), sub_id varchar(20), sub_name varchar(20) ) 1.2 放入测试数据。 begin insert into hek_bom values('0001','V1 卡车','10001','V1 发动机'); insert into hek_bom values('0001','V1 卡车','10002','V1 车架'); insert into hek_bom values('0001','V1 卡车','10003','V1 车轮'); insert into hek_bom values('0002','V2 卡车','10001','V1 发动机'); insert into hek_bom values('0002','V2 卡车','10002','V1 车轮'); insert into hek_bom values('0002','V2 卡车','10004','V2 车架'); insert into hek_bom values('0003','V3 卡车','10004','V1 发动机'); insert into hek_bom values('0004','V4 卡车','10005','V2 发动机'); insert into hek_bom values('10001','V1 发动机','10006','V1 活塞'); insert into hek_bom values('10001','V1 发动机','10007','V1 火花器'); insert into hek_bom values('10007','V1 活塞','10008','V1 橡胶片'); insert into hek_bom values('10007','V1 活塞','10009','V1 螺丝'); end; 1.3 查询一下明细: select t.* from hek_bom t for update ------------------------ 1 0001 V1 卡车 10001 V1 发动机 2 0001 V1 卡车 10002 V1 车架 3 0001 V1 卡车 10003 V1 车轮 4 0002 V2 卡车 10001 V1 发动机 5 0002 V2 卡车 10002 V1 车轮 6 0002 V2 卡车 10004 V2 车架 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 41 7 0003 V3 卡车 10004 V1 发动机 8 0004 V4 卡车 10005 V2 发动机 9 10001 V1 发动机 10006 V1 活塞 10 10001 V1 发动机 10007 V1 火花器 11 10006 V1 活塞 10008 V1 橡胶片 12 10006 V1 活塞 10009 V1 螺丝 1.4 问题:求 V1 螺丝料品有哪几层产品用到。 select level,t.* from hek_bom t connect by prior t.master_id=t.sub_id start with t.sub_id='10009' --注意这条 SQL 语名的语法,connect by prior t.master_id=t.sub_id 表示优先从子节点到父节点。 --start with t.sub_id='10009'相当于 where t.sub_id='10009' --------------------------------------------- 1 10006 V1 活塞 10009 V1 螺丝 2 10001 V1 发动机 10006 V1 活塞 3 0001 V1 卡车 10001 V1 发动机 4 0002 V2 卡车 10001 V1 发动机 1.5 问题:求:V1 卡车的 BOM 结构: select level,t.* from hek_bom t connect by prior t.sub_id=t.master_id start with t.master_name='V1 卡车' --connect by prior t.sub_id=t.master_id 表示优先从父节点查询到子节点。 --------------------------------------------------------------------------------------------- 1 0001 V1 卡车 10001 V1 发动机 2 10001 V1 发动机 10006 V1 活塞 3 10006 V1 活塞 10008 V1 橡胶片 4 10006 V1 活塞 10009 V1 螺丝 5 10001 V1 发动机 10007 V1 火花器 6 0001 V1 卡车 10002 V1 车架 7 0001 V1 卡车 10003 V1 车轮 ――――――――――――――――――――――――――――――――――――― 通过这两个例子,已经可以很形象地说明 connect by 的典型应用了。 Merge into 应用 Merge into 适用于数据量非常大的表,做 insert\update 动作。比起 insert into select 效率上要更高些。当然 merge into 也提供了 when matched then 的条件规范。 基本语法: Truncate table 1.Truncate table 与 delete * from table 作用是一样,都是删除表中全部数据。但 Delete 是与事务关联的,所 以 Truncate table 会快很多。另外 Oracle 书上说,truncate 会把 highwatermark 回归至 0 ,当下一次再插 入新资料时就会快一些。 需要注意的是 Truncate table 不是 PL/SQL 直接使用,必须使用动态 SQL 来执行,并且 truncate table 是无须 commit 语句的。 begin Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 42 execute immediate 'truncate table hek_table'; end; 2.Truncate Table 的权限:必须是表的所有者,或具 drop any table 权限的用户。 绑定变量与非绑定变量之比较 1、以 insert 20 条记录为测试用例。数据库:Oracle9iR2、Oracle10gR2。操作系统:Linux。 表结构:heap 表,建有主键 create table hek_heap ( col1 int , col2 char(30) ) organization heap; 绑定变量: declare v_name varchar(10) :='测试数据'; v_num number; v_num1 number; v_num2 number; begin execute immediate 'truncate table hek_index'; dbms_output.put_line('绑定变量测试用例'); v_num1 :=dbms_utility.get_time(); dbms_output.put_line('开始时间:'||to_char(sysdate,'yyyy-mm-dd hh:mm:ss')); for i in 1..200000 loop execute immediate 'insert into hek_index values(:x,:y )' using i,v_name; end loop; commit; v_num2 :=dbms_utility.get_time(); dbms_output.put_line('结束时间:'||to_char(sysdate,'yyyy-mm-dd hh:mm:ss')); v_num := v_num2-v_num1; dbms_output.put_line('用时:'||v_num||' 毫秒'); end; 结果: 绑定变量测试用例 开始时间:2009-06-27 11:06:54 结束时间:2009-06-27 11:06:07 用时:1322 毫秒 非绑定变量: declare v_name varchar(10) :='测试数据'; v_num number; v_num1 number; v_num2 number; begin Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 43 execute immediate 'truncate table hek_index'; dbms_output.put_line('非绑定变量测试用例'); v_num1 :=dbms_utility.get_time(); dbms_output.put_line('开始时间:'||to_char(sysdate,'yyyy-mm-dd hh:mm:ss')); for i in 1..200000 loop execute immediate 'insert into hek_index values('||i||',''||v_name||'')'; end loop; commit; v_num2 :=dbms_utility.get_time(); dbms_output.put_line('结束时间:'||to_char(sysdate,'yyyy-mm-dd hh:mm:ss')); v_num := v_num2-v_num1; dbms_output.put_line('用时:'||v_num||' 毫秒'); end; 结果: 非绑定变量测试用例 开始时间:2009-06-27 11:06:19 结束时间:2009-06-27 11:06:39 用时:7917 毫秒 静态SQL语句 declare v_name varchar(10) :='测试数据'; v_num number; v_num1 number; v_num2 number; begin execute immediate 'truncate table hek_index'; dbms_output.put_line('静态SQL用例'); v_num1 :=dbms_utility.get_time(); dbms_output.put_line('开始时间:'||to_char(sysdate,'yyyy-mm-dd hh:mm:ss')); for i in 1..200000 loop insert into hek_index values(i,v_name); end loop; commit; v_num2 :=dbms_utility.get_time(); dbms_output.put_line('结束时间:'||to_char(sysdate,'yyyy-mm-dd hh:mm:ss')); v_num := v_num2-v_num1; dbms_output.put_line('用时:'||v_num||' 毫秒'); end; 直接书写SQL语句用例 开始时间:2009-06-27 11:06:46 结束时间:2009-06-27 11:06:57 用时:1094 毫秒 结论:绑定与不绑定变量在动态SQL时,用时差别非常大。但静态SQL与绑定变量SQL,由于动态SQL的 执行时编译,所以会占有一些编译时间,用时也略大于静态SQL。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 44 取不重复的记录行 表结构:table ID NAME 110 AA 120 AA 125 BB 126 BB 129 CC 取得如下记录: 110 AA 125 BB 129 CC 如果是 MySQL 实现这个比较简单。因为 MySQL Group By 支持多字段。 Select ID,NAME from TABLE group by NAME 但 Oracle 不支持此用法。 可以做到的,目前只有 Select NAME,MAX(ID) FROM TABLE GROUP BY NAME 查询具有相同薪水的职工 表结构: create table emp(emp_id int,dept_id int,commission number); 测试数据: insert into emp values(1,2,11.22); insert into emp values(2,3,12.22); insert into emp values(3,4,12.22); insert into emp values(4,5,13.22); 查询薪水相同的职工: select e.* from emp e, emp m where e.emp_id <> m.emp_id and e.commission = m.commission 统计 EBS 表数量及 Oracle 概念 转到 Oracle ERP 有段时间了,突然被一个问题问倒。Oracle ERP 有多少张表?统计表的数量首先想到数据 字典,可是 select 了半天,发现对一些概念有点乱。特总结如下: 首先,要明白几个 Oracle 核心概念。这些概念 2 年前在读《Oracle8i 初学者指南》中明白了一些,无耐后 来转做 JAVA 开发,这些东西又还给作者了^_^。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 45 “解释数据库、表空间、数据文件、表、数据的最好办法是想象一个装满东西的柜子。数据库就是柜子, 柜中的抽屉是表空间,抽屉中的文件夹是数据文件,文件夹中的纸是表,写在纸上的信息就是数据”。 这 是《Oracle8i 初学者指南》关于这些概念的理解。这样理解并没有错,但事实远非如此简单。 1.核心概念:数据库(Scheme)、数据文件、表空间(Tablespace)、表(table)、用户(user)。 1.1 数据库是数据文件、控制文件、日志文件等组成的。属于物理文件的范畴。一个 Scheme 可以多个 Tablespace。对于不同的应用,Oracle 也推荐创建不同的表空间,以提高性能。例:DBA 备份时可以按 表空间进行备份。 1.2 表空间是逻辑文件的范畴,Scheme 是通过 Tablespace 进行管理的。这也是 Oracle 与 SQL SERVER 在设 计理念上的最大区别。一个表空间一般只对应一个数据文件,但也可以有多个数据文件。例:给表空 间增容。 1.3 表也是逻辑文件的范畴。一个表空间可以多张表,一张表一般只能属于一个表空间。当然 Oracle 也有 提供分区表,来实现一张表存放在多个表空间中。 1.4 一张表只能属于一个用户。即用户与表是 1 对多的关系。Oracle 是通过 synonym 来实现不同用户创建 的表进行互访的。 1.5 一个用户通过配额(Quota)可以拥有多个表空间,但只能有一个默认的表空间。 引申一下数据字典的概念,数据字典就是存放 Oracle 数据对象的表。一般是通过数据字典视图来查询,有 三种视图:user_、all_、dba_。 2.查看全部表的数量 select count(*) from dba_tables -–因为有做过大量的二次开发,所以直接这样得到的数量是不准确 的。 3.查看当前用户所有表的数量 select count(*) from all_tables 4.查看当前用户创建表的数量 select count(*) from user_tables 5.ebs11i 对应的表空间是 APPS_TS_TX_DATA,通过对表空间进行表的统计,算是比较准确的。 select count(*) from dba_tables dt where dt.tablespace_name='APPS_TS_TX_DATA' 得到 EBS 表的数量为“15997”。 Oracle 死锁之解决方法 1. 解决 ORA-04021 错误,查看 EBS 死锁的进程。 SELECT c.owner ,c.object_name ,c.object_type ,fu.user_name locking_fnd_user_name ,fl.start_time locking_fnd_user_login_time ,vs.module ,vs.machine ,vs.osuser ,vlocked.oracle_username ,vs.SID ,vp.pid Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 46 ,vp.spid AS os_process ,vs.serial# ,vs.status ,vs.saddr ,vs.audsid ,vs.process FROM fnd_logins fl,fnd_user fu,v$locked_object vlocked,v$process vp ,v$session vs,dba_objects c WHERE vs.SID = vlocked.session_id AND vlocked.object_id = c.object_id AND vs.paddr = vp.addr AND vp.spid = fl.process_spid(+) AND vp.pid = fl.pid(+) AND fl.user_id = fu.user_id(+) --AND c.object_name LIKE '%' || UPPER('&tab_name_leaveblank4all') || '%' AND NVL(vs.status ,'XX') != 'KILLED'; 或者: select b.sid,b.serial#,b.machine,b.terminal,b.program,b.process,b.status from v$lock a , v$session b where a.SID = b.SID 2.Kill 掉进行程 alter system kill session 'SID,serial#'; —SID、serial# 这两个参数,可以从上面的查询结果中找到。 PL/SQL 过程调用表示法 PL/SQL 过程调用支持两种参数表示方式,一种是位置表示法,如 C、JAVA 都是使用这种方式。第二 种是名称表示法,这个恐怕只有 PL/SQL 使用,当然 PL/SQL 也是从 Ada 那边学来的。 名称表示法示例(注:仅限于过程,名称表示法不适用于函数。切记!): 创建存储过程: create or replace procedure test_proc(i_temp number,j_temp number,c_temp varchar) is t varchar(30); begin t := i_temp || '' || j_temp || c_temp; dbms_output.put_line(t); end; 调用: begin test_proc(j_temp=>10,i_temp => 60,c_temp => 'test'); end; PL/SQL 调用 Java 类 作为 JAVA 程序员,对 ORACLE 的 javasource 一直是想找个机会下手的。呵呵!风高月夜,此时正下 手之机。Oracle 有提供一个叫“外部例程”来实现与其他编程语言的访问,但 ORACLE 特别集成了 JAVA。 例如:可以将业务逻辑写在 JAVA 类中,通 PL SQL 进行调用,从而实现了业务逻辑层与数据库分离。 1.HelloWorld 例子 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 47 1.0 创建 java source create or replace and compile java source named JTest as public class JTest { public static String entry(String s,int i) { return “First “+i+“ hello“+s; } } 1.1 创建调用 function create or replace function read_string (t in varchar2,i number) return varchar2 is language java name ' JTest.entry(java.lang.String,int) return t'; --注:function 的形参应与调用的 JAVA 类中方法的形参相同 1.2 调用测试 SELECT read_string(' world',10) from dual 注意:在 ORACLE 中,JAVA SOURCE 可以拥有非 static 方法,但 PLSQ/包调用的方法必须是 static 的。 2.再来一个实用一点的例子(JDBC) 2.1 创建 JAVA 类 create or replace and compile java source named testjdbc as import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author jarwang * @since 2008-6-12 * @version xx1.0 */ public class TestJDBC{ public static String queryTable(){ String temp=““; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try{ conn = getConn(); pstmt = conn.prepareStatement(“select hname from test “); rs = pstmt.executeQuery(); while(rs.next()){ temp += rs.getString(“hname“); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 48 } }catch(Exception ioex){ System.out.println(“failure: “+ioex); }finally{ closeConn(conn,pstmt,rs); } return temp; } public static void insertTable(String s){ Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = “insert into test (hname)values('“+s+“')“; try{ conn = getConn(); pstmt = conn.prepareStatement(sql); pstmt.execute(); conn.commit(); }catch(Exception oex){ System.out.println(“ has failure: “+oex); try{ conn.rollback(); }catch(Exception ex){ ex.printStackTrace(); } }finally{ try{ pstmt.close(); conn.close(); }catch(Exception ex){ ex.printStackTrace(); } } } public static Connection getConn(){ Connection conn = null; String user = ““; String pwd = ““; String url = “jdbc:oracle:thin:@ip:sid“; try{ Class.forName(“oracle.jdbc.driver.OracleDriver“); conn = DriverManager.getConnection(url,user,pwd); conn.setAutoCommit(false); System.out.println(“ OPEN DATABASE SUCCESS “); }catch(SQLException ex){ System.out.println(“ OPEN DATABASE FAILURE: “+ex); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 49 2.2 创建 PL/SQL 包 2.3 测试调用 3.调用含有外部的 jar 的 JAVA 类 3.1 在 ORACLE 中加载 JAR 包。 3.1.1 上传 JAVA 类 上传 jar 包至 ORACLE 服务器。例:/data/test/test.jar 3.1.2 注册 JAVA 类 }catch(Exception e){ System.out.println(“JDBC CLASS LOADE FAILURE: “+e); } return conn; } public static void closeConn(Connection conn,PreparedStatement pstmt,ResultSet rs){ try{ rs.close(); pstmt.close(); conn.close(); }catch(Exception e){ e.printStackTrace(); } } } create or replace package TestJDBC is -- Author : JARWANG -- Created : 2008-6-12 10:24:51 -- Purpose : TestJDBC procedure insert_table(t varchar2); function query_table return varchar2; end TestJDBC; create or replace package body TestJDBC is procedure insert_table(t varchar2) is language java name 'TestJDBC.insertTable(java.lang.String)'; function query_table return varchar2 is language java name 'TestJDBC.queryTable() return String'; end TestJDBC; declare t varchar2(3000); begin TestJDBC.insert_table('JDBC 例子'); t := TestJDBC.query_table(); dbms_output.put_line('t='||t); end; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 50 3.1.3 查询是否注册成功 3.1.4 删 除 注 册 的 JAVA 类 PL/SQL 操作 EXCEL PL/SQL 操作 EXCEL 有多种方法,但都不理想。比较了一下,还是通过 JAVA 的方法比较适用。 1. 使用 UTL_FILE 包 declare l_file utl_file.file_type; BEGIN l_file :=utl_file.fopen('U_FIEL','test1.xls','w'); utl_file.put_line(l_file,'jobs 表导出数据'); utl_file.fflush(l_file); end; 说明:此方法操作文本尚可,对于 EXCEL 这种 COM 文件并不可行。网络大多数所谓的 PL/SQL 操作 EXCEL, 也是使用 cvs 这种简单的格式。如果要读入稍微复杂点的 EXCLE 模板文件,utl_file 就挂掉了。 2. 使用 OLE2 declare application OLE2.OBJ_TYPE; workbooks OLE2.Obj_Type; workbook OLE2.Obj_Type; worksheets OLE2.Obj_Type; worksheet OLE2.Obj_Type; args OLE2.List_Type; cell OLE2.Obj_Type; begin application:=OLE2.CREATE_OBJ('Excel.Application'); OLE2.Set_Property(application,'Visible','True'); workbooks := OLE2.GET_OBJ_PROPERTY(application,'Workbooks'); Select OBJECT_NAME, OBJECT_TYPE, STATUS, SYS.DBMS_JAVA.LONGNAME(OBJECT_NAME) LONGNAME, LAST_DDL_TIME, OBJECT_ID, uo.created from sys.user_objects uo where object_type in ('JAVA CLASS', 'JAVA SOURCE', 'JAVA RESOURCE', 'JAVA DATA') order by uo.created desc CALL DBMS_JAVA.loadjava('/data/test/test.jar'); CALL dbm Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 51 workbook:=OLE2.INVOKE_OBJ(workbooks,'Add'); worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets'); worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add'); args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, 4); OLE2.ADD_ARG(args, 2); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.DESTROY_ARGLIST(args); OLE2.Set_Property(cell, 'Value', 'Hello Excel!'); args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, 5); OLE2.ADD_ARG(args, 3); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.DESTROY_ARGLIST(args); OLE2.Set_Property(cell, 'Value', 'Hello Excel!'); exception when others then FND_MESSAGE.DEBUG('ERROR:'||SQLERRM); end; 说明:Object Link and embed,有学过 VB/VC 的同学们应该很熟悉。可惜 ORACLE 本身并不支持 OLE2,OLE2 是 ORACLE FORMS 集成的一个 OLE 接口。所以限制此方法的使用范围。另外 更重要的 OLE2 无法在 B/S 架构中使用,只能在传统的 FORM C/S 程序中使用。 3. 使用 ORACLE COM 函数 这种方法就与通过 extproc 来实现与 excle 通信,这种方法直接与 window api 打交道。大致的的代码 是:ordcom.CreateObject(’Excel.Application’, 0, servername,applicationToken); 测试了一下,似乎只能要求 ORACLE 的宿主系统必须是 window。这也就有严重的局限性了。 4. 使用 JAVA 存储过程(通过 POI 包,这也是本文的重点) 4.1 至 http://poi.apache.org 下载 POI 包,注意与 ORACLE JVM 版本匹配(旧版本的 POI 下载:http: //archive.apache.org/dist/jakarta/poi/release/bin/)。至 ORACL 官网下载 JDBC 驱动,这一步不是 必须的,一般安装 ORACLE 时,都有内置了 JDBC 包。 4.2 在 ORACLE 注册 POI。 4.2.1 先将 POI 包上传至 ORACLE 服务器。目录为:$ORACLE_HOME/javavm/lib ,例:/ data/book/bookdb/9.2.0/javavm/lib,必须是 javavm/lib 这个目录或其下层目录。 4.2.2 使用 DBMS_JAVA 注册 call dbms_java.loadjava('-r -v -definer -g public /data/book/bookdb/9.2.0/javavm/lib/poi-2.5.1-final-20040804.jar'); call dbms_java.loadjava('-r -v -definer -g public /data/book/bookdb/9.2.0/javavm/lib/poi-contrib-2.5.1-final-20040804.jar'); call dbms_java.loadjava('-r -v -definer -g public /data/book/bookdb/9.2.0/javavm/lib/poi-scratchpad-2.5.1-final-20040804.jar'); 注:这个问题折腾了我很久,注册 JAR 包有两种方法。1.使用 DBMS_JAVA,这种方法要自行上 传jar包到服务器上。2.是使用loadjava。这也是能google到最多的方法,但loadjava在ORACLE9i 以后就不单独提供了,而是集成在 JDeveloper 中,而可恶的 JDeveloper 是有版本兼容性的问题。 4.2.3 查询一下是否注册成功(注:如果注册成功,STATUS 应该为 VALID)。 Select OBJECT_NAME, OBJECT_TYPE, Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 52 STATUS, SYS.DBMS_JAVA.LONGNAME(OBJECT_NAME) LONGNAME, LAST_DDL_TIME, OBJECT_ID, uo.created from sys.user_objects uo where object_type in ('JAVA CLASS', 'JAVA SOURCE', 'JAVA RESOURCE', 'JAVA DATA') and uo.object_name like '%poi%' 4.3 创建 JAVA SOURCE create or replace and compile java source named testjdbc as import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.*; import java.io.*; public class TestJDBC{ public static void exportExcel(){ try{ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(“new sheet“); wb.setSheetName( 0, “SomeUnicodeName“, HSSFWorkbook.ENCODING_UTF_16 ); HSSFRow r = sheet.createRow( 0 ); HSSFCell c = r.createCell( (short)0 ); c.setCellValue(“X 中国“); // Write the output to a file FileOutputStream fileOut = new FileOutputStream(“/data/book/k.xls“); wb.write(fileOut); fileOut.close(); }catch(Exception e){ System.out.println(“poi error :“+e); e.printStackTrace(); } } } 4.4 创建调用的 PL/SQL 包 create or replace package TestJDBC is procedure exportExcel; procedure insert_table(t varchar2); function query_table return varchar2; end TestJDBC; create or replace package body TestJDBC is procedure exportExcel is Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 53 language java name 'TestJDBC.exportExcel()'; end TestJDBC; 4.5 测试 PL/SQL 块 begin testjdbc.exportExcel; end; 5. 使用其他编程语言操作 说明:此方法完全脱离了 ORACLE,已超出了 ORACLE 的范围,本文不讨论。 PL/SQL 异常机制 Exception,一些中文教材译为例外,我认为这种翻译很失水准。Exception 大多数的程序语言都这个捕 获机制,但基本都是译为异常。为什么在 PL/SQL 中就搞成“例外”. 一般的 PL 程序员都这样写异常处理: Declare …… Begin …… Exception When others then raise_application_error(-20000,’has an error’||sqlerrm); End; ORACLE 有提供两个函数来实现捕获异常信息,SQLCODE:错误代码、SQLERRM:错误描述。事 实上,上述写法主要是方便,也没有根本性的错误。但 PL/SQL 与 JAVA 相似,异常捕获也是有层次的。 OTHERS 会捕获到 ORACLE 全部的异常,这对于大型数据库而言,也是会影响到效率的。 实现自定义异常。 Declare i number:=1; Cu_ex Exception; Begin insert into test_table values(2,22); If (i=1) then Raise Cu_ex; End if; DBMS_OUTPUT.put_line('TEST'); Exception When Cu_ex then Raise_application_error(-20000,'has a customer error'||sqlerrm); When others then Raise_application_error(-20001,'has an error'||sqlerrm); End; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 54 这样如果发生自定异常,就不会执行 when others then 异常处理语句了。在 PL/SQL 如果没有定义异常, 那么就相当于 JAVA 中抛出异常 THROWS,抛出的异常由调用该块的 PL/SQL 程序负责处理。 另外,异常与事务,ORACLE 的书上都只说异常是不会中止事务。但拿上面那段代码去测试,就会异 常发生时事务是有回滚的。那么是否说明异常不会中止事务的说法不正确的? 再测试下面这段代码,异常就不会中止事务了。 BEGIN Declare i number:=1; Cu_ex Exception; Begin insert into test_table values(2,22); If (i=1) then Raise Cu_ex; End if; DBMS_OUTPUT.put_line('TEST'); commit; Exception When Cu_ex then Raise_application_error(-20000,'has a customer error'||sqlerrm); When others then Raise_application_error(-20001,'has an error'||sqlerrm); End; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('error!'); NULL; END; 原因:Raise_application_error(-20000,'has a customer error'||sqlerrm)这段语句会再次抛出异常。但在外层 块中将这个异常捕获到,而没有抛出。这样异常不会传回调用环境,事务也就不会由中止。 总结:异常捕获后再抛出,则事务会回传给调用环境,由服务器自动进行中止事务。但如果捕获异常 后,没有抛出,则不会中止事务,这样就发生异常了。比较好的做法是在异常处理代码中,进行 rollback 中止事务。 PL/SQL 面向对象编程 自转到 ORACLE 开始,就开始对 ORACLE OOP 有非常高的热情,于是写了一个 DEMO。先从创建 TYPE 开始,TYPE 与 PACKAGE 非常相似。TYPE 与 JAVA 中的 CLASS 可以等同。 1、 创建 TYPE 规范 create or replace type hek_send_mail_obj as object( mail_host varchar2(20), mail_port integer, member function test_f(i number) return varchar2 )instantiable Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 55 not final; 2、创建 TYPE 主体 create or replace type body hek_send_mail_obj as member function test_f(i number) return varchar2 is begin return 'success visit ' ||i||' ' || self.mail_host||' '||self.mail_port; end; end; 3、进行调用 Declare --有多少个数据成员,在实例化 TYPE 时,必须都初始化,这一点跟 JAVA 的构造器类似,只是 JAVA 没有强制要求。 t hek_send_mail_obj:= hek_send_mail_obj(' 192.168.1.110',44); begin --调用 TYPE 的方法 test_f dbms_output.put_line(t.test_f(1)); end; 注:上面创建的 TYPE 都是临时对象,随着块的结束,对象生命也结束了。下面将会说明如何创建持久对 象。 4、持久化 TYPE(也就是创建对象表) create table hek_send_mail_tbj of hek_send_mail_obj; 5、在对象表中 INSERT 些数据 insert into hek_send_mail_tbj values('192.168.210.232',25); insert into hek_send_mail_tbj values('192.168.210.232',80); insert into hek_send_mail_tbj values('192.168.210.232',35); 6、进行调用 select tb.mail_host,tb.mail_port,tb.test_f(2) from hek_send_mail_tbj tb 注:①这边我们可以直接象关系表一样使用对象表,也不必进行初始化。ORACLE 会自动初始化对象。 ②TYPE 不能使用 PACKAGE 的对象等等,如:UTL_SMTP。 PL/SQL 实现字符串转成数组 这个功能在 JAVA 中比较简单,String 有提供一个 split 的方法实现此功能。在 PL/SQL 其实也不难,用 substr 与 instr 联合使用就可以实现。 function varchar2_to_array(ls varchar2) return list_table is Type list_table is table of varchar2(200); tl list_table := list_table(NULL); i integer :=0; j integer :=0; v_ls varchar2(1000); begin i := instr(ls,','); if i=0 then tl(1) := ls; goto ex; else Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 56 v_ls :=ls; end if; while i>0 loop tl(j) := substr(v_ls,1,i-1); v_ls := substr(v_ls,i+1,length(v_ls)); i := instr(v_ls,','); tl.extend; if i=0 then j := j+1; tl(j) := v_ls; goto ex; end if; end loop; <> return tl;end varchar2_to_array; PowerDesigner PowerDesigner一款非常好的数据库建模工具。。如果要用UML之类面向对象的东东,可以使用Rational Rose。当然也可以考虑直接使用 JDeveloper10g 以上版本,UML 工具在 JDeveloper 已经有集成了。只是在 ORACLE ERP 很少用到面向对象,所以首推 powerdesigner。PowerDesigner 功能比 Oracle Designer 强大多 了。 1.从代码反向生成 E-R 视图 2. Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 57 ORA-01403 未找到数据之解决方法 这个问题在大家遇到,如查询出某个值,然后赋给变量。例: declare v_d date; begin select sysdate into v_d from dual where 1 = 2; end; 上述代码就报异常了。首先,我们想到使用 exception。例: declare v_d date; begin select sysdate into v_d from dual where 1 = 2; exception when no_data_found then dbms_output.put_line('has error'); end; 但问题是我们并不想程序就这样中止执行。当然,也可以将功能代码直接在 exception,但这样的 code 相当不合格,review 时候肯定通不过。 于是有了改进。例: declare v_d date; v_count number; begin select count(sysdate) into v_count from dual where 1 = 2; if v_count >0 then select sysdate into v_d from dual where 1 = 2; end if; end; 似乎问题已解决了,但如果上述代码条件很多的话,会乱成代码迅速膨胀。再改进一下,例: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 58 declare v_d date; begin select (select nvl(sysdate, '') from dual where 1 = 2) into v_d from dual; end; 这下,比较完美了吧。^_^ PL/SQL 细粒度事务控制 有时候,我们需要更为细粒的事务控制。例:成批导入 10 万条数据。一般的写法是: declare t number :=0; begin insert into test values(200); insert into test values(201); --更多数据 commit; exception when others then rollback; dbms_output.put_line('has an error:'||sqlerrm); end; 但这有一个致命问题,如果此时数据导到 99999 时,发生错误,导致事务 rollback。显然这是我们不 愿意看到的,为了一条记录,导致全部的记录无法成功 commit。呵呵!使用函数 SQL%ROWCOUNT 可以 达到我们的目的。 declare t number :=0; begin insert into test values(200); t := SQL%ROWCOUNT; dbms_output.put_line('t='||t); if t>0 then commit; else rollback; end if; insert into test values(201); --更多数据 t := SQL%ROWCOUNT; dbms_output.put_line('t='||t); if t>0 then commit; else rollback; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 59 end if; end; 这只是一个简单的例子,具体的实现可根据业务要求来做。 物化视图 在提升性能的开发方面,有二个方法。1、使用中间表+JOB 定时更新。2、使用物化视图。 采用中间表,没什么可说的。无非就是在原来上作分类汇总等操作,然后 insert 到另一张表中,最后写个 JOB 来定时更新。采用物化视图呢?这个以前接触比较少,也比较感兴趣。开始 materialized view 之旅。 1.1 建表 create table T_TEST ( TID INTEGER primary key, TNAME VARCHAR2(20), TSALARY NUMBER(8,2), TDEPTNO INTEGER ) 1.2 测试数据 BEGIN FOR I IN 1..1000 LOOP insert into t_test values(i+200,'小王'||i,500.5+i,i); END LOOP; END; 1.3 创建物化视图日志 CREATE MATERIALIZED VIEW LOG ON t_test WITH PRIMARY KEY, ROWID; --注意:物化视图日志的名称为 MLOG$_后面跟基表的名称,本例就是 mlog$t_test 任何物化视图都会包括的列: SNAPTIME$$:用于表示刷新时间。 DMLTYPE$$:用于表示 DML 操作类型,I 表示 INSERT,D 表示 DELETE,U 表示 UPDATE。 OLD_NEW$$:用于表示这个值是新值还是旧值。N(EW)表示新值,O(LD)表示旧值,U 表示 UPDATE 操作。 CHANGE_VECTOR$$表示修改矢量,用来表示被修改的是哪个或哪几个字段。 如果 WITH 后面跟了 ROWID,则物化视图日志中会包含: M_ROW$$:用来存储发生变化的记录的 ROWID。 如果 WITH 后面跟了 PRIMARY KEY,则物化视图日志中会包含主键列。 如果 WITH 后面跟了 OBJECT ID,则物化视图日志中会包含: SYS_NC_OID$:用来记录每个变化对象的对象 ID。 如果 WITH 后面跟了 SEQUENCE,则物化视图日子中会包含: SEQUENCE$$:给每个操作一个 SEQUENCE 号,从而保证刷新时按照顺序进行刷新。 如果 WITH 后面跟了一个或多个 COLUMN 名称,则物化视图日志中会包含这些列 1.4 创建物化视图 物化视图的刷新方式有三种:COMPLETE、FAST 和 FORCE。下面 fast 为例: CREATE MATERIALIZED VIEW mv_test_pk --如果可以快速刷新则进行快速刷新,否则完全刷新 refresh force on demand REFRESH FAST on demand Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 60 start with sysdate --第一次刷新时间 next TRUNC(SYSDATE+1)+18/24 --刷新时间间隔 AS select * from t_test; 也可以手动刷新物化视图:dbms_mview('mv_emp_pk'); 通过游标更新数据 declare cursor cur_list(c_header_id number) is select COL1,COL2,COL3 from HEK_TABLE ll where ll.col3=c_header_id for update of ll.COL1,COL2; begin for l_list in cur_list loop update HEK_TABLE ll set ll.col1 = 12,ll.col2=22 --遍历游标的同时,将表 col1,col2 两列的数据也更新了 WHERE CURRENT OF cur_list; end loop; end; 给 PACKAGE 加密 刚接触 EBS 都会知道,有些 package 是加密处理了。这其实是 ORACLE 的标准功能了。 1、首先,写一个创建 package 代码,存为本地 hek_test_pkg.sql 文件。 create or replace package hek_test_pkg is procedure hello_encrypt; end hek_test_pkg; / create or replace package body hek_test_pkg is procedure hello_encrypt is begin dbms_output.put_line('This was encrypted pl/sql package'); end hello_encrypt; end hek_test_pkg; / 2、将hek_test_pkg.sql上述到oracle服务器的随便一个目录(当然,要有权限的目录啦。^_^),并执行: # wrap iname=hek_test_pkg.sql oname=hek_test_pkg.plb 3、用生成的hek_test_pkg.plb文件创建package(注:源代码要保管好,否则加密后无法再修改这个package 了)。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 61 本例是以整个package进行加密。在实际开发过程中,一般是选择只加密package body,而package规范则对 外开放。否则,你整个package都加密了,别人怎么使用呢?^_^ Oracle Advanced Queue AQ官方定义:是 Oracle 数据库的消息队列功能。使用高级队列,就可以如同从 Oracle 数据库中执行 SQL 操 作一样执行消息队列操作。 AQ 的消息队列功能使得 Oracle 数据库的不同应用程序和用户之间能够使用队列进行异步通信。话说从概念 上来理解事物,总是不够自然。以下就以一个示例来说明AQ的简单运用(点对点)。 1. 创建AQ管理员用户并授权 SQL> create user aqadmin identified by “aqadmin“ default tablespace hek SQL> grant resource,connect to aqadmin SQL> grant aq_administrator_role to aqadmin 2.创建AQ普通用户并授权 SQL> create user aquser identified by “aquser“ default tablespace hek SQL> grant create session to aquser SQL> grant aq_user_role to aqadmin SQL>EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege=>'ENQUEUE_ANY',grantee =>'aquser',admin_option=>FALSE); SQL>EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege=>'DEQUEUE_ANY',grantee =>'aquser',admin_option=>FALSE); 3.创建AQ的payload数据类型,当然也可直接使用number、varchar这类数据类型 SQL> conn aqadmin/aqadmin –以 aqadmin 登陆,下同 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 62 SQL>create type queue_message_type as object (num number,title varchar2(30),text varchar2(1000)); SQL> grant execute on queue_message_type to aquser 4.创建queue table SQL>EXEC DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table=> 'aqadmin.obj_qtab',queue_payload_type=> 'aqadmin.queue_message_type'); 5.创建并启动queue SQL>EXEC DBMS_AQADM.CREATE_QUEUE(queue_name=>'aqadmin.obj_queue',queue_table=>'aqadmin.obj_qtab'); EXEC DBMS_AQADM.START_QUEUE (queue_name=>'aqadmin.obj_queue'); 6.入队enqueue(以aquser用户登录,下同。) DECLARE queue_options DBMS_AQ.enqueue_options_t; msg_properties DBMS_AQ.message_properties_t; msg_id RAW(16); msg_text aqadmin.queue_message_type; BEGIN msg_text := aqadmin.queue_message_type(1, 'This is a title', 'Hello,Advanced Queuq' || to_char(SYSDATE, 'yyyy-mm-dd HH24:MI:SS')); DBMS_AQ.enqueue(queue_name => 'aqadmin.obj_queue', enqueue_options => queue_options, message_properties => msg_properties, payload => msg_text, msgid => msg_id); commit; END; 此时,可以查看一下queue table的内容。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 63 7.出队dequeue DECLARE queue_options DBMS_AQ.DEQUEUE_OPTIONS_T; message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; msg_id RAW(2000); msg_text aqadmin.queue_message_type; BEGIN DBMS_AQ.DEQUEUE(queue_name => 'aqadmin.obj_queue', dequeue_options => queue_options, message_properties => message_properties, payload => msg_text, msgid => msg_id); COMMIT; DBMS_OUTPUT.PUT_LINE('Dequeued no: ' || msg_text.num); DBMS_OUTPUT.PUT_LINE('Dequeued title: ' || msg_text.title); DBMS_OUTPUT.PUT_LINE('Dequeued text: ' || msg_text.text); END; 跨数据库的 SQL 编写跨数据库的SQL语句,主要通过DBLINK来实现。本例以机器A(SID为PROD)、B(SID为DEVE)来说明。 如果SID相同,那除以下步骤外,还有设置global_name。 1、在机器A上定义tnsname,以连接机器B上的数据库。 DEVE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.211.22)(PORT = 1541)) (CONNECT_DATA = (SID = deve)) ) 2、在机器A上定义DB LINK。 SQL> create database link db_deve connect to apps identified by password using 'deve'; 上述语句使用了apps作为连接机器B的数据库用户, 3、测试一下是否成功。 SQL> select name from v$database; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 64 NAME --------- PROD SQL> select name from v$database@db_deve; NAME --------- TEST ORACLE 高级复制 Oracle Advanced Replication。相对DataGuard、Stream等这些DBA领域的东西,高级复制功能跟开发人员比 较接近。本例以机器A(SID为prod)、B(SID为aux)来说明。Replication没局限数据库版本、操作系统等。 Node1机器为主体定义站点(即:指配置复制工作的源站点)。 Node2机器为主体站点(即:复制的目标站点)。 1、准备工作(以下两个数据库都要做): 1.1 修改启动参数: SQL>show parameter job_queue_processes #值必须大于0 SQL> show parameter global_names; #值必须为true SQL> alter system set global_names=true scope=both; 1.2 设置global_name Node1:SQL> alter database rename global_name to node1.prod.com.cn; Node2:SQL> alter database rename global_name to node2.aux.com.cn; 查询是否设置成功: SQL> select * from global_name; 2 配置replication环境(以下两个数据库都要做) 2.1创建replication用户环境 SQL>create user repadmin identified by “repadmin“; SQL>grant connect,resource to repadmin; 2.2 配置replication权限 授予repadmin用户权限可以管理当前站点中任何主体组 SQL>EXECUTE dbms_repcat_admin.grant_admin_any_schema('repadmin'); 授予repadmin用户权限可以为任何表创建snapshot logs SQL>GRANT comment any table TO REPADMIN; SQL>GRANT lock any table TO REPADMIN; 指定repadmin用户为propagator,并授予执行任何procedure的权限 EXECUTE dbms_defer_sys.register_propagator('REPADMIN'); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 65 GRANT execute any procedure TO REPADMIN; 3 配置Database Link(分开配置) Node1: create public database link NODE2.AUX.COM.CN connect to repadmin identified by repadmin using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = aux)))'; Node2: create public database link NODE1.PROD.COM.CN connect to repadmin identified by repadmin using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = prod)))'; 注:global_names为true时,database link必须与global_name一致。 4.创建复制的对象(以下两个数据库都要做,以repadmin用户登录) SQL> conn repadmin/repadmin SQL> create table rep_test(col1 int primary key,col2 char(10)) ; 说明:replication的表必须定义主键,否则加入不了复制组。 5. 以下只主体定义站点操作,即node1(以repadmin用户登录)。 5.1 定义复制组 SQL> conn repadmin/repadmin SQL> execute dbms_repcat.create_master_repgroup('rep_hh'); 查询复制组:SQL> select gname,master,status from dba_repgroup where gname='REP_HH'; 5.2 在复制组中加入复制对象 SQL>Execute dbms_repcat.create_master_repobject(sname=>'repadmin',oname=>'rep_test',type=>'table',use_existing_object=>t rue,gname=>'rep_hh',copy_rows=>false); 说明:sname:指对象的拥有者;Oname:指对象名;Type:指对象类型;Gname:指复制组。 查询复制对象:select sname,oname,status,gname from dba_repobject where gname='REP_HH'; 5.3 对复制对象产生复制支持 SQL> execute dbms_repcat.generate_replication_support('repadmin','rep_test','table'); 查询复制对象支持:SQL>select sname,oname,status,gname from dba_repobject where gname='REP_HH' 5.4 添加主体站点 SQL>execute dbms_repcat.add_master_database(gname=>'rep_hh',master=>'NODE2.AUX.COM.CN',use_existing_objects=>tr ue,copy_rows=>false, propagation_mode => 'synchronous'); 说明:主体站点,即目标复制站点。 5.5 可以切到主体站点查看一下复制对象(在主体定义站点执行,即B机器)。 SQL>select sname,oname,status,gname from dba_repobject where gname='REP_HH' 5.5启动replication Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 66 SQL>execute dbms_repcat.resume_master_activity('rep_hh',true); 6.到这,replication配置完毕。下面进行测试。 Node1: SQL> insert into rep_test1 values(1,'test'); SQL>commit; SQL> select * from rep_test1; Node2: SQL> select * from rep_test1; 两边的结果是一样的,这也说明replication是基于触发器工作的。这与基于重做日志DataGuard、Stream等 解决方案的工作原理完全不同。 查询某一表或视图中的触发器 select * from dba_triggers dt where dt.table_name='T1' EBS 开发基础 开发工具安装 Oracle EBS11i 开发工具是developer6i的东西,非常古老。因为是转手过来的机器,开发工具都是安装 好了,所以也没去在意这工具的安装。今天系统重装了,才知道这工具安装起来也是特烦人的。 先安装FORMS6i与REPORT6i,安装完后一定要打补丁。否则会出现一大堆莫名的错误。接着安装 discoverer。安装discoverer时先将注册表备份出来,因为discoverer不能和Forms安装在同一目录。然后再安 装 discoverer的补丁,接着再将注册表恢复回来。大致这样可以了。注意顺序一定要不能错了,否则又会 出现一堆无聊的错误。唉!developer6i是98年的工具。 安装ftp工具=>LeapFTP。 安装远程登录工具=>cygwin。这个工具可以在window模似执行linux shell命令。 下载:http://www.cygwin.com/ 安装很简单,记得将openssh包安装上即可。 远程连接Linux Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 67 Oracle EBS 工具集 除了 Oracle 本身的开发工具,如:Form、Report、Discoverer、Jdeveloper、Workflow 外。还一些工具也是 必须的。 1. Firefox3:EBS11i 最高只支持 Firefox3,版本太高会不支持。安装时先安装 firefox,再安装 EBS 插件。 2. FireFTP:基于 Firefox 的 FTP 插件。非常好用。 3. PUTTY:SSH 连接工具 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 68 3.1.中文输入 Putty 支持中文输入:Window=>Appearance=>Change 3.2. sqlplus 中文乱码 检查三大字符集设置是否一致:客户终端字符集、NLS_LANG 参数、数据库字符集 1. 客户终端字符集:本文为 XP 系统、PUTTY 工具 XP 字符集: C:\>chcp Active code page: 936 代码页 936 就是中文字符集 GBK,可以参考 msdn 的资料《Windows Codepage 936》 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 69 http://www.microsoft.com/globaldev/reference/dbcs/936.htm PUTTY 字符集: 2. 服务器 NLS_LANG 环境变量 export NLS_LANG=“SIMPLIFIED CHINESE_CHINA.ZHS16GBK“ 3. 数据库字符集 SQL> col VALUE for a30 SQL> select * from nls_database_parameters ORACLE 字符转换规则: 如果“NLS_LANG”等于“数据库字符集”时,不需要进行任何转换,直接把字符插入数据库。 如果“NLS_LANG”不等于“数据库字符集”,则需要进行转换,乱码的根源就在这里。 推荐设置: 1.如果有可能,尽量保证客户端编码(Windows XP 的 cmd 工具可以使用 chcp 命令来确认)、NLS_LANG 参数和数据库字符集这三个内容一致,这样设置,无论是从性能上,还是从防止编码转换上都是最佳的; 2. 可已将 NLS_LANG 参数与操作终端字符编码一致,这样可以保证数据库能正确获得应用终端使用的编 码,这时会发生“编码转换”。但是,这样就可以保证正确转码,可以防止错误的编码存入数据库。 3.3.分颜色显示支持 putty 也支持分颜色显示目录,可执行文件,普通文件等,要设置一下: connection->;terminal type string->;将默认的 xterm 改为 xterm-color Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 70 3.4.复制和粘贴 在 putty 窗口中,通过鼠标左键拖动选取复制对象,如一个命令,同时已经加入到 windows 的粘贴板了, 通过单击鼠标右键来粘贴对象。在 windwos 应用可以直接粘贴使用。同时 windows 的粘贴板的对象可以在 putty 窗口中通过单击鼠标右键来粘贴对象。 EBS 二次开发包注册 2.1 从 Server 中下载 ERP Library (.pll), 存放于本机Server Path : /u01/au/11.5.0/resource Form文件与Library文件要存放在固定目录 例如: Form存放在 d:\ErpForm\Form Library 存放在d:\ErpForm\Library; 2.2 在 Regedit > HKEY_LOCAL_MACHINE > SOFTWARE > ORACLE > FORMS60_PATH 加入client 端存放 Form 及 Library的路径(如图) 例如:d:\ErpForm\Library;d:\ErpForm\Form(建议要加在最前面 ) 利用 TEMPLATE.fmb 模板来开发 Form Oracle 已 经 为 我 们 提 供 了 一 个 Form 的 开 发 模 板 , (/data/deve/devappl/au/11.5.0/forms/ZHS/TEMPLATE.fmb) 我们的开发实际要基于这个模板,这个模板里面已经存在了我们将会用到的 Oracle 标准的对象,我们需要 做的,就是这这个模板的基础上面,添加我们自己的对象。这也是人家说二次开发没啥技术含量的重大原 因。但如果真让你出写那么多 PL 包,估计也没几人能写好。人有时就是这样,让你站在巨人的肩膀,还 在抱怨这抱怨那的。我也是这类人啦。出出气呀。^_^! 1、更改 template.fmb 文件名,同时删除一些无用的样本对象。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 71 2、增加数据块 一路照做就可以了,步骤太简单就不用讲了吧。完成后,选择“仅创建数据块”。 3、增加一个画布 选择工具菜单上面的布局向导来做。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 72 注意:选择画布时,选择新画布。如上图。 显示记录数一般为 10 就可以了。如上图。 注:将画布名称改成与数据块的名称一致 4、增加一个窗口 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 73 注:窗口尽量保持与画面一致,同时选择相应的主画布。对应的画布那边也要选择主窗口 5、选择对象的子类信息 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 74 所有的对象都应选择相对的子类信息,这边仅以BLOCK为例。 6、调整布局 不需要显示到画布的ITEM,可以将ITEM的画布属性设成空 7、修改触发器(这一步很关键!) Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 75 8、选择第一导航块 9、上传及编译FORMS Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 76 9.1先将FORMS上传至/data/deve/devappl/au/11.5.0/forms/ZHS/ 9.2telnet至server,进入/data/deve/devappl/au/11.5.0/forms/ZHS/目录 目录:cd /data/deve/devappl/au/11.5.0/forms/ZHS/ 编译:f60gen FRMSTONE.fmb apps/apps 复制:cp FRMSTONE.fmx /data/deve/deveappl/hek/11.5.0/forms/ZHS/ 注:一定要进入FORM的目录,再进行编译。否则编译也可以通过,但会出现奇怪的问题。这个问题 折腾了我二天时间,才发现是ORACLE的BUG。 好了,一个最基本的EBS FORM就开发完成了。 注册表单 FORM 1.定义表单 操作路径:应用开发员=>应用产品=>表单 填写说明如下: 表单:FORMS 文件名 应用:HEK 惠尔康客户化应用 用户表单名:这个参数与“功能”中的表单名是相关联的。 2.定义功能:操作路径:应用开发员=>应用产品=>功能 2.1 2.2 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 77 2.3 3.定义菜单 操作路径:应用开发员=>应用产品=>菜单 注:“子菜单”是指菜单可以将另一个菜单的功能全部包含进来。也就是父菜单的关系。 5. 完成设置,效果如下: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 78 注册请求 1.定义可执行 路径:系统管理员->并发->方案->可执行 注意:定义成请求的过程或函数,必须加入(Errbuf Out Varchar2,Retcode Out Number)两个形参。 2.定义并发程序 路径:系统管理员->并发->方案->可执行 注意:并发程序界面的可执行组中的“名称”,与上面的可执行并发程序界面的“简称”是一致的。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 79 注意:如果要传入参数到 PL/SQL 包中,点击《参数》按钮进行设置。 也可以为参数赋给默认值,例:默认取得用户 ID 如果是字符型参数,但想传入当前日期。可按下图设置 如果要限定参数的取数范围,则要定义值集(如何定义?请参考后面的定义值集)。 3.定义请求组 路径:系统管理员->安全性->责任->请求 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 80 4.将请求组置于职责下 系统管理员->安全性->责任->定义 注册职责 1.新建菜单 操作路径:应用开发员=>应用产品=>应用菜单 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 81 2.新建一个职责 操作路径:系统管理员=>安全性=>责任=>定义 将职责分配给用户 操作路径:系统管理员=>安全性=>用户=>定义 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 82 3.转到EBS主页,多显示一个职责。 注册值集 1.定义集 操作路径:总帐=>设置=>财务系统=>验证=>集 1.1 如果值的来源于数据表,则值的验证类型选择“表”,然后点击“编辑信息”。 注:如果要实现请求参数的值集来源,作前后过滤条件的话。其语法: where head.customer_number = :$FLEX$.HEK_OM_KHJGB_ACCT_CUST and head.created_by = :$FLEX$.HEK_OM_FHD_CREATE_BY and TO_CHAR(head.ordered_date,'YYYYMMDD') = :$FLEX$.HEK_DAY_TIME_ID ORDER BY HEAD.ORDER_NUMBER Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 83 :$FLEX$.为参数界面的名称 HEK_OM_KHJGB_ACCT_CUST 为某个参数的值集名称。 1.2 实现的效果如下: 1.3 定义具有多列的值集 1.4 实现效果 2. 定义固守的值 2.1 如果值的来源固定的某些值,那么可以直接定义值。将值的验证类型选择=>独立 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 84 2.2 定义集所对应的值 操作路径:操作路径:总帐=>设置=>财务系统=>验证=>值 注册主从值集 1.定义主集 操作路径:总帐=>设置=>财务系统=>验证=>集 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 85 2.定义从集 操作路径:总帐=>设置=>财务系统=>验证=>集 点击编辑信息按钮 3.定义主集值 操作路径:总帐=>设置=>财务系统=>验证=>集 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 86 4.定义从集值 操作路径:总帐=>设置=>财务系统=>验证=>集 5.效果如下: 值集之间个有关联关系。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 87 通过应用开发员取得系统管理员职责 一般公司都有区分DBA与DEVELOPER的,业务机上一般是APPS用户由DBA掌握,诸如用户管理也 是DBA来负责的。developer是不知道 没有APPS密码,也没有”SYSTEM ADMINISTTRATOR“职责的。也 就是说DEVELOPER只有”Application Developer“这个职责。同理,应用开发员是没有权限给终端用户加权 限的。但我今天测试了一下,developer虽然没直接加职责的权限,但developer有”Application Developer“职 责,这个职责下面有注册菜单的权限,完全可以通过注册菜单将SYSTEM ADMINISTROR加进来。如图: 查询视图时没有显示数据 在 EBS 以外的地方(含 report),如果确认 SQL 语句没有问题,这主要是因为视图是 OU 屏蔽的,因此需 要初始化身份。例如:PO_HEADERS。有两种方法,方法一适用于知道相应的组织代码,方法二就比较通 用了。 方法一: BEGIN DBMS_APPLICATION_INFO.set_client_info(122); --122 代表具体的组织代码,不同公司,代码是不同。 END; 方法二: BEGIN APPS.FND_GLOBAL.apps_initialize ( user_id =>APPS.FND_GLOBAL.user_id, --ERP 用户的 ID resp_id =>APPS.FND_GLOBAL.resp_id, resp_appl_id =>APPS.FND_GLOBAL.resp_appl_id ); END; //另外方法一有时可能会无效。例:GME.GME_BATCH_HEADER_VW 这张视图有用户组织权限,只能使用方法二. 注册报表(report) 定义可执行 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 88 路径:系统管理员->并发->方案->可执行 注册报表与注册请求的步骤是差不多的。 定义并发 路径:系统管理员->并发->方案->定义 定义报表参数。点击上图的参数按钮。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 89 注:这边与注册请求不一样的就是,要填写变量栏(变量名称就是 REPORT 中的变量名称) 定义请求组 操作路径:系统管理员->安全性->责任->请求 将请求组置于职责下 系统管理员->安全性->责任->定义 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 90 OK,完成了报表的注册。 请求的输出及日志 1、Effect: Oracle EBS 会在 EBS 的安装目录,保存查看输出及日志之文件。查看具体目录: select t.logfile_name,t.outfile_name from fnd_concurrent_requests t where t.request_id = _request_id 2、当我们自行定义了一个并发请求时,也经常需要使用上面的方式进行一些调试。 请求的输出:apps.Fnd_File.Put_line (apps.FND_FILE.OUTPUT, '请求的输出'); 请求的日志:apps.Fnd_File.Put_line (apps.FND_FILE.LOG, '请求的日志'); 对于报表,一般情况是把报表的内容输出到 OUTPUT,把报表中间的 debug 逻辑输出到日志中。 对于请求包,一般情况是输出到日志中。OUTPUT 就不用管了。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 91 实现手动提交请求 在 Form 里面,我们可以用 APPS.FND_REQUEST.SUBMIT_REQUEST 提交一个 Request 到 Oracle Request Manager。如果提交成功,该函数返回 Request ID,否则,返回 0。 1、初始化 在提交一个 Request 之前,我们会调用 Oracle Standard 的 Procedure 对这个 Request 做一些基本的参数的初 始化。 APPS.FND_GLOBAL.apps_initialize ( user_id =>APPS.FND_GLOBAL.user_id, resp_id =>APPS.FND_GLOBAL.resp_id, resp_appl_id =>APPS.FND_GLOBAL.resp_appl_id ); 注:这个初始化不是必须的,之所以要初始化,是因为视图是 OU 屏蔽的。 上述语句等同于 dbms_application_info.set_client_info; 2、函数介绍 2.1 函数 APPS.FND_REQUEST.SUBMIT_REQUEST 有 105 个参数: APPS.FND_REQUEST.SUBMIT_REQUEST ( APPLICATION IN VARCHAR2 DEFAULT NULL, PROGRAM IN VARCHAR2 DEFAULT NULL, DESCRIPTION IN VARCHAR2 DEFAULT NULL, START_TIME IN VARCHAR2 DEFAULT NULL, SUB_REQUEST IN BOOLEAN DEFAULT FALSE, chr(0),'','','','','','','','','','','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','' ) RETURN NUMBER; 2.2 参数详解 2.2.1 APPLICATION(必需参数) 应用程序的名称缩写。一般我们可能会用到下面的几个: Oracle Assets ……> OFA Oracle General Ledger ……> SQLGL Oracle Inventory ……>INV Oracle Order Management ……>ONT Oracle Payables ……SQLAP Oracle Pricing ……QP Oracle Purchasing ……PO Oracle Receivables ……AR 操作路径:系统管理员=>应用=>注册 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 92 2.2.2 PROGRAM(必需参数) 要提交到 Oracle Request Manager 的并发程序之简称 操作路径:应用开发员=>并发=>程序 2.2.3 第三、第四个参数 第三、第四个参数默认为空 2.2.4 第五个参数 第五个参数默认为 false 2.2.5 第六至第十零五个参数 第六至第十零五个参数为要传入到请求中的自定义参数值。如果无须这么多参数时,以 chr(0)作为 参数结束的标记。Chr(0)后面剩余的参数为 ’’。 手动提交请求示例 declare v_order_number number; v_req_id number; begin v_order_number := :HEK_ODS_TH_FEE_M_V.OE_HEAD_NUMBER; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 93 if :HEK_ODS_TH_FEE_M_V.OE_HEAD_NUMBER is null then FND_MESSAGE.DEBUG('请选择配送单后再打印!'); RAISE FORM_TRIGGER_FAILURE; end if; v_req_id := fnd_request.submit_request('HEK', 'HEK_退货单', '', '', FALSE, v_order_number,--v_batch_no, null,--v_batch_no, null,--v_cust_num, null,--v_driver_num, null,--v_trans_num, null,--v_vendor_num, null, null, null, chr(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '','', '', '', '', '', '', '', '', '', '','', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',''); if (v_req_id = 0) then FND_MESSAGE.RETRIEVE; FND_MESSAGE.ERROR; else update HEK_ODS_TH_FEE_M set print_mark='Y' where OE_HEAD_NUMBER = (v_order_number); commit_form; fnd_message.debug('您的请求已经提交,请求号为:' || to_char(v_req_id) || ',请通过查看->请求来查看输出结果。 '); end if; end; 客制化菜单 Effect: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 94 Usage:调用 app_special.instantiate 包 Examples: 1、增加一个自定义 Form Level 的触发器(SPECIAL11) 2、加入代码如下: declare v_invoice_num varchar2(50); begin v_invoice_num := '菜单栏客制化100'; fnd_message.debug(v_invoice_num); end; 3、在 Form WHEN-NEW-FORM-INSTANCEFJ 进行调用: APP_SPECIAL.INSTANTIATE('SPECIAL11','测试菜单2', '', TRUE, 'LINE'); 这样就完成了 EBS 菜单的开发啦。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 95 4、一些特殊说明: 上面自定义 FORMS 级触发器,名字必须定义为“SPECIAL+数字”,否则会报错。并且数字的大小决定了 菜单出现的先后顺序。数字还有更大作用就是决定了,自定义菜单选项放在哪个主菜单下。 SPECIAL1—SPECIAL15 在“工具”主菜单下。(不允许改变名称) SPECIAL16—SPECIAL30 在“报表”主菜单下。(允许改变名称) SPECIAL31—SPECIAL45 在“活动”主菜单下。(允许改变名称) 例:APP_SPECIAL.INSTANTIATE('SPECIAL_B','报表=>修改',NULL,TRUE); APP_SPECIAL.INSTANTIATE('SPECIAL_C','活动=>修改',NULL,TRUE); SPECIAL46 以上就直接报错了。^_^ 如下图: 5、控制自定义菜单的是否激活可用。 使用 app_special.enable 函数可以控制菜单是否可以使用。 例如:基于不同的数据块,实现菜单的不可用。在 block 的”when-new-block-instance”中加入 app_special.enable('SPECIAL1',property_off); 效果: 6、在自定义的菜单上使用 checkbox 按钮。 ①增加一个自定义 Form Level 的触发器(SPECIAL1_CHECKBOX),代码如下: if app_special.get_checkbox('SPECIAL1_CHECKBOX')='TRUE' then fnd_message.debug('Special 1 is True!'); else fnd_message.debug('Special 1 is False!'); end if; 注:使用 app_special.get_checkbox 来获取 checkbox 的状态值。 ②在 Form 的 WHEN-NEW-FORM-INSTANCE 触发器中初始化菜单。 app_special.instantiate('SPECIAL1_CHECKBOX','Spe&cial 1 Box w Line', '',TRUE,'LINE'); app_special.set_checkbox('SPECIAL1_CHECKBOX','TRUE'); 注:app_special.set_checkbox 是对 checkbox 进行赋值操作。 ③效果如下: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 96 客制化右键菜单 1、 首先如果是要为某个 ITEM,另外开发一个右键菜单。这个需要直接按照 FORM 的开发教程,自定义 一个 POPUP 菜单就可以了。但本文讲的在 EBS 所有的快捷菜单上,额外增加所需的菜单按钮。也就 是要图上所示的快捷菜单上增加菜单按钮。 2、 在 FROM-LEVEL 增加自定义触发器(名字规则为:POPUP+N) 3、 ITEM 的“PRE-POPUP-MENU”触发器上初始化菜单。 APP_POPUP.INSTANTIATE('POPUP1','First Entry'); APP_POPUP.INSTANTIATE('POPUP10','SECONED Entry',TRUE,'LINE'); APP_POPUP.INSTANTIATE('POPUP3','THREE Entry',FALSE,NULL); 4、 如果是整个 BLOCK 的 ITEM 都需要客制化快捷菜单,可以在 BLOCK 的“PRE-POPUP-MENU”定义。 APP_POPUP.INSTANTIATE('POPUP1','global'); 客制化工具栏 增加工具栏按钮: 1.在 FORM 级增加一个自定义触发器(SPECIAL16),加入代码 begin fnd_message.debug('EBS工具栏扩展'); end; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 97 2.在 when-new-form-instance 触发器,加入代码 APP_SPECIAL.INSTANTIATE('SPECIAL_B','报表=>扩展',NULL,TRUE); APP_SPECIAL.INSTANTIATE('SPECIAL16','工具栏按钮','FNDIREQF',TRUE);提示: 提示:FNDIREQF 是指位于 EBS 应用层$OA_MEDIA 目录的图片名称。 3.效果如下: 失效工具栏保存按钮: 1.在 WHEN-NEW-FORM-INSTANCE 中加入 set_menu_item_property('FILE.SAVE', enabled, property_off); 2.查看效果 3.与此类似的还有。 set_menu_item_property('Edit.delete', enabled, property_off); 失效“Deletes Recode set_menu_item_property('Edit.Clear', enabled, property_off); 失效“Clear Recode“ 调用 EBS 日期控件 1、 首先将 ITEM 的 LOV 属性设置为“ENABLE_LIST_LAMP”、列表验证属性设置为“否” 2、在 ITEM 的“KEY-LISTVAL”解发器下加入对下代码: BEGIN calendar.show(); END; 3、注意:ITEM 对应数据库类型必须是 DATE 类型,否则会报 frm-40700 错误。效果如下: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 98 4、设置默认为系统的当前日期 查询数据时限定语言环境 EBS 是个多语言的业务系统,界面上只查询到一条记录,实际上后台数据表是多条记录的。如:值集表。 select * from apps.FND_FLEX_VALUES_TL t, apps.FND_FLEX_VALUES B where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID and t.language = userenv('LANG') 实现历史记录查询 1.要实现的效果 2.在定义数据表时,必须加入以下五个字段: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 99 即:CREATED_BY NUMBER、CREATION_DATE DATE、LAST_UPDATED_BY NUMBER、LAST_UPDATE_DATE DATE、LAST_UPDATE_LOGIN NUMBER 网上有些文章说还必须定义主键、序列。但如果仅实现此功能是不需要的。当然从数据库设计方面来说,这些定义也是必须 的。 3、在数据块的 PRE-INSERT、PRE-UPDATE 触发器中加入代码 FND_STANDARD.SET_WHO; 实现文件夹功能 1、在客制的 Form 里面实现 Oracle Folder 的功能,最终效果如下: 2、使用 TEMPLATE.fmb 创建一个 Form(名称:FRMSTONE)。 2.1 添加 Form Objects 包括一个 Window:FOLDER_W 一个 Canvas:FOLDER_C 一个 Block:MY_FOLDER(可以使用向导创建),并修改其属性。如下图: 注:使用文件夹的数据块,其名称总长不能超过 22 字节! 2.2 修改 Trigger 和 Program Units Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 100 Form Level Trigger: PRE-FORM: app_window.set_window_position(' FOLDER_W ', 'FIRST_WINDOW'); Program Units:APP_CUSTOM: if (wnd = ' FOLDER_W') then app_window.close_first_window; 2.3 修改 FORM 第一导航块、控制窗口属性 注:须要说明的是:设成第一个导航数据块的 blockname 必须至少一个 item 处于 canvas 中,否则会报 FRM-40106 的错误。 3、添加 Folder 相关的 Objects 3.1 文件夹相关的 Window、Canvas、Block、Item…等都包含在 APPSTAND.FMB。 3.2 在同一窗口打开 APPSTAND.FMB 和我们自己客制的 Form,将 APPSTAND.FMB 的对象组 STANDARD_FOLDER,然后拖动至我们自己的 Form 中。 3.3 此时会弹出对话框: 选择子类,你会发现 Form Builder 都会在你的 Form 里面自动添加很多对象。 4、增加 Attached Libraries 做完上面的步骤后,检查一下 Attached libraries 里面有没有 APPFLDR。如果没有,我们需要手工添加。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 101 5、增加 Stacked Canvas (命名为:FOLDER_STACK) 这一步是必须的,而且你期望实现 Folder 拖动功能的那些 Item 都是放在这个 Canvas 里面,这个 Stacked Canvas 又是放在前面我们建立的 Canvas:FOLDER_C 上面的。 5.1 增加 Stacked Canvas 双击打开 Canvas MY_FOLDER,在左侧工具条里面选择 Stacked Canvas,然后在 FOLDER_C 里面 拖动。 5.2 设置 Stacked Canvas 属性 6、更改数据块 MY_FOLDER 中的 Item 属性 注:这个画布设置为 FOLDER_STACK 层叠画布。 在 FOLDER_C 里面拖动 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 102 7、创建控制块 MY_FOLDER_PROMPT 7.1 设置块的属性 子类信息:block 数据库数据块:否 7.2 增加 ITEM 注:增加 Item,名称必须和 Block MY_FOLDER 中的 Item 保持一致。另外,数据块中有些 item (如 id)不要显示在 canvas 上的,那么此类 item 也无须在 MY_FOLDER_PROMPT 创建. 7.2 设置其初始值(即:显示的标签名)及子类信息 7.3 增加其他相关 Item 我们还需要增加其他的一些 Item,都是和 Folder 的功能有关的。 Item Property Value ORDER_BY1 ORDER_BY2 ORDER_BY3 Item Type Push Button Subclass Information FOLDER_ORDERBY Canvas FOLDER_STACK X Position 0 Y Position 0 Width 0 Height 0 备注 后面的四个 0 是这是这个 Item 不需要显示出来,是因为目前我们不需要利用几个 Button. Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 103 Item Property Value FOLDER_OPEN Item Type Push Button Subclass Information FOLDER_OPEN Canvas FOLDER_STACK Trigger WHEN-BUTTON_PRESSED copy('OPEN', 'global.folder_action'); execute_trigger('folder_action'); Item Property Value FOLDER_TITLE Item Type Display Item Subclass Information DYNAMIC TITLE Canvas FOLDER_STACK X Position 0 Y Position 0 Width 0 Height 0 备注 后面的四个 0 是这是这个 Item 不需要显示出来,是因为目前我们不需要利用这个 Item. Item Property Value FOLDER_DUMMY Item Type Text Item Subclass Information FOLDER_DUMMY Canvas TOOLBAR X Position 0 Y Position 0 Width 0 Height 0 8、增加相应的 trigger Level Object Trigger Event Form FRMSTONE FOLDER_ACTION app_folder.event(:global.folder_action); WHEN-NEW-FORM-INSTANC E app_folder.define_folder_block ( 'MY_FOLDER', --'FormName' 'MY_FOLDER', --'folder_block', 'MY_FOLDER_PROMPT', --'prompt_block', 'FOLDER_STACK', --'stacked_canvas', 'MY_FOLDER', --'window', NULL --'disabledfunctions'; ); Block MY_FOLDE R PRE-BLOCK app_folder.event('PRE-BLOCK'); POST-BLOCK app_folder.event('POST-BLOCK'); WHEN-NEW-BLOCK-INSTAN CE app_folder.event('WHEN-NEW-BLOCK-INSTANCE'); PRE-QUERY app_folder.event('PRE-QUERY'); KEY-EXEQRY app_folder.event('KEY-EXEQRY'); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 104 KEY-NEXT-ITEM app_folder.event('KEY-NEXT-ITEM'); KEY-PREV-ITEM app_folder.event('KEY-PREV-ITEM'); 备注 最简单的方式是直接把标准 Form ‘APPSTAND.fmb’ 中的 Block ‘SAMPLE_FOLDER’ 中的各个 Triger 复制过来 9、其他注意事项 9.1 错误:在编译带有文件夹的 FORM 时,经常会出现如下提示。 这是因为 ITEM 超出的了画布的高度或是宽度造成的。如图: 可以通过调整 ITEM 的 X 坐标或 Y 坐标来解决此错误。 9.2 错误:无法实现拖动。 解决:数据块的 item 与 folder 的 item 与调整一定的距离。 实现手电筒查找的功能 首先,先按 TEMPLATE 将其他功能先实现,然后再来实现查找的功能。手电筒查找的 Effect 有两二种, ①LOV 形式,②window 形式。 1、LOV 查找模式:一般适用于查询结果只返回单条条件的查询(如主从数据块的界面)。 FRM-30085: Unable to adjust form for output Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 105 ①创建一个 parameter 参数:TEST_P ②创建一个基于主键查询 LOV-TEST_L,并将主键字段映射给 Parameter 参数 TEST_P。 ③在数据块中创建 PRE-QUERY 触发器。 IF :parameter.G_query_find = 'TRUE' THEN :BLOCKNAME.HEADER_ID := :parameter.TEST_P; :parameter.G_query_find := 'FALSE'; end if; ④在数据块中再创建 QUERY_FIND 触发器。 begin app_find.query_find('TEST_L'); --调用 LOV end; ⑤完成效果如下: 2、WINDOW 查找模式:一般适用多条件查询,可以返回多条查询记录。 ①打开在标准 FORM---APPSTAND.fmb。将对象组中的 QUERY_FIND 拖至待开发的 FORM 中。 ②重命名第一个数据块、画布、窗口(QUERY_***都是刚才自动创建的)。USER GUIDE 说可以重命名, 但我没有重命名成功。也懒得去折腾一个命名的问题。^_^ ③打开 QUERY_FIND 画布,改写“新建”、“查找”两个按钮 WHEN-BUTTON-PRESS 之中的代码。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 106 NEW 按钮代码: --app_find.new('Your blockname here'); app_find.new('HEK_SALE_CUST_MANUAL'); -- HEK_SALE_CUST_MANUAL 为实现查找的数据块名 FIND 按钮代码: :parameter.G_query_find := 'TRUE'; --app_find.find('your blockname here'); app_find.find('HEK_SALE_CUST_MANUAL');-- HEK_SALE_CUST_MANUAL 为实现查找的数据块名 :parameter.G_query_find := 'FALSE'; ④设置 QUERY_FIND 数据块的“前一导航数据块”,也就是要实现询查功能的数据块。 ⑤修改 QUERY_FIND 数据块 KEY-NXTBLK 触发器代码 :parameter.G_query_find := 'TRUE'; --app_find.find('your blockname here'); app_find.find('HEK_SALE_CUST_MANUAL'); -- HEK_SALE_CUST_MANUAL 为实现查找的数据块名 :parameter.G_query_find := 'FALSE'; ⑦修改 QUERY_FIND 窗口的标题及尺寸,并在 QUERY_FIND 画布上创建要查找的条件 ITEM。 ⑧在要实现查询的数据块(如:HEK_SALE_CUST_MANUAL),创建 PRE-QUERY 触发器。 if :parameter.g_query_find = 'TRUE' then app_find.query_range(name_in('query_find.CUSTOMER_NUMBER'),name_in('query_find.CUSTOMER_ NUMBER'),'HEK_SALE_CUST_MANUAL.CUST_CODE'); app_find.query_range(:query_find.CUST_DATE,:query_find.CUST_DATE,'HEK_SALE_CUST_MANUAL.TI ME_ID'); :parameter.G_query_find := 'FALSE'; end if; 注:app_find.query_range 参数前两个是 QUERY_FIND 中的 ITEM,后一个是查询块中的 ITEM。 事实上我们也可以直接使用 copy 函数将 QUERY_FIND 的值赋给数据块。例: copy(:query_find.CUST_DATE,'HEK_SALE_CUST_MANUAL.TIME_ID'); ⑨在要实现查询的数据块(如:HEK_SALE_CUST_MANUAL),再创建 QUERY_FIND 触发器。 --APP_FIND.QUERY_FIND('','',''); app_find.query_find(' MAIN_W','QUERY_FIND','QUERY_FIND'); --main_w 指数据块所属的窗体 ⑩完成效果如下: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 107 记录指示器切换标签页 Effect: 1、 在该数据块中手工增加一个 ITEM 设置 ITEM 的属性:子类信息:设成 DRILLDOWN_RECORD_INDICATOR;数据库项:否。 2.在新增的 ITEM 的 WHEN-MOUSE-CLICK 触发器加入 declare v_order number; begin v_order := : block_name1.item; --block_name 表示要切换 tab 的数据块 if v_order is not null then go_block(' block_name1'); set_block_property('block_name1 ',DEFAULT_WHERE,'ORDER_NUMBER='||v_order); execute_query; end if; end; 3.这样就可以实现类似 VB 中的 TAB 切换了。由于 ORACLE FORM 没有专门的 TAB 函数,只能如此曲折 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 108 实现。 EBS 的条件查询方法 1、使用 app_find.find IF (NAME_IN('PO_HEADERS.PO_HEADER_ID') IS NOT NULL) THEN :parameter.G_query_find := 'TRUE'; app_find.find('CUX_PO_HEADERS_ADD_MESSAGE'); go_block('CUX_PO_HEADERS_ADD_MESSAGE'); :parameter.G_query_find := 'FALSE'; ELSE FND_MESSAGE.DEBUG('请先保存订单头'); END IF; 在 CUX_PO_HEADERS_ADD_MESSAGE 的 PRE-BLOCK 中处理 copy(name_in('PO_HEADERS.PO_HEADER_ID'),'CUX_PO_HEADERS_ADD_MESSAGE.PO_HEADER_ID'); 2、使用 Default_where 属性处理 declare lv_default varchar2(2000); begin lv_default:='.....'; go_block('CUX_PO_HEADERS_ADD_MESSAGE'); set_block_property('CUX_PO_HEADERS_ADD_MESSAGE',DEFAULT_WHERE,’PO_HEADER_ID=’ ||lv_default); execute_query; end; 3、使用 app_find.query_range()来处理 注:app_find.query_range()必须有三个参数,也只能有三个参数 begin app_find.query_range(:find_date_from, :find_date_to, 'CUX_PO_HEADERS_ADD_MESSAGE.REATE_DATE'); :parameter.Q_query_find:='false'; end; 4、查询综合运用举例 前提准备工作,数据块:HKE_TEST 控制块:CONTROL 4.1.在数据块 HKE_TEST 的 PRE-QUERY 触发器设定查询条件 if :parameter.g_query_find = 'TRUE' then app_find.query_range(:CONTROL.CUST_F, :CONTROL.CUST_F ,'HKE_TEST.CUSTOMER_NUMBER'); :parameter.g_query_find := 'FALSE'; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 109 end if; 4.2 在查询按钮的 WHEN-BUTTON-PRESSED :parameter.G_query_find := 'TRUE'; app_find.find('HEK_TEST'); :parameter.G_query_find := 'FALSE'; 自定义代码 例 1:实现某个 ITEM 为必填项。如:OM 订单界面的销售人员为必填。 菜单:诊断=>自定义代码=>个性化 选择相应的触发器。如果是基于 block 的触发器,则要选择触发器对象(是哪一个块?)。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 110 切到活动选项卡,选择目标对象: 选择特性名称及值。 如:1.是否可必填:REQUIRED。2.是否可修改:ENABLED (APPLICATIONS COVER)等 保存完后,就可以实现“销售人员这一项为必填了”。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 111 例 2:根据条件实现某个 ITEM 不可修改。如:PO 订单界面,当物料编码不为空时,物料说明不可修改。 打开自定义代码界面。 Condition 选项卡: Trigger Event:WHEN-NEW-ITEM-INSTANCE Trigeer Object:PO_LINES.ITEM_DESCRIPTION Condition::po_lines.item_number is not null Actions 选项卡: Object Type:Item Target Object: PO_LINES.ITEM_DESCRIPTION Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 112 Property Name: ENABLED (APPLICATIONS COVER) Value:Fase 效果如下: 有物料编号时,物料说明不可修改: 反之,可以修改: 通过科目编码取科目描述 科目段弹性域 通过编码取得名称 Select apps.gl_flexfields_pkg.get_description_sql(101, Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 113 1, --公司段 '11' --科目编码--gcc.segment1 ) 公司描述, apps.gl_flexfields_pkg.get_description_sql(101, 2, --部门段 'ZJNBZY' --科目编码 ) 部门描述, apps.gl_flexfields_pkg.get_description_sql(101, 3, --科目段 '114301' --科目编码 ) 科目描述, apps.gl_flexfields_pkg.get_description_sql(101, 4,--子科目段 9021 --子科目编码 ) 子科目描述, apps.gl_flexfields_pkg.get_description_sql(101, 5, --项目段 '1010' --项目编码 ) 项目描述, apps.gl_flexfields_pkg.get_description_sql(101, 6, --备用段 '3001' --备用编码 ) 备用描述 from dual Fnd_Profile 之使用 begin fnd_message.debug('user_id= ' || fnd_profile.value('user_id')); --取当前登录 EBS 用户 ID fnd_message.debug('user_name= '||fnd_profile.value('USERNAME')); --取当前登录 EBS 用户名 fnd_message.debug('FND_Global.User_Name='|| FND_Global.User_Name); --取当前登录 EBS 用户名 fnd_message.set_string('GL_SET_OF_BKS_ID='||fnd_profile.value('GL_SET_OF_BKS_ID')); fnd_message.show; --取当前帐套 end; Form 之间共享参数组织 ID 这个功能类似于标准功能的参数设置。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 114 点击 Changge Organization 菜单后显示: 我们开发这个示例相对简单。参数设置界面直接调用 LOV 即可。下面具体说明:开发二个 Form,一个 Form 实现设置组织 ID,另一个 Form 引用其设置的组织 ID。 1、首先开发参数设置的 FORM,创建一个 LOV(LV_ORGN 赋值给 Parameter.orgn_id,具体开发过程略)。 然后在 PRE-FORM 的 Trigger 中加入: declare a boolean; begin a:=show_lov('LV_ORGN'); if :parameter.orgn_id is not null then FND_PROFILE.PUT('MFG_ORGANIZATION_ID',:parameter.orgn_id); else raise form_trigger_failure; end if; end; 2. 在开发第二个 FORM 时,在 PRE-FORM 的 Trigger 中加入判断: if fnd_profile.value('MFG_ORGANIZATION_ID') is null then FND_MESSAGE.set_token('MSG_TEXT','您必须先选择会话参数!'); FND_MESSAGE.show; raise form_trigger_failure; end if; 弹性域 弹性域分成键弹性域、说明性弹性域。这两种弹性域用途是不一样的。 关键性弹性域:在使用 KEY 弹性域的基表中,只保存 ID。但可以通 ID 查询到相应的 SEGMENT。在表结 构中表现为***_ID、SEGMENT1 等。 说明性弹性域:针对特定用户扩展输入特定信息的字段。在表结构中表现为 attribute_category、attribute1 字段等。 注册关键性弹性域 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 115 注册说明性弹性域 1.创建数据表。 注:使用说明性弹性域的数据表,必须含有 ATTRIBUTE_CATEGORY 及若干 ATTRIBUTEN 字段。 ATTRIBUTE_CATEGORY 字段:指弹性域的 CONTEXT 字段。 ATTRIBUTEN 字段:指实际使用的字段。 create table HEK_DISCOUNT_POLICY_H_NEW( POLICY_ID NUMBER, POLICY_NO VARCHAR2(30) not null, CUSTOMER_ID NUMBER, CUSTOMER_NUMBER NUMBER , CUSTOMER_NAME VARCHAR2(100), BENIFICIARY_ID NUMBER, BENIFICIARY_NUMBER NUMBER , BENIFICIARY_NAME VARCHAR2(100), RELATION_NUMBER NUMBER , RELATION_NAME VARCHAR2(100), PAYMENT_TYPE VARCHAR2(20), POLICY_PERIOD VARCHAR2(20) not null, ATTRIBUTE_CATEGORY VARCHAR2(150), ATTRIBUTE1 VARCHAR2(150), ATTRIBUTE2 VARCHAR2(150), ATTRIBUTE3 VARCHAR2(150), CREATED_BY NUMBER, CREATION_DATTE DATE, LAST_UPDATED_BY NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATE_LOGIN NUMBER) 2.注册弹性域表 begin ad_dd.register_table('HEK','HEK_DISCOUNT_POLICY_H_NEW','T',10,10,40); commit; end; 注:HEK 为客户化应用的简称。具体的查找方法请参考相关文档。下同。 3.注册弹性域表的列 begin ad_dd.register_column('HEK','HEK_DISCOUNT_POLICY_H_NEW','ATTRIBUTE_CATEGORY',1,'VARCHAR2',150,'N', 'N'); ad_dd.register_column('HEK','HEK_DISCOUNT_POLICY_H_NEW','ATTRIBUTE1',2,'VARCHAR2',150,'N','N'); ad_dd.register_column('HEK','HEK_DISCOUNT_POLICY_H_NEW','ATTRIBUTE2',3,'VARCHAR2',150,'N','N'); ad_dd.register_column('HEK','HEK_DISCOUNT_POLICY_H_NEW','ATTRIBUTE3',4,'VARCHAR2',150,'N','N'); commit; end; 4.查看是否注册成功 select ft.table_id from FND_TABLES ft where ft.table_name='HEK_DISCOUNT_POLICY_H_NEW' Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 116 select * from FND_COLUMNS fc where fc.table_id in (select ft.table_id from FND_TABLES ft where ft.table_name='HEK_DISCOUNT_POLICY_H_NEW') 5.注册弹性域列 操作路径:应用开发员=>弹性域=>说明性=>注册 点列按钮,选择注册相应的列 6.注册弹性域段 操作路径:应用开发员=>弹性域=>说明性=>段 点段按钮,输入提示文本 7.启用说明性弹性域 7.1 先按将相应的 FORM 开发好,然后在 FORM 级的以下 TRIGGER 中加入 PRE-QUERY: FND_FLEX.EVENT('PRE-QUERY'); POST-QUERY: FND_FLEX.EVENT('POST-QUERY'); PRE-INSERT: FND_FLEX.EVENT('PRE-INSERT'); PRE-UPDATE : FND_FLEX.EVENT('PRE-UPDATE'); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 117 WHEN-VALIDATE-ITEM: FND_FLEX.EVENT('WHEN-VALIDATE-ITEM'); WHEN-VALIDATE-RECORD: FND_FLEX.EVENT('WHEN-VALIDATE-RECORD'); 7.2 在 PRE-FORM 的 TRIGGER 中加入 fnd_descr_flex.define ( BLOCK=>'HEK_DISCOUNT_POLICY_H_NEW', FIELD=>'DESC_FLEX', APPL_SHORT_NAME=>'HEK', DESC_FLEX_NAME=>'HEK 返利_订金'); 说明一下相应参数:BLOCK:指启用弹性域的数据块名 FIELD:指后面新增的 ITEM APPL_SHORT_NAME:指相应模块的简称(系统管理员->应用->注册) DESC_FLEX_NAME:指注册弹性域时的名称 8.在要启用弹性域的数据块中增加一个 ITEM。名称为:DESC_FLEX。 8.1 DESC_FLEX 属性设置如下: 子类信息:TEXT_ITEM_DESC_FLEX 数据库项:否 画布:MAIN_C 8.2 在 DESC_FLEX 项的以下 trigger 中加入: WHEN-VALIDATE-ITEM :FND_FLEX.EVENT('WHEN-VALIDATE-ITEM'); WHEN-NEW-ITEM-INSTANCE:FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE'); 9.完成效果: 条件控制说明性弹性域 说明:通过一个字段的值控制是否显示说明性弹性域。与普通的说明性弹性域类似。下面仅说明不同之处。 1. 注册弹性域列时,定义一个参考列。 点击参考字段按钮 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 118 DISCOUNT_TYPE 是要启用弹性域数据表中的一个字段,即通过这个字段,来控制是否显示弹性域。 2.定义弹性域段 新增一条上下文值记录,例:年返。即当 DISCOUNT_TYPE 的值为“年返”时,才显示弹性域。 3.实现效果。 PO 行界面启用弹性域 在标准的 PO 订单 FORM 上启用弹性域步骤(其他 EBS 标准 FORM,步骤也一样): 1、启用弹性域列。操作路径:应用开发员=>弹性域=>说明性=>注册 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 119 点击列按钮,确保勾选要启用的弹性域的列(如:Attribute6、Attribute7)。 2、启用弹性域列。操作路径:应用开发员=>弹性域=>说明性=>段 点击编译 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 120 3、切到 PO 界面 调用会计科目弹性域 1.首先在创建数据表时,添一个字段用来保存会计科目的 ID。如:CODE_COMBINATION_ID 2.在 FORM 相应的数据块增加两个 ITEM,用来显示科目 NUMBER 与 DESCRITION。 例:GL_CODE_NUMBER、GL_CODE_DESC 注 : ENABLE_LIST_LAMP 是 TEMPLATE 模板自带的,无须手工去定义。 将 GL_CODE_NUMBER 项的 LOV 设成“ENABLE_LIST_LAMP”,数据库项设为“否”。 将 GL_CODE_NUMBER“从列表中验证”属性设成否。 3.在 Form Level 的 WHEN-NEW-FORM-INSTANCE Trigger 初始化弹性域 FND_KEY_FLEX.DEFINE(BLOCK => 'BlockName ', FIELD => 'GL_CODE_NUMBER', ID => 'CODE_COMBINATION_ID', DESCRIPTION => 'GL_CODE_DESC', APPL_SHORT_NAME => 'SQLGL', CODE => 'GL#', NUM => 101, --STRUCTURE Number REQUIRED => 'N' ); 说明:APPL_SHORT_NAME 查找:系统管理员=>应用=>注册 CODE 查找:应用开发员=>键=>注册 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 121 NUM 查找:总帐超级管理员=>设置=>财务系统=>帐簿=>定义 4.可以在 GL_CODE_NUMBER 项的 WHEN-VALIDATE-ITEM 触发器中,对所选择的科目进行核查。 IF : BlockName.CODE_COMBINATION_ID = -1 then fnd_message.debug('所选科目组合无效!'); raise form_trigger_failure; end if; 5、完成效果: 键弹性域与说明性弹性域明显的区别。就是使用键弹性域的数据表只保留了 ID。以会计科目弹性域为例, 数 据 表 只 保 存 了 ID( 即 CODE_COMBINATION_ID, 这 个 字 段 是 对 应 会 计 科 目 的 ID)。 GL_CODE_NUMBER、GL_CODE_DESC 都只是控制块的 ITEM,通过 CODE_COMBINATION_ID 来查询 对应的科目。 EBS11i Form 开发实现 MVC 模式 一个同事在的视频会议上提到,在做 EBS 开发时,要用 MVC 模式来满足不断在变化的业务需求。很 有意思的话题,值得展开讨论。JAVA 设计模式的书偶看了 N 次,但只会在 JAVA 应用,但从未想过在其 他的 4GL 编程语言实现。以下是我的理解。 首先,按 JAVA 的那一套,MVC 是指 MODEL(模型层)、VIEW(表现层)、CONTROLLER(控制层) 的缩写。编写软件为什么分层,这个是软件工程的需求。也就是说分层的目的是提高软件的可维护性,避 免“动一牵百”的修改软件。 其次,按照这种软件分层原理,在 JAVA 中比较容易实现。Struts 就是这类比较典型应用。模型层可以 用实体类来实现,表现层可以用JSP+STRUTS 标签实现,控制层可以 action 类实现。那么在ORACL FORMS 如何实现分层呢?以下对号入座的想法。 MODEL 用 数 据 块 来 实 现 、 VIEW 用 FORM 界 面 实 现 、 控 制 层 呢 ? MVC 中 最 为 关 键 的 是 CONTROLLER。因为 CONTROLLER 实现业务逻辑与流程流转。并且按照设计模式的要求,CONTROLLER Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 122 又被分成若干小层,如 DAO 层、SERVICES 层等等,并且带来的“面向接口编程”之方法。ORACLE FORM 实现业务逻辑与界面分离是通过程序单元来实现,控制层具体表现在 ORACLE FORMS 当中没有,只能通 过繁琐的 PL/SQL 语句实现。 不知道这种理解对不对。这个话题是很有意思的,大家觉得呢?欢迎讨论哦。 EBS 预警功能自定义开发 EBS 预警分为事件预警和定期预警。预警功能非常强大,本例以订单录入后,以邮件的形式通知财务主管 进行“订单登记”审核。 1、 定义预警。操作路径:预警系统管理器=>预警=>定义 2、 编写 SQL 语句(注:SQL 必须包含 INTO、where rowid=:rowid) select ORDER_NUMBER into &order_number from OE_ORDER_HEADERS_all head WHERE rowid = :ROWID 3、 点击上图中的“活动按钮”。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 123 4、 点击上图中的“活动详细资料”按钮。 5、 定义活动集。 点击“活动集”按钮 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 124 6、 预警安装 点击“预警详细资料”按钮。 7、 完成效果如下: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 125 EBS 自定义邮件通知 前面已经介绍过了 EBS 预警的功能,事实上 Oracle 数据库本身就有提供 UTL_SIMPLE(ORACLE10g 以后变成 UTL_MAIL)包来实现邮件发送的功能,利用这个包可以开发出更灵活东西来。首先要安装 utlsmtp.sql、utltcp.sql 这两个包。 ①发送带有 URL 邮件代码 declare p_sender varchar2(30) := 'cho @hek.cn'; p_recipient varchar2(30) := 'jia @hek.cn'; p_subject varchar2(50) := '使用 PL/SQL 发送邮件'; p_body long := '这是邮件正文内容啦!我又来啦!!!进行 ORACLE ERP '; mail_conn utl_smtp.connection; mail_host varchar2(15) := 'mail.hek.cn'; user_name varchar2(156) := 'chongdong_wang@hek.cn'; user_pwd varchar2(156) := '***'; begin --创建一个 TCP MAIL 连接 mail_conn := utl_smtp.open_connection(mail_host, 25); --ehlo 与 helo 的区别:是否对邮件主机进行登陆认证 --utl_smtp.helo(main_conn,mail_host); utl_smtp.ehlo(mail_conn, mail_host); --登陆认证语句 utl_smtp.command(mail_conn, 'AUTH LOGIN'); --对用户及密码进行加密 utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_name))); utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_pwd))); --指定发件人 utl_smtp.mail(mail_conn, p_sender); --指定收件人 utl_smtp.rcpt(mail_conn, p_recipient); --开始写邮件内容 utl_smtp.open_data(mail_conn); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 126 --指定显示的发件人,注意这边的显示的发件人可以上面指定发件人不同 --这实际上是 SMTP 协议的缺陷,也是造成垃圾邮件主要原因 utl_smtp.write_data(mail_conn, 'From:' || p_sender || utl_tcp.CRLF); utl_smtp.write_data(mail_conn, 'To:' || p_recipient || utl_tcp.crlf); --邮件主题:中文必须进行编码转换,否则会乱码 utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert('Subject:' || p_subject || utl_tcp.CRLF, 'ZHS16GBK'))); --设置邮件内容模式为 HTML,也可以直接设置文本 Content-Type:text/plain utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GBK' || utl_tcp.CRLF, 'ZHS16GBK'))); utl_smtp.write_data(mail_conn, utl_tcp.CRLF); --邮件正文 utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert(p_body, 'ZHS16GBK'))); --关闭连接 utl_smtp.close_data(mail_conn); utl_smtp.quit(mail_conn); exception when utl_smtp.transient_error or utl_smtp.permanent_error then utl_smtp.quit(mail_conn); raise_application_error(-20000, sqlerrm); when others then raise_application_error(-20001, 'The send mail was error ' || sqlerrm); end; ②发送带有附件的邮件 declare p_sender varchar2(30) := '*****@he.cn'; p_recipient varchar2(30) := '******* @he.cn'; p_subject varchar2(50) := 'PL/SQL 发邮件、带链接、带附件'; p_body long := 'PL/SQL 发邮件、带链接带附件
这是邮件正文内容啦!这是带附件的啦!< br>

进行 ORACLE ERP'; mail_conn utl_smtp.connection; mail_host varchar2(15) := '*******.cn'; user_name varchar2(156) := '*******.cn'; user_pwd varchar2(156) := '*********k'; --发附件要用到的变量 L_FIL BFILE; L_FILE_LEN NUMBER; L_MODULO NUMBER; L_PIECES NUMBER; L_FILE_HANDLE UTL_FILE.FILE_TYPE; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 127 L_AMT BINARY_INTEGER:=672*3;/* ensures proper format; 2016 */ L_FILEPOS PLS_INTEGER:=1;/* pointer for the file */ L_CHUNKS NUMBER; L_BUF RAW(2100); L_DATA RAW(2100); L_MAX_LINE_WIDTH NUMBER:=54; L_LINE VARCHAR2(1000); L_MESG VARCHAR2(32767); BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD'; FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF; LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' || utl_tcp.CRLF; --发送带有附件邮件,MIME 必须设为 multipart/mixed MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary=“'|| BOUNDARY || '“'; begin --创建一个 TCP MAIL 连接 mail_conn := utl_smtp.open_connection(mail_host, 25); --ehlo 与 helo 的区别:是否对邮件主机进行登陆认证 --utl_smtp.helo(main_conn,mail_host); utl_smtp.ehlo(mail_conn, mail_host); --登陆认证语句 utl_smtp.command(mail_conn, 'AUTH LOGIN'); --对用户及密码进行加密 utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_name))); utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_pwd))); --指定发件人 utl_smtp.mail(mail_conn, p_sender); --指定收件人 utl_smtp.rcpt(mail_conn, p_recipient); --开始写邮件内容 utl_smtp.open_data(mail_conn); utl_smtp.write_data(mail_conn, 'From:' || p_sender || utl_tcp.CRLF); utl_smtp.write_data(mail_conn, 'To:' || p_recipient || utl_tcp.crlf); utl_smtp.write_data(mail_conn, 'Subject: ' || p_subject || utl_tcp.crlf); --中文编码转换 utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Subject:' ||p_subject ||utl_tcp.CRLF,'ZHS16GBK'))); utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Type:'||MULTIPART_MIME_TYPE||ut l_tcp.CRLF,'ZHS16GBK'))); --utl_tcp.CRLF 数据流行尾符 utl_smtp.write_data(mail_conn, utl_tcp.CRLF); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 128 --邮件正文 utl_smtp.write_data(mail_CONN, FIRST_BOUNDARY); utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GB2312' ||utl_tcp.CRLF, 'ZHS16GBK'))); utl_smtp.write_data(mail_conn, utl_tcp.CRLF); utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert(p_body, 'ZHS16GBK'))); utl_smtp.write_data(mail_conn, utl_tcp.CRLF); --附件格式 utl_smtp.write_data(mail_CONN, FIRST_BOUNDARY); utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GB2312' ||utl_tcp.CRLF, 'ZHS16GBK'))); utl_smtp.WRITE_RAW_DATA(mail_conn, UTL_RAW.CAST_TO_RAW(CONVERT('Content-Disposition' || ':' ||'attachment;filename=“'||'qq.xls“' || utl_tcp.CRLF, 'ZHS16GBK'))); utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Transfer-Encoding:base64'||utl_tcp.CR LF,'ZHS16GBK'))); utl_smtp.write_data(mail_CONN, UTL_TCP.CRLF); --附件二进制流 BEGIN --把附件分成多份,这样可以发送超过 32K 的附件 L_FILEPOS := 1; --CREATE OR REPLACE DIRECTORY U_FIEL AS '/data/book/' --qq.xls 附件放在 ORACLE 服务器/data/book/下,注意大写 L_FIL := BFILENAME('U_FIEL', 'qq.xls'); L_FILE_LEN := DBMS_LOB.GETLENGTH(L_FIL); L_MODULO := MOD(L_FILE_LEN, L_AMT); L_PIECES := TRUNC(L_FILE_LEN / L_AMT); IF (L_MODULO <> 0) THEN L_PIECES := L_PIECES + 1; END IF; DBMS_LOB.FILEOPEN(L_FIL, DBMS_LOB.FILE_READONLY); DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF); L_DATA := NULL; FOR I IN 1 .. L_PIECES LOOP L_FILEPOS := I * L_AMT + 1; L_FILE_LEN := L_FILE_LEN - L_AMT; L_DATA := UTL_RAW.CONCAT(L_DATA, L_BUF); L_CHUNKS := TRUNC(UTL_RAW.LENGTH(L_DATA) / L_MAX_LINE_WIDTH); IF (I <> L_PIECES) THEN L_CHUNKS := L_CHUNKS - 1; END IF; utl_smtp.write_raw_data(MAIL_CONN, UTL_ENCODE.BASE64_ENCODE(L_DATA)); Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 129 L_DATA := NULL; IF (L_FILE_LEN < L_AMT AND L_FILE_LEN > 0) THEN L_AMT := L_FILE_LEN; END IF; DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF); END LOOP; DBMS_LOB.FILECLOSE(L_FIL); EXCEPTION WHEN OTHERS THEN DBMS_LOB.FILECLOSE(L_FIL); utl_smtp.WRITE_DATA(mail_CONN, UTL_TCP.CRLF); RAISE; END; --结束处理二进制附件 --关闭连接 utl_smtp.close_data(mail_conn); utl_smtp.quit(mail_conn); exception when utl_smtp.transient_error or utl_smtp.permanent_error then utl_smtp.quit(mail_conn); raise_application_error(-20000, sqlerrm); when others then utl_smtp.quit(mail_conn); DBMS_OUTPUT.put_line(sqlerrm); end; EBS 配置文件(Profile)常用设置 序 号 设置 值 功能 1 Self Service Personal Home Page mode Personal Home Page 设置职责的显示方式 2 ICX:Session Timeout 180000 设置活动用户多少时间注销 3 Java Color Scheme blaf 设置 FORM 的背景色彩 4 Java Look and Feel Oracle 设置 FORM 的样式 5 导出 MIME 类型 application/vnd.ms-excel 设置菜单 EXPORT 文件的格式。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 130 设置菜单导出的文件格式 操作路径:系统管理员=>安装=>浏览器选项 操作路径:系统管理员=>配置文件=>系统 这样就可以实现直接导出为 excel 文件了。 IE 打不开 EBS ①症状:一直停留在 EBS 弹出式窗体,显示正在加载 FORMS。 解决:安装 SUN JDK1.4,并将 JVM.DLL 替换掉 X:\Program Files\Oracle\JInitiator 1.3.1.21\bin\hotspot ②症状:无法查看工作流状态图(view datagram) 解决:安装微软的虚拟机 msjavx86.exe。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 131 一个职责下打开多个 FORM 界面 一个职责下打开多个 FORM 界面。本来是一个不起眼,但又很好用的功能,却折腾了很久。一直以为是配 置文件设置。后来发现,如此简单。 把上述勾选上,那么一个职责下面只能打开一个 FORM 界面 获取 EBS 的查询语句 1.帮助=>诊断=>检查 2.在块中输入 SYSTEM、在字段中输入 LAST_QUERY,就可以得到查询的 SQL 语句。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 132 EBS 调用 JSP 页面 1、在 EBS 环境中使用 JAVA,有两种方式。1.使用 OAF,这也是 ORACLE 推荐的。2.直接开发 JSP, 然后在 FORM 中调用。第一方法比较死,必须得使用 ORACLE 的框架。第二方法的发挥余地比较大了。 本文只讲述第二方法。 首先,开发好 JSP 页面。例:test.jsp <%@page contentType=“text/html;charset=GBK“%> <%@page import = “java.sql.*“ %> <%@ page import = “java.sql.DriverManager“ %> <%@ page import = “java.sql.ResultSet“ %> <%@ page import = “java.sql.Statement“ %> <%@ page import = “java.sql.Connection“ %> <%String sDriver = “oracle.jdbc.driver.OracleDriver“; String sConnStr = “jdbc:oracle:thin:@192.168.211.2:1561:deve“; Connection conn = null; try{ Class.forName(sDriver); conn = DriverManager.getConnection(sConnStr,“apps“,“apps“); conn.setAutoCommit(false); }catch(Exception ex){ ex.printStackTrace(); }%> ORALCE-EBS EBS 开发 JSP 页面! This is a test page! <%Statement stmt = null; ResultSet rs =null; try{ if (conn != null){ stmt = conn.createStatement(); stmt.execute(“insert into hek_test_a values(1,sysdate,'测试数据 QQ11')“); rs = stmt.executeQuery(“select col1,col2,col3 from hek_test_a for update“); out.println(““); while(rs.next()){ out.println(““); } out.println(“
“); out.println(“输出:“+rs.getString(1)); out.println(“
“); conn.commit(); } }catch(Exception ex){ ex.printStackTrace(); }finally{ try{ Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 133 rs.close(); stmt.close(); conn.close(); }catch(Exception exsql){ exsql.printStackTrace(); } } %> 其次,至 EBS 服务器的$OA_HTML 目录下创建子目录 cd $OA_HTML mkdir ebs_java 然后,将 test.jsp 上传到刚才创建的目录。这样就可直接访问了。 访问链接:http://EBS 域名:端口/OA_HTML/ebs_java/test.jsp 效果如下: 2、事实上根据上述方法,完全可客制化一下 EBS 的登录页面。 EBS 的 index.jsp 一般存放在$OA_HTML 的前一级 portal 目录下。 例:$OA_HTML 为/data/deve/devecomn/html 那么 index.jsp 就存放在/data/deve/devecomn/portal 下。事实这个目录也 EBS11i 应用服务器的根目录。 OAF 页面查看历史信息 Form 的界面查看历史信息的功能,很好用。OAF 有没有类似的功能呢?答案是肯定的。下面以 workflow 的审批页面为例来说明。 1、设置配置文件 操作路径:系统管理员=>配置文件=>系统 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 134 定义文件夹的管理权限 以销售订单的文件夹管理为例。操作路径:订单管理=>订单:退货=>销售订单 在销售订单界面新建二个文件夹 TESTA(无销售单价)、TESTB(有销售单价)。TESTA 文件为默认公用的 文件夹。TESTB 是有权限管理的。 切到应用开发员的职责,删除掉用户自定义的文件夹。只保留 TESTA、TESATB 两个文件夹。 操作路径:应用开发员=>应用=>管理文件夹 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 135 然后以其他用户登陆 EBS,会发现文件夹只能使用 TESTA,并且不调整文件夹了。 而以前面定义文件夹的用户登陆 EBS,会发文件夹功能仍然可用。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 136 这边只能基于用户来管理,事实上,还可以根据职责来管理文件夹的权限。 判断 EBS 提交请求是否成功 例如:导入 AP 发票,需要根据是否成功生成发票来做下一步的动作。 FUNCTION GET_REQUEST_STATUS(G_REQ_ID IN NUMBER) RETURN BOOLEAN IS --通过传入请求号来判断请求运行 结果是否正常 REQ_ID NUMBER := G_REQ_ID; WAIT BOOLEAN; PHASE VARCHAR2(2000); STATUS VARCHAR2(2000); DEVPHASE VARCHAR2(2000); DEVSTATUS VARCHAR2(2000); MESSAGE VARCHAR2(2000); V_WAIT_TIME NUMBER; BEGIN IF REQ_ID = 0 THEN RETURN(FALSE); ELSE DEVPHASE := 'START'; V_WAIT_TIME := 0; WHILE DEVPHASE <> 'COMPLETE' AND V_WAIT_TIME < 3600 LOOP WAIT := FND_CONCURRENT.WAIT_FOR_REQUEST(REQ_ID, 10, 0, PHASE, STATUS, DEVPHASE, DEVSTATUS, MESSAGE); V_WAIT_TIME := V_WAIT_TIME + 1; Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 137 END LOOP; FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------DEVPHASE1=------'||DEVPHASE||' DEVSTATUS='||DEVSTATUS); IF DEVPHASE = 'COMPLETE' AND DEVSTATUS = 'NORMAL' THEN FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------true------'); RETURN(TRUE); ELSE FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------false------'); RETURN(FALSE); END IF; END IF; --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------DEVPHASE2=------'||DEVPHASE); EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------DEVPHASE exception==------'||DEVPHASE); RETURN(FALSE); END GET_REQUEST_STATUS; FORM 更新多表视图 如果是数据源是一张多表视图,但只更新数据到一张表,这种情况比较好处理。 先将数据块的 Query 表与 DML 目标表分别设置, 然后将非 DML 表的字段设置 Query only 设置“是”。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 138 如果数据源是一张多表视图,并且要将数据更新到多张表中。要实现此功能方法很多。 方法一:可以手工在 form 中编写 insert、update、delete、lock 触发器。此方法代码量较大。 方法二:使用基于视图的 instead of 触发器。本文采用此方法。 1.创建两张表 CREATE TABLE T1(T_ID INT,T1_NAME VARCHAR2(30)); CREATE TABLE T2(T_ID INT,T2_ADDRESS VARCHAR2(50)); 2.创建一张含有多张表的视图 create or replace view T1_T2_V as SELECT T1.T_ID,T1.T1_NAME,T2.T2_ADDRESS FROM T1,T2 WHERE T1.T_ID=T2.T_ID; 3. 创建一个 Instead of 视图触发器 create or replace trigger tr_t1 instead of insert or update or delete on T1_T2_V for each row begin declare begin if inserting then insert into T1 values(:new.T_ID,:new.T1_NAME); insert into T2 values(:new.T_ID,:new.T2_ADDRESS); elsif updating then update T1 set T_ID=:new.T_ID,T1_NAME=:new.T1_NAME where T_ID=:old.T_ID; update T2 set T_ID=:new.T_ID,T2_ADDRESS=:new.T2_ADDRESS where T_ID=:old.T_ID; else delete from T1 where T_ID=:old.T_ID; delete from T2 where T_ID=:old.T_ID; end if; end; end tr_t1; 4.按标准的 FORM 开发步骤,修改 template。 将数据块的查询源调成前面开发的 VIEW,DML 目标表留空。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 139 编译及注册到 EBS 环境中。发现 insert 可以成功,但 update、delete 出错。 解决:将该数据块的键模式设成“可更新” OK,这样就完成 Form 的多表视图更新啦。总结一下,就四大点: u 设置数据块属性为“允许使用主键”; u 设置一到多个主键项目; u 将数据块的“键模式”属性设置为可更新。 u 编写一个 instead of 触发器。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 140 WorkFlow EBS WorkFlow WorkFow builder 安装 一接触 EBS 时,就听说 workflow 是 ORACLE EBS 的精华,可能是太精华了,学习起就觉得特神奇。 安装 WorkFlow 费了一些时间,因为是安装 WorkFlow,系统会挂掉。可能是硬件与 WorkFlow 不是很兼容, 所以只能搞个虚拟机来安装。 Workflow 查看工作流权限 Workflow Owned By 没法选择。 解决: 以 sysadmin 用户登陆,进入 workflow administrator 职责。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 141 修改成“*”。 注销当前用户,重新登陆 ebs。 Workflow Builder 打开 PO 审批流时报错 错误: 这个问题提了 SR 后才解决了。原因是这个错误 323: Invalid message subject '&SUPPLIER 已 &ACCEPTANCE_RESULT &DOCUMENT_DISPLAY_NAME &DOCUMENT_NUM_REL, &REVISION_NUMBER. '. Subjects are required, and have a maximum length of 240 byte 逐一排查下来: select * from WF_ACTIVITIES_TL where length(display_name) <> length(trim(display_name)) and item_type = 'POAPPRV' select * from WF_ACTIVITIES_TL where length(description) <> length(trim(description)) and item_type = 'POAPPRV' select * from WF_MESSAGES_TL Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 142 where type = 'POAPPRV' and length(display_name) <> length(trim(display_name)) select * from WF_MESSAGES_TL where type = 'POAPPRV' and length(subject) <> length(trim(subject)) 解决:去掉多余的空格,就可以了。 update WF_MESSAGES_TL set subject = trim(subject) where type = 'POAPPRV' and length(subject) <> length(trim(subject)) Workflow 启动报错 客制化使用的 workflow 启动时报错。 问题分析:造成这个错误的原因,主要 workflow 业务程序出错了,如:变量赋值错误等。 Oracle BPM BPM 安装 Business Process Management,业务流程管理。较之 WorkFlow,WorkFlow 是基于 Oracle 数据库的, 语言也是依赖于 PL/SQL。而 Oracle BPM 是基于 JAVA 的,不再依赖于 Oracle 数据库。所以,可以这样认 为 Oracle BPM 是 WorkFlow 的升级版。 Oracle BPM 可以直接从官方网站下载得到。先安装 JDK,然后安装 BPM。打开 BPM 一看,Oracle BPM 不过是 Eclipse 为基础开发的插件而已。另外,Oracle SOA 套件还整合 BEA 的东东,演变成了 BPEL (业务流程执行语言)。Workflow、BPM、BPEL、BPA 概念层出不穷,但核心还是业务流程。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 143 BPM 基本使用 先占位,以后补。 Discoverer 工具 我刚接触 Discoverer 时,首先就想到了 Excel 的数据透视表,这两个工具所实现的功能太象了。这也 是为什么 Discovere 被财务人员所亲赖的。因为 ERP 不管怎么深入应用,涉及到最终的财务数据。还是离 不开 EXCEL 的。Discoverer 分成两个部分,一个是 Administration Edition,主要是开发人员用来写 SQL 语 句的。另一个是 Desktop Edition,主要终端用户在使用。 Discoverer 安装设置 1、Discoverver 安装最好不要与 FORM、REPORT 安装在同一目录。所以安装 DISCOVERER 时,先备份 注册表。然后将“HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE”项删除,再选择不同的目录进行安 装 DISCOVERER。2、安装完成后,要先设置一下连接方式,这样才可以使用 EBS 用户连接到 Discoverer 服务器。 操作路径:Tools=>Options Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 144 3、打开 Discoverer 连接时,选择“Oracle Application User”. 1、首次连接 DISCOVERER 时,会提示创建 EUL(End User Layers)。 Discoverer 快速入门示例 1、创建一个 Business Area。 2、选择用户,EBS 开发一般选择 APPS。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 145 3、选择数据库对象,这一步很奇怪。一定要选择一个表或视图之类的对象,否则过不去。 4、设置参数,一般使用默认的即可。 5、命名 AREA。 6、为了练习,删除刚才自动创建的 FOLDER,并手工创建一个 FOLDER。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 146 7、最终效果如下: 注:Discoverer 中 Business Area 就类似于 Scheme,Folder 类似于表或视图,Item 类似与 Collumn 8、 授权分为 AREA 授权、工作簿授权两种方式。 8.1AREA 授权。随便说一下,如果用户没有 EBS 任何职责权限,那么 DISCOVERE DESKTOP 也连 接不了 ERP。所以要 EBS 用户至少要拥有一上职责,才能进行 DISCOVERER ADMIN 授权。 8.2DISCOVERER DESKTOP 工作簿共享 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 147 9、使用 Discoverer Desktop 登陆。登陆后,创建一个新的工作簿。 Discoverer 工作表数值型显示 数值型的字段都自动显示为 count。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 148 解决:在 discoverer aministartor 中,如果不想实现统计的列,可以选择 Detail 通过工作簿找到对应的 Discoverer 职责 这是一个 SAMPLE 的问题,却很实用。因为用户只会反映,我的**报表不能用,而不会说**职责下的报 表不能用。在 Discoverer Desktop 查看职责,然后就可以 Discoverer Aadministrator 找到这个职责下的工作 簿 SQL 语句。 Discoverer JOIN 之使用 JOIN 可以将多个 FOLDER 关联起来,这个功能同数据库中的关系表是一样的。当然不同的 FOLDER 也就 得有相同的 KEY,方可使用 JOIN。以下以两个 FOLDER 使用 JOIN 为例。 1、首先我们要打开两个 FOLDER,如果这两个 FOLDER 属于不同的 AREA,那就要打开这些 AREA。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 149 2、打开 FOLDER 后,选择相应的 ITEM,并创建 JOIN 3、相应 JOIN 的关联规则。 注:这个 JOIN 的规则与数据库的主从表的概念是一致,并且不能颠倒。这个问题折腾了我半天时间, 后来在一个同事的帮助下,才发现的。尽管 MASTER FOLDER 与 DETAIL FOLDER 颠倒,也可以得到 正确的数据,但是在交叉表格式一个 NUMBER 字段时,却怎么也格式化不成功,导致不能使用总计。 4、使用 Discoverer Desktop 登陆。创建一个新的工作簿,并将关联表相应的字段引用过来。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 150 Discoverer 数值型无法正确显示 可以尝试,按以下面方法进行解决。 1.在 DISCOVERER DESKTOP 进行格式化(选择要格式化的 ITEM,然后点击右键)。 2.在 DISCOVERER ADMIN 进行格式化。 3.在 SQL 语句中格式化 在写 SQL 语句时,加入 TO_NUMBER(),对于 SUM 的字段加入 NVL()。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 151 Discoverer 上钻/下钻 1、 新建 Hierarchies 2、设置层级关系,例:一个配送商可配送多个订单,一个订单又可包含多少订购产品。 3、设置平行关系。例:产品编号与产吕名称就是一对一的平行关系。 4、在 Discoverer Desktop 下面创建交叉表,便可实现钻探。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 152 Discoverer 条件 LOV 效果: 开发步骤:先将此 discoverer 报表开发好,然后在该字段上创建 itemclass Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 153 点击完成,itemclass 创建完成。 Discoverer 分类汇总求 AP 供应商余额 PO 产生的应付帐款—暂估科目是不带税,为了简化起见,可以手工加上税额。 1.PO 入库产生的基本科目: 借:物资采购 贷:应付货款—暂估 AP 供应商余额有两种付款流程:1.预付流程,2.非预付流程。 2.预付款流程产生的基本科目: 预付款(购买货物前,预付货款): 借:预付帐款 贷:应付帐款 预付款发票(购买货物后,核销货款): 借:应付帐款—暂估 贷:预付帐款 3.非预付款流程产生的基本科目: 标准发票(购买货后,收到的发票): 借:应付帐款—暂估 贷:应付帐款 付款(购买货物后,支付货款): 借:应付帐款 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 154 贷:预付帐款 只要编写简单 SQL 语句,然后在 DISCOVERER 使用交叉表+分类小许,就可完成分类汇总表,如下: Oracle DBI 工具 在 EBS 中 DBI 是默认安装好了。Daily Business Intellignece:每日商务智能。它是一个 BI 工具,可以实现 灵活的报表查询和用户动态查询。 EBS DBI 架构图: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 155 DBI 工具之配置 查询 DBI 版本: SELECT MAX(decode(bug_number, '3086726', 'DBI 6.0 - RUP4 Patch number: 3086726', '4084433', 'DBI 6.0.5 Patch number: 4084433', '4054609', 'DBI 7.0.1 Patch number: 4054609', '4143498', 'DBI 7.1 Patch number: 4143498', '4887502', 'DBI 7.2 Patch number: 4887502', '5404886', Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 156 'DBI 7.3 Patch number: 5404886')) “Current DBI Family Pack level:“ FROM ad_bugs WHERE bug_number IN ('3086726', '4084433', '4054609', '4143498', '4887502', '5404886') 加入职责: 操作路径:System administrator=>Security=>User=Define 加入 Daily Business Intelligence Administrator、Daily Business Intelligence Designer 职责 定义菜单:HX_DBI_REPORTS 定义职责: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 157 加入职责: DBI 之报表开发 DBI 简单报表开发 本例以 OPM 每月产量分析表为例子,讲述 DBI 报表开发。 1.创建 OPM 产量视图: create or replace view hk_product_output_monthly as select trunc(gbh.ACTUAL_CMPLT_DATE, 'mm') ACTUAL_CMPLT_DATE, sum(gmd.actual_qty) actual_qty, max(gmd.item_um) item_um from GME.GME_BATCH_HEADER gbh, GME.GME_MATERIAL_DETAILS gmd where gbh.batch_id = gmd.batch_id and gmd.line_type = 1 --产品 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 158 and gbh.ACTUAL_CMPLT_DATE is not null --and gbh.batch_no = '00009337' group by trunc(gbh.ACTUAL_CMPLT_DATE, 'mm') 2.创建 DBI 报表 点击 Report Designer 点击 Create 按钮 输入相应参数:View Name 为前面定义的视图;Ttile 为报表的名称 定义数据源映射 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 159 创建报表布局 定义图表参数 保存并应用 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 160 点击 Preview 按钮 出错: 解决:在 HK_DBI_REPORTS 菜单加入上述报表 再次点击 Preview 按钮,出错: 解决:修改报表,点击 UPDATE 按钮 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 161 修改如下: 保存及应用后,最终效果如下: Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 162 DBI 维度报表开发 本例以采购单价趋势为例子,讲述带有维度的 DBI 报表开发。 DBI 维度:直面来理解就是参数的 LOV。 创建维度的视图(列名必须定义 ID、VALUE,允许定义其他列) create or replace view hk_item as select msi.SEGMENT1 id, msi.SEGMENT1 VALUE,msi.DESCRIPTION from apps.MTL_SYSTEM_ITEMS_FVL msi where msi.organization_id = 123; 点击 Create 按钮,创建维度 输入维度参数 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 163 创建维度对象 输入维度对象属性 定义维度对象显示属性 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 164 定义维度对象的数据来源 创建报表的数据源视图 create or replace view hk_po_list as select msi.SEGMENT1 物料编号, ph.SEGMENT1 采购单编号, ph.PO_HEADER_ID 采购单ID, ph.VENDOR_ID 供应商ID, ph.CREATION_DATE 采购日期, ph.AGENT_ID 采购员ID, pap.first_name || ' ' || pap.last_name 采购员, pv.vendor_name 供应商, pl.item_id 物料ID, pl.item_description 物料名称, pl.unit_price 采购单价, Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 165 pl.quantity 采购数量 from po.po_headers_all ph, po.po_vendors pv, po.po_lines_all pl, apps.MTL_SYSTEM_ITEMS_FVL MSI, HR.PER_ALL_PEOPLE_F pap where ph.VENDOR_ID = pv.vendor_id and ph.po_header_id = pl.po_header_id and MSI.INVENTORY_ITEM_ID = pl.item_id and pap.person_id = ph.agent_id and nvl(pap.effective_end_date,sysdate)>=sysdate and msi.organization_id=123 --and ph.segment1='25639' --and rownum<100; 创建 DBI 报表 输入报表参数 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 166 定义数据源映射 定义维度参数 定义图表 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 167 定义表格 最终效果 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 168 Oracle BIEE 工具 BIEE 工具之安装 BI,Bussiness Intelligence 商业智能。这个概念有点铺天盖地,每个 Application Vendor 都在热捧。BI 最基本的原理就是要实现 OLAP(联机分析处理,面向数据仓库 DATAWHSE)。而平常做的 FORM 开发, 基本上是基于 OLTP(联机事务处理,面向数据库 DATABASE)。原以为 Oracle Diccoverer 也是 BI 的范畴, 可 Oracle 却明确把 Discoverer 列入 C/S-BI 工具的范畴。要实现 WEB 版的 ROLAP、MOLAP,就非得使用 Oracle BIEE。实际上 Oracle 的 BI 工具也是很乱,有 C/S 的 DISCOVERER,也有 WEB 的 DISCOVERER。 还有 Oracle BIEE。另外还有收购其他公司的 BI 工具。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 169 从 Oracle 官方下载后,得到一个 600 多 M 的 biee_windows_x86_101340 文件。安装 BIEE 之前要先安 装 JDK5,并设置 JAVA_HOME 变量,因为 BIEE 也是运行在 OC4J 下的。其他的安装步骤一路 NEXT,就 不多说了。 安装完后,在 Services.msc 检查一下 Oracle BI Java Host、Oracle BI Presentation Server、Oracle BI Scheduler、Oracle BI Server 这四个服务是否启动。正常情况 Oracle BI Scheduler 是没有启动的,因为要先 配置一下。开始菜单=>Oracle BIEE=>Job Manage。然后 File=>Configuration Options 然后执行 X:\OracleBI\server\Schema 下的 SAJOBS.Oracle.sql(不同的数据库,选择相应的脚本)。这样 配置完后,启动 Oracle BI Scheduler。然后在开始菜单选择”Welcome to BIEE”,就可以在 IE 登陆了(初始用 户及密码:Administrator/ Administrator)。 BIEE 工具之体念 登陆界面。 设置数据仓库维度(物理层、逻辑层、表现层,这个功能与 Discoverer Aministrator 类似。当然功能更 强大)。 终端用户操作界面(是基于 WEB 的,功能与 Discoverer Desktop 类似。但基于 WEB 方便多了。) Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 170 BI Publisher 工具之体念 BI Publisher 是包含在 BIEE 套件中的。在开始菜单打开 BI Publisher。用户及密码:Administrator/ Administrator,BI Publisher 登陆页面与 EBS R12 非常类似。 配置 JDBC,然创建文件夹、报表。就可实现与 DISCOVERER 一样的“页明细表”了。当然,使用也 比 DISCOVERER 方便很多。基于 WEB 的界面,感觉非常好。 选择相应的数据源,也就刚才创建的 JDBC 连接名称。这一点比 Discoverer 方便多了,Discoverer 只能 连接 Oracle 数据库。而 BI Publisher 通过 JDBC 可以所有的数据库。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 171 查询速度上比 Discoverer 快一些。因为 Discoverer 每次都是通过提取数据库表,而 BI Publisher 是将数 据表转成 XML,缓存在 OC4J 中,然后传给过页面。 BI Publisher 也可以实现交叉报表,但功能相对 Discoverer 来说少一些了。这一点可以通过 BI For Excel 的 插件来补充。安装完 Oracle BI Publisher Desktop,就可通过 Excel 来访问 BI Publisher 了。 数据导入工具 Dataload Dataload 工具本身很小,但在 ERP 初期上线时,却非常有用。特别是对于一些基础数据要成批导入 ERP 时,显得非常有用。DataLoad 使用也非常简单,直接参照使用文档就可以掌握了。Dataload 企业版与 标准版的最大区别就是,导数据时不会全部占据电脑。这是我的第一感觉。当然速度方面也更好,收费的 总比免费的好。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 172 Loadrunner Loadrunner 是一款压力测试工具,但也可以用来数据导入。在 ORACLE EBS 中使用 LR 导入数据,必 须先开启 GCI 的访问模式。 1. EBS1.5.10 启用 CGI 访问模式。 1.1 修改$APPL_TOP/admin/_.xml 文件,如下: ON 1.2 运行 autoconfig 命令 # cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME # sh adautocfg.sh 1.3 以 GCI 方式登陆 EBS http://IP_DOMAIN:8040/dev60cgi/f60cgi 2.Loadrunner 录制脚本分为三个步骤:INIT 部分,Action 部分,End 部分。 Init:初始化部分,主要操作连接服务器,进入相关责任功能,启动后运行一次; Action:活动部分,这部分代码会循环运行,具体导入数据的操作; End:结束部分,Action 循环指定次数后,运行该部分,可用来退出系统,该部分运行一次; 3.本文使用的 Loadrunner7.8 版本 3.1Loadrunner 只支持 IE,并且将 IE 设置如下: 将 IE 代理去掉 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 173 将第三方浏览器扩展失效。 3.2 启动 Loadrunner 选择协议 设置 RECORD 参数 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 174 参数说明: Application type:Internet Applications; Program to record:选择IE浏览器; URL Address:CGI登陆地址,后面加上“?record=names”; 本文为 http://erpdeve.hek.cn:8040/dev60cgi/f60cgi?record=names Record Into Action:Vuser_init; 点击OK按钮,会自动打开IE进行脚本录制。 进入 OPM 库存职责,然后打开主物料维护界面。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 175 Init 阶段结束,从 LR 菜单中选择 Action(Action 表示这部分代码会循环运行,即具体导入数据的操作)。 Oracle ADI ADI:Application Desktop Integration,主要功能就是通过 Excel 将数据导入 EBS。EBS11.5.9 以后 ADI 就改成了 WEB ADI 了。通过 ADI 大大简化了在 EXCEL 中开发工作量。 GL 日记帐导入 登陆 EBS 后,选择 Oracle Web ADI 职责(授权职责,就不用说了吧?),即可使用 WEB ADI。日记帐导入是 ORACLE EBS 本身就开发的模板,可以直接拿来使用。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 176 提示下载 EXCEL 文件,保存至本地后,将数据复制入 EXCEL 模板。同时,这样也可以实现在脱机状态下 操作日记帐。 EXCEL 设置一下宏及信任 VBA 工程。 生成模板,就可整理数据进去了。 Oracle ADI-客户化开发 见下面链接中的相关文档。 Author:JarWang(王重东) Email:Jarwang@sina.com QQ:Jarwang@sina.com 177 Other Notes Oracle EBS11i开发笔记:http://www.itpub.net/thread-1133804-1-1.html Oracle EBS11i表结构、API接口笔记:http://www.itpub.net/thread-1265230-1-1.html Oracle EBS--OAF开发笔记:http://www.itpub.net/thread-1236269-1-1.html Oracle Workflow与JBoss jBPM开发笔记:http://www.itpub.net/thread-1245869-1-1.html Oracle EBS11i OM-扩展开发:http://www.itpub.net/thread-1154690-1-1.html Oracle EBS WEB ADI开发笔记(抽丝拨茧篇):http://www.itpub.net/thread-1225495-1-1.html Oracle EBS-11i多节点安装、克隆等 (32bit):http://www.itpub.net/thread-1228782-1-1.html Oracle EBS-11i Database9i升级10g:http://www.itpub.net/thread-1256323-1-1.html Oracle EBS-R12安装、克隆等(64bit):http://www.itpub.net/thread-1129128-1-1.html Oracle EBS-R12学习笔记(从零开始):http://www.itpub.net/thread-1247010-1-1.html Oracle EBS Advanced Replication10g配置:http://www.itpub.net/thread-1216295-1-1.html Oracle EBS DataGuard10g配置: Oracle EBS Stream10g配置: Oracle10g OCP学习笔记:http://www.itpub.net/thread-1229073-1-1.html Oracle10g RAC安装:http://www.itpub.net/thread-1194959-1-1.html Oracle10g 基于ASM存储的异机恢复:http://www.itpub.net/thread-1208926-1-1.html PL/SQL操作EXCEL:http://www.itpub.net/thread-1101710-1-1.html MySQL5开发笔记:http://www.itpub.net/thread-1158177-1-1.html RHEL5.2内核升级:http://www.itpub.net/thread-1145199-1-1.html
还剩176页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 15 金币 [ 分享pdf获得金币 ] 5 人已下载

下载pdf

pdf贡献者

hong1987

贡献于2011-11-07

下载需要 15 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf