- 浏览: 668006 次
- 性别:
- 来自: 中山
文章分类
最新评论
-
wuhuizhong:
jFinal支持Rest风格吗?可以想spring mvc那样 ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
在jfinal中应如何获取前端ajax提交的Json数据?ht ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
jfinal如何处理json请求的数据:问题: 在某些api接 ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
Ubuntu14.04 安装 Oracle 11g R2 Ex ...
Oracle 11g release 2 XE on Ubuntu 14.04 -
alanljj:
这个很实用,已成功更新,谢过了!
odoo薪酬管理模块l10n_cn_hr_payroll
REF Cursors are cool. They allow you to encapsulate SQL queries behind a PL/SQL package API. For example, you can create a function called GET_EMPLOYEES that returns a SYS_REFCURSOR containing the employees in a specific department:
function get_employees (p_deptno in number) return sys_refcursor as l_returnvalue sys_refcursor; begin open l_returnvalue for select empno, ename, job, sal from emp where deptno = p_deptno; return l_returnvalue; end get_employees;
The client (an application written in Java, .NET, PHP, etc.) can call your API and process the returned REF Cursor just as if it was a normal result set from a SQL query. The benefits are legion. The client no longer needs to contain embedded SQL statements, or indeed know anything about the actual database structure and query text. Privileges on the underlying tables can be revoked. The API can be shared and reused among different clients, whether they are written in Java, .NET, or any number of other languages.
That is, unless your client is Oracle Application Express (Apex). Apex unfortunately lacks the ability to process REF Cursors, or, more accurately, you cannot create report regions in Apex based on REF Cursors. For standard reports, you have to either embed the SQL statement in the region definition, or return the SQL text string from a function (and hope that the string you built is valid SQL when it gets executed). For interactive reports, only embedded SQL statements are supported.
I dislike having to scatter literal SQL statements all around my Apex applications, and not be able to take advantage of a package-based, shared and reusable PL/SQL API to encapsulate queries. I submitted a feature request to the Apex team back in 2007, asking for the ability to base report regions on REF Cursors, but so far this has not been implemented.
The problem, as far as I know, is that Apex uses (and must use) DBMS_SQL to "describe" a SQL statement in order to get the metadata (column names, data types, etc.) for a report region. But not until Oracle 11g did DBMS_SQL include a function (TO_CURSOR_NUMBER) that allows you to convert a REF Cursor into a DBMS_SQL cursor handle. So, as long as the minimum supported database version for Apex is Oracle 10g, support for REF Cursors is unlikely to be implemented.
In the meantime, there are a couple of alternatives:
OPTION 1: PIPELINED FUNCTIONS
It's possible to encapsulate your queries behind a PL/SQL API by using pipelined functions. For example, the above example could be rewritten as...
create type t_employee as object ( empno number(4), ename varchar2(10), job varchar2(9), sal number ); create type t_employee_tab as table of t_employee; function get_employees (p_deptno in number) return t_employee_tab pipelined as begin for l_rec in (select empno, ename, job, sal from emp where deptno = p_deptno) loop pipe row (t_employee (l_rec.empno, l_rec.ename, l_rec.job, l_rec.sal)); end loop; return; end get_employees;
And used from Apex (in a report region) via the TABLE statement:
select * from table(employee_pkg.get_employees (:p1_deptno))
OPTION 2: XML FROM REF CURSOR
The DBMS_XMLGEN package can generate XML based on a REF Cursor. While this does not "describe" the REF Cursor per se, it does give us a way (from PL/SQL) to find the column names of an arbitrary REF Cursor query, and perhaps infer the data types from the data itself. A couple of blog posts from Tom Kyte explain how this can be used to generate HTML based on a REF Cursor.
So back to Apex, you could generate a "report" based on a PL/SQL region with code similar to this:
declare l_clob clob; l_rc sys_refcursor; begin l_rc := get_employees (:p1_deptno); l_clob := fncRefCursor2HTML (l_rc); htp_print_clob (l_clob); end;
It would also be possible to pass your own XLST stylesheet into the conversion function (perhaps an Apex report region template fetched from the Apex data dictionary?) to control the appearance of the report.
I put "report" in quotes above, because until the Apex team implements report regions based on REF Cursors, you will miss all the nice built-in features of standard (and interactive) reports, such as sorting, paging, column formatting, linking, etc.OPTION 3: JSON FROM REF CURSOR
Bear with me, I am finally getting to the point of this blog post.
JSON is cool, too, just like REF Cursors. It's the fat-free alternative to XML, and JSON data is really easy to work with in Javascript.
For triple coolness, I want to use an API based on REF Cursors in PL/SQL, client-side data manipulation based on JSON, and Apex to glue the two together.
What I need is the ability to generate JSON based on a REF Cursor.
Apex does include a few JSON-related procedures in the APEX_UTIL package, including JSON_FROM_SQL. Although this procedure does support bind variables, it cannot generate JSON from a REF Cursor. (Also, the fact that is is a procedure rather than a function makes it less flexible than it could be. Dear Apex Team, can we please have overloaded (function) versions of these JSON procedures?)REF CURSOR TO JSON: THE (10G) SOLUTION
So I came up with this solution: Use DBMS_XMLGEN to generate XML based on a REF Cursor, and then transform the XML into JSON by using an XSLT stylesheet.
Note: As mentioned above, in Oracle 11g you can use DBMS_SQL to describe a REF Cursor, so you could write your own function to generate JSON from a REF Cursor, without going through XML first. (And perhaps in Oracle 12g the powers that be at Redwood Shores will provide us with a built-in DBMS_JSON package that can both generate and parse JSON?)
In the meantime, for Oracle 10g, I created the JSON_UTIL_PKG package.
Here is the code for the REF_CURSOR_TO_JSON function:
function ref_cursor_to_json (p_ref_cursor in sys_refcursor, p_max_rows in number := null, p_skip_rows in number := null) return clob as l_ctx dbms_xmlgen.ctxhandle; l_num_rows pls_integer; l_xml xmltype; l_json xmltype; l_returnvalue clob; begin /* Purpose: generate JSON from REF Cursor Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 30.01.2010 Created */ l_ctx := dbms_xmlgen.newcontext (p_ref_cursor); dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag); -- for pagination if p_max_rows is not null then dbms_xmlgen.setmaxrows (l_ctx, p_max_rows); end if; if p_skip_rows is not null then dbms_xmlgen.setskiprows (l_ctx, p_skip_rows); end if; -- get the XML content l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none); l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx); dbms_xmlgen.closecontext (l_ctx); close p_ref_cursor; if l_num_rows > 0 then -- perform the XSL transformation l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet)); l_returnvalue := l_json.getclobval(); else l_returnvalue := g_json_null_object; end if; l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode); return l_returnvalue; end ref_cursor_to_json;
EXAMPLES OF USAGE
Get a small dataset
declare l_clob clob; l_cursor sys_refcursor; begin l_cursor := employee_pkg.get_employees (10); l_clob := json_util_pkg.ref_cursor_to_json (l_cursor); dbms_output.put_line (substr(l_clob, 1, 200)); end; {"ROWSET":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10}]}
A large dataset, with paging
declare l_clob clob; l_cursor sys_refcursor; begin l_cursor := test_pkg.get_all_objects; l_clob := json_util_pkg.ref_cursor_to_json (l_cursor, p_max_rows => 3, p_skip_rows => 5000); dbms_output.put_line (substr(l_clob, 1, 1000)); end; {"ROWSET":[{"OBJECT_ID":5660,"OBJECT_NAME":"LOGMNRT_SEED$","OBJECT_TYPE":"TABLE","LAST_DDL_TIME":"07.02.2006"},{"OBJECT_ID":5661,"OBJECT_NAME":"LOGMNRT_MDDL$","OBJECT_TYPE":"TABLE","LAST_DDL_TIME":"07.02.2006"},{"OBJECT_ID":5662,"OBJECT_NAME":"LOGMNRT_MDDL$_PK","OBJECT_TYPE":"INDEX","LAST_DDL_TIME":"07.02.2006"}]}
It works with nested datasets, too.. !
select d.deptno, d.dname, cursor (select e.* from emp e where e.deptno = d.deptno) as the_emps from dept d declare l_json clob; begin l_json := json_util_pkg.sql_to_json ('select d.deptno, d.dname, cursor (select e.* from emp e where e.deptno = d.deptno) as the_emps from dept d'); dbms_output.put_line (substr(l_json, 1, 10000)); end; {"ROWSET":[{"DEPTNO":10,"DNAME":"ACCOUNTING", "THE_EMPS":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10}, {"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10}, {"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10}]}, {"DEPTNO":20,"DNAME":"RESEARCH", "THE_EMPS":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"SALESMAN","MGR":7902,"HIREDATE":"17.12.1980","SAL":880,"COMM":null,"DEPTNO":20}, {"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02.04.1981","SAL":2975,"COMM":null,"DEPTNO":20}, {"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09.12.1982","SAL":3000,"COMM":null,"DEPTNO":20}, {"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12.01.1983","SAL":1100,"COMM":null,"DEPTNO":20}, {"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03.12.1981","SAL":3000,"COMM":null,"DEPTNO":20}, {"EMPNO":9999,"ENAME":"BRATEN","JOB":"CLERK","MGR":7902,"HIREDATE":"05.05.2009","SAL":1000,"COMM":null,"DEPTNO":20}, {"EMPNO":9998,"ENAME":"DOE","JOB":"CLERK","MGR":7902,"HIREDATE":"25.04.2009","SAL":500,"COMM":null,"DEPTNO":20}]}, {"DEPTNO":30,"DNAME":"SALES", "THE_EMPS":[{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20.02.1981","SAL":1600,"COMM":300,"DEPTNO":30}, {"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22.02.1981","SAL":3200,"COMM":500,"DEPTNO":30}, {"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28.09.1981","SAL":1250,"COMM":1400,"DEPTNO":30}, {"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01.05.1981","SAL":2850,"COMM":null,"DEPTNO":30}, {"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08.09.1981","SAL":1500,"COMM":0,"DEPTNO":30}, {"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7788,"HIREDATE":"03.12.1981","SAL":950,"COMM":null,"DEPTNO":30}]}, {"DEPTNO":40,"DNAME":"OPERATIONS", "THE_EMPS":null}]}
Passing a REF Cursor directly to the function call by using the CURSOR function:
select json_util_pkg.ref_cursor_to_json(cursor(select * from emp)) from dual {"ROWSET":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"SALESMAN","MGR":7902,"HIREDATE":"17.12.1980","SAL":880,"COMM":null,"DEPTNO":20},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20.02.1981","SAL":1600,"COMM":300,"DEPTNO":30},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22.02.1981","SAL":3200,"COMM":500,"DEPTNO":30},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02.04.1981","SAL":2975,"COMM":null,"DEPTNO":20},{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28.09.1981","SAL":1250,"COMM":1400,"DEPTNO":30},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01.05.1981","SAL":2850,"COMM":null,"DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09.12.1982","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08.09.1981","SAL":1500,"COMM":0,"DEPTNO":30},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12.01.1983","SAL":1100,"COMM":null,"DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7788,"HIREDATE":"03.12.1981","SAL":950,"COMM":null,"DEPTNO":30},{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03.12.1981","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7934,"ENAME":"MILLERø","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10},{"EMPNO":9999,"ENAME":"BRATEN","JOB":"CLERK","MGR":7902,"HIREDATE":"05.05.2009","SAL":1000,"COMM":null,"DEPTNO":20},{"EMPNO":9998,"ENAME":"DOE","JOB":"CLERK","MGR":7902,"HIREDATE":"25.04.2009","SAL":500,"COMM":null,"DEPTNO":20}]}
DOWNLOAD THE PACKAGE
You can download the complete package, including the XSLT stylsheet, here (spec) and here (body).
Update 12.02.2011: This package can now be downloaded as part of the Alexandria library for PL/SQL.
Note that to compile the packages you need the following SQL type defined in your schema:
create type t_str_array as table of varchar2(4000); /
http://ora-00001.blogspot.com/2010/02/ref-cursor-to-json.html
发表评论
-
用函数unistr将Oracle数据库中的Unicode转换为中文
2016-07-19 11:51 7873例子: DECLARE V_EXT_DES V ... -
ORACLE APPLICATION EXPRESS 5.0 升级
2016-05-12 11:43 538Oracle11GR2 XE 缺省是安装了oracle ap ... -
Oracle ACL(Access Control List)
2016-05-12 11:36 845在oralce 11g中假如你想获取server的ip或者h ... -
了解systemstate dump
2016-04-26 14:09 457当数据库出现严重的性能问题或者hang了的时候,我们非常需要 ... -
通过ORACLE的UTL_HTTP工具包发送包含POST参数的请求
2016-03-18 16:25 5079DECLARE req utl_http. ... -
Shell: extract more from listener.log(分析监听日志)
2016-03-16 14:57 1109统计一天内每小时的session请求数 # fgrep ... -
ORA-01031: insufficient privileges 问题解决笔记
2016-02-01 15:53 1150A) File $Oracle_HOME/network/a ... -
listener.log中报Warning: Subscription For Node Down Event Still Pending问题的解决方法
2016-01-07 16:34 1601一套Oracle 10.2.0.1 for aix的数据库环 ... -
Oracle触发器和MySQL触发器之间的区别
2015-11-19 12:55 615Oracle触发器格式: CREATE [OR RE ... -
查询正在执行的存储过程
2015-11-13 09:27 19891、找正在执行的PROCEDURE的 sid ,serial# ... -
undo表空间损坏的处理过程
2015-10-14 13:49 1185磁碟陣列故障,分區/rman上包括undo和archivel ... -
登录oracle资料库时很久无反应的问题处理一例
2015-10-11 10:56 962原因是系统存在僵死的进程,促使session处于激活状态.首 ... -
TNS-12560问题解决
2015-10-01 19:52 579tnsping远程主机实例出现TNS-12560: TNS ... -
查看undo中sql语句的占用情况
2015-08-06 17:18 1702查看undo中sql语句的占用情况 select * ... -
Install Open System Architect And ODBC Instant Client
2015-05-21 14:03 716How to Install Open System Arc ... -
恢复oracle中用pl sql误删除drop掉的表
2015-04-03 16:12 526查看回收站中表 select object_name,or ... -
在Oracle Linux 6.6上安装Oracle 10gR2
2015-01-15 15:36 2600查看硬體配置 # df -h Filesystem ... -
kill
2015-01-03 11:36 426--根据某一对象查询进程 col owner fo ... -
Oracle 数据库Storage存储迁移笔记
2014-12-27 11:08 9411.确认数据文件、控制文件、临时文件、日志文件 位置 / ... -
異地備份資料庫的開啟步驟
2014-11-19 14:03 448使用EMC設備執行異地備份, 資料庫的複製是開啟的狀態下, ...
相关推荐
本文档演示如何从 Java 存储过程将 JDBC ResultSet 作为 REF CURSOR 返回。JDBC ResultSet 是一个表示数据库的数据表,通常通过执行查询数据库的语句产生该表。REF CURSOR 是 PL/SQL 中相应的类型。Java 存储过程的...
在数据开发中,有时你需要合并两个动态游标sys_refcursor。 开发一个存储过程PROC_A,这个过程业务逻辑相当复杂,代码篇幅较长。一段时间后要开发一个PROC_B,要用PROC_A同样的逻辑,而且在这个过程中,还要循环调用...
解析 JSON Schema 并解析$ref字段。 安装 $ npm install json-schema-parser 用法 var json = require ( "./spec/fixtures/schema.json" ) ; var parser = require ( "json-schema-parser" ) ; var schema = ...
一个在JSON对象中转换$ref并将其替换为_ref的模块,以便可以将其存储在Mongo中,反之亦然。 我创建此模块是因为$ref是和的标准。 用法 import { replaceRefsJSON , replaceRefsMongo } from 'ref-replace' ; const...
5.Add the JSON Message formatters to the axis2.xml: <messageFormatter contentType="application/json" class="org.apache.axis2.json.JSONMessageFormatter"/> <messageFormatter contentType="application/...
JSON模式$ Ref解析器 解析,解析和取消引用JSON模式$ ref指针 问题: 您已经有了一个带有$ref指向其他文件和/或URL的JSON模式。 也许您提前知道所有引用的文件。 也许你不知道。 也许有些是本地文件,有些是远程URL...
按照$ref的JSON模式,OpenAPI的(原名扬鞭)值,和任何其他对象以$ref他们的内部价值。 查看更改日志: 特征 性能:记住热路径,并发解析远程URI,并爬网并解析最小表面积。 缓存:缓存来自远程URI的结果。 不可...
关键字/[nameRef].json 单个关键字 rarities.json 所有稀有 稀有/[nameRef].json 单身稀有 regions.json 所有地区 区域/[nameRef].json 单一区域 spellSpeeds.json 所有法术速度 spellSpeeds / [nameRef] ....
var ref = require('ref') var StructType = require('ref-struct') // define the time types var time_t = ref.types.long var suseconds_t = ref.types.long // define the "timeval" struct type var timeval ...
json从模式 json-from-schema基于架构生成随机JSON。 用法 var jfs = require ( 'json-from-schema' ) ;... , someEnum : { $ref : '#/definitions/blaEnum' } , someEnumArray : { type : 'array' ,
Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level. Focus on the ...
How To Use REF Cursors in JDBC Program
json_schema JSON模式V4和Hyperschema V4解析器和验证器。 根据JSON模式验证一些数据: gem install json_... ( schema_data )# expand $ref nodes - raise SchemaError if unable to resolveschema . exp
当项目中使用了fastjson框架转换json字符串后,默认情况下会有$ref这样的引用方式。 如果不使用此引用,在重复嵌套时,可能会耗尽系统资源。 但是如果启用的话,在页面js中又无法正常使用。 现只需要引入此js文件,...
包括使用$ ref或内联的关联 自动为枚举生成示例 通过描述,示例,验证等轻松自定义模式 专为草案06设计 例子 // User model is defined as // userDefinition = { // full_name: Sequelize.STRING, // status: { //...
PLSQL中显示Cursor、隐示Cursor、动态Ref_Cursor区别(有分支图解)
$ ref分辨率 Java文档 该项目是JSON Schema Draft v4 , Draft v6和Draft v7规范的实现。 它使用org.json API (由Douglas Crockford创建)代表JSON数据。 什么时候使用这个库? 让我们假设您已经知道什么是JSON ...
框架整合安装您可以通过composer安装该软件包: > composer require --dev sixlive/json-schema-assertions用法如果要使用外部架构$ref: 'bar.json ,例如$ref: 'bar.json ,则必须通过文件路径或使用配置路径解析来...
json-schema-compose 接受一个输入 JSON 模式,读取外部 JSON 模式(“ $ref ”)引用并生成一个新的自包含 JSON 模式,只有内部引用。 我构建它是为了将 JSON Schema 全部显示在单个文件中以用于文档目的。 例子: ...