Good news about Oracle XE 11g

Oracle OpenWorld 2010 came and went without any announcement of Oracle 11g Express Edition (XE).

However, according to Bradley D. Brown at TUSC, Oracle has now (re-) confirmed that they are still working on it.

For some time now the ACE Director community has been asking Mark Townsend (product manager for the Oracle DB) when the 11g version of Express was going to be available.  He was consistent in saying that they wouldn't even consider releasing it until 11gR2 was available.  Well...it's available now!  So the ACED community said "OK, Mark..." and he responded with - it's already in the works and should be out...when asked "when..." we got the standard answer.  The good news is that it's coming!  Another piece of good news is that they raised the DB size limit from 4GB to 10GB.  That makes for a nice free DB engine.

This is good news, since it has been a very long time since XE 10g was released. With Microsoft releasing updates to its free version of SQL Server at the same time as the full version, I think Oracle should do the same.


But hey, it's good, it's free, and it's coming eventually, so I'm not complaining! :-)

Proxy Authentication with Thoth Gateway

Christian Vind submitted an enhancement request for the Thoth Gateway to support Oracle proxy authentication by passing on the current Windows username to the database connection string.

The point of proxy authentication is that

• The proxy user only has "create session" privileges but can't do much else.
• The real user does not have "create session" privileges and cannot log on to the database without knowing the proxy user name and password (and that is only set on the web/application server).
• The USER function returns the real user name, and all standard database auditing, roles, etc. work as usual.

As of version 1.3 of the Thoth Gateway, proxy authentication is now supported. Here is how it works:


IIS Setup

Set up the application (virtual directory) in IIS where the gateway runs with Integrated Windows Authentication, so that the CGI environment variable LOGON_USER will be populated with the client's Windows username. (If the user is using Internet Explorer to browse the site, his identity will be passed on to the web server/gateway automatically; if using another browser, then an explicit logon is required.)

Oracle Setup

Define an "application server user", ie the common user that connections will be established through:


-- Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.


create user appserver identified by eagle;



create user end_user identified by secret;
grant create session to end_user;
alter user end_user grant connect through appserver;




Now test the setup with SQL*Plus, by connecting with the "application server user", and then "becoming" the end user:



-- note we don't specify the end_user password, but still become that user


SQL> connect appserver[end_user]/eagle
Connected.
SQL> select user from dual;


USER
------------------------------
END_USER


SQL>

Note that since the point of this is to take advantage of existing Active Directory accounts, you probably want to create your users like this:

create user "your_domain\end_user" identified externally;

grant create session to "your_domain\end_user";

alter user "your_domain\end_user" grant connect through appserver;

Thoth Gateway Setup

In web.config, modify the DAD settings (the following example assumes a local Oracle XE installation):


param name="DatabaseConnectString" value="//127.0.0.1:1521/xe"
param name="DatabaseConnectStringAttributes" value="Enlist=false;Proxy User Id=appserver;Proxy Password=eagle;"
param name="DatabaseUserName" value="LOGON_USER"
param name="DatabasePassword" value=""


Notice the value "LOGON_USER" specified for the DatabaseUserName parameter. This is a reserved string that will be replaced with the actual value of the LOGON_USER value from the web request (ie. the user's Windows username, typically "domain\username"). You can also specify "LOGON_USER_NO_DOMAIN" to strip away the domain part of the user name -- what you use will depend on how you have set up your user accounts in Oracle.

Testing It

To test that everything works at this point, create a procedure similar to the following, and execute it via the gateway (don't forget to grant execute privileges on it to the end-user's account, and create a public synonym for it unless you prefix with the procedure owner's name in the URL).


procedure test_proxy_auth
as
begin


  htp.header(1, 'Proxy authentication');


  htp.ulistopen;
  htp.listitem ('USER = ' || user);
  htp.listitem ('Proxy user = ' || sys_context('userenv', 'proxy_user'));
  htp.listitem ('CGI LOGON_USER = ' || owa_util.get_cgi_env('LOGON_USER'));
  htp.ulistclose;


end test_proxy_auth;

If successful, the USER function should return the end-user's Windows username, and the Proxy User should display as "appserver".

Postscript: A little enigma

Actually, if you do as described above, you could possibly get this error when you try to run the procedure via the gateway:

ORA-1045: user %s lacks CREATE SESSION privilege; logon denied

At least, that's what I got . To get around it, I had to explicitly grant this to the "appserver" user:

grant create session to appserver;

The funny thing is that my example above, tested via SQL*Plus, shows that this works without the grant! But when attempting the same connection via ODP.NET, it gives the above error unless the grant is made.

And if I revoke the "create session" from the end_user, the above example doesn't work in SQL*Plus, because of the missing privilege. Which seems to contradict the purpose of proxying, as defined at the top of this blog post.

If anybody knows why SQL*Plus and ODP.NET show different behaviour here, please let me know.

Thoth Gateway version 1.3 available

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.

It contains the following bug fixes and enhancements:

• Bug Fix: Issue with parsing client IP address: Added exception handling to prevent error when parsing client IP address with invalid format.
• Ignore additional request parameters: Certain tools and frameworks may dynamically add additional parameters to a request, which causes the corresponding PL/SQL call to fail, since these parameters are not defined in the procedure signature. As of this version, the gateway will now retry the call after dropping (ignoring) any parameters that cannot be found in the Oracle data dictionary for the procedure being called.
• Support for Oracle proxy authentication (and Single Sign On) via dynamic username substitution: Oracle proxy authentication, combined with Integrated Windows Authentication in IIS, allows you to pass the end-user's identity from the client to the database session (so the function USER will return the end-user's Windows username, with no login required). This is useful in an intranet scenario where users are defined in an Active Directory domain and use Internet Explorer to access the PL/SQL web application.

For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Apex on Thoth Gateway and IIS 7

Several people have asked for instructions on how to run the Thoth Gateway (a mod_plsql replacement) on Microsoft Internet Information Server (IIS) 7.

I finally had the chance (and the time!) to test the configuration on a server running IIS 7. The biggest challenge was understanding the new administration console user interface for IIS 7; it was was slightly confusing for someone who is used to IIS 6.

After a bit of fiddling, I got the gateway up and running:

I have updated the installation instructions in the latest download package (version 1.2.1) with separate sections for IIS 6 and IIS 7. So download, unzip and read the instructions in the "doc" folder.

And leave a comment if you run into problems (or leave a comment if you managed to get it up and running using the instructions!).

Replacing Apex? More like Find and Replace...

I was getting my daily dose of Apex blog posts when I noticed this advertisement:

I was curious, so I clicked the link and got the following page (http://www.wavemaker.com/solutions/oracleforms.html):

"Well, this is strange", I thought... it says "Oracle Forms" there in the URL, and in the illustration in the middle of the page, yet the advertisement and the page heading talks about replacing "APEX". There is also a claim that the latter "costs a fortune", but as we all know Application Express is a no-cost option in the Oracle database. WTF?

There is also a link to an 8-page whitepaper on "Migrating Oracle Apex Applications to Java" (http://www.wavemaker.com/pdf/Migrating-Oracle-Apex-Apps-To-Java-With-WaveMaker.pdf). This whitepaper includes the following screenshot:

As well as this table:

OK, so it is clearly Oracle Forms that is depicted and described here, but labeled as if it was Oracle Apex.

At best, this is a clueless mistake made by some marketing sod who did a global Find & Replace from FORMS to APEX. At worst, this is deliberately misleading.

In any case, it's just wrong, wrong, wrong. I don't know about you, but I would not trust a company that is either incompetent, dishonest, or both. I'll stick with Apex, thank you.

Small patch for the Thoth Gateway (Apex on IIS)

Just a quick note to announce that a minor patch release (version 1.2.1) of the Thoth Gateway is available for download. The Thoth Gateway allows you to run Oracle Apex applications on the Microsoft IIS web server (a replacement for Apache and mod_plsql).

The following has changed:

• Bug Fix for Content-Length: Fixed an issue where the content-length header would be incorrectly set for non-AL32UTF8 databases if the page contained multibyte characters.
• Set ODP.NET connection string attributes: Added option to specify additional connection string attributes in the DAD configuration. This allows you to fine-tune the connection properties. See the ODP.NET documentation for more details.

Upgrade for existing installations: Simply download the latest version, unzip the archive and drop the PLSQLGatewayModule.dll file into your bin folder on the web server.

ApexGen has a new home

My first open-source project was ApexGen, a utility to generate Oracle Application Express (Apex) pages from PL/SQL, in a fraction of the time it takes to create Apex pages manually (using point-and-click). I originally hosted it on SourceForge. However, since then I have started a few other projects hosted on Google Code, so I decided to move ApexGen to Google Code as well.

To summarize, here are my current Oracle, Apex and PL/SQL projects, all on Google Code:

• Thoth Gateway, a mod_plsql replacement that runs on Microsoft Internet Information Server (IIS). It allows you to use IIS as the web server for Apex applications (instead of Apache or the Embedded PL/SQL Gateway), and it has a few extra features as well, such as CLOB support, automatic Web Services published from PL/SQL, XDB integration, and integrated Windows authentication out-of-the-box.
• JQGrid Integration Kit for PL/SQL, a set of PL/SQL packages that allows you to use the JQGrid component to display and edit tabular data in your Apex applications. It is faster, better-looking and more flexible than the built-in tabular forms in Apex.
• ApexGen, a utility to generate Oracle Application Express (Apex) pages from PL/SQL, in a fraction of the time it takes to create Apex pages manually. With Apex 4.0 just around the corner, I believe ApexGen will be due for an overhaul soon, as the export files are likely to have changed quite a bit.

SELECT * FROM spreadsheet (or How to parse a CSV file using PL/SQL)

I recently needed to retrieve/download a comma-separated values (CSV) file from a website, and insert the data in an Oracle database table.

After googling around a bit, I found various pieces of the solution on AskTom, ExpertsExchange and other sites, which I put together in the following generic utility package for CSV files.

Usage

Because I have implemented the main parsing routine as a pipelined function, you can process the data either using straight SQL, or in a PL/SQL program.

For example, you can retrieve a download a CSV file as a clob directly from the web and return it as a table with a single statement:

?

1 2	select * from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))

And maybe do a direct insert via INSERT .. SELECT :

?

1 2 3

insert into my_table (first_column, second_column) select c001, c002 from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))

You can of course also use SQL to filter the results (although this may affect performance):

?

1 2 3

select * from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob())) where c002 = 'Chevy'

Or you can do it in a more procedural fashion, like this:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

create table x_dump (clob_value clob,  dump_date date default sysdate,  dump_id number);     declare   l_clob clob;     cursor l_cursor   is   select csv.*   from x_dump d, table(csv_util_pkg.clob_to_csv(d.clob_value)) csv   where d.dump_id = 1;   begin     l_clob := httpuritype('http://www.foo.example/bar.csv').getclob();   insert into x_dump (clob_value, dump_id) values (l_clob, 1);   commit;   dbms_lob.freetemporary (l_clob);     for l_rec in l_cursor loop     dbms_output.put_line ('row ' || l_rec.line_number || ', col 1 = ' || l_rec.c001);   end loop;   end;

Auxiliary functions

There are a few additional functions in the package that are not necessary for normal usage, but may be useful if you are doing any sort of lower-level CSV parsing. The csv_to_array function operates on a single CSV-encoded line (so to use this you would have to split the CSV lines yourself first, and feed them one by one to this function):

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

declare   l_array t_str_array;   l_val varchar2(4000); begin     l_array := csv_util_pkg.csv_to_array ('10,SMITH,CLERK,"1200,50"');     for i in l_array.first .. l_array.last loop     dbms_output.put_line('value ' || i || ' = ' || l_array(i));   end loop;     -- should output SMITH   l_val := csv_util_pkg.get_array_value(l_array, 2);   dbms_output.put_line('value = ' || l_val);     -- should give an error message stating that there is no column called DEPTNO because the array does not contain seven elements   -- leave the column name out to fail silently and return NULL instead of raising exception   l_val := csv_util_pkg.get_array_value(l_array, 7, 'DEPTNO');   dbms_output.put_line('value = ' || l_val);   end;

Installation

In order to compile the package, you will need these SQL types in your schema:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

create type t_str_array as table of varchar2(4000); /   create type t_csv_line as object (   line_number  number,   line_raw     varchar2(4000),   c001         varchar2(4000),   c002         varchar2(4000),   c003         varchar2(4000),   c004         varchar2(4000),   c005         varchar2(4000),   c006         varchar2(4000),   c007         varchar2(4000),   c008         varchar2(4000),   c009         varchar2(4000),   c010         varchar2(4000),   c011         varchar2(4000),   c012         varchar2(4000),   c013         varchar2(4000),   c014         varchar2(4000),   c015         varchar2(4000),   c016         varchar2(4000),   c017         varchar2(4000),   c018         varchar2(4000),   c019         varchar2(4000),   c020         varchar2(4000) ); /   create type t_csv_tab as table of t_csv_line; /

UPDATE 04.04.2012: The latest version of the package itself (CSV_UTIL_PKG) can be found as part of the Alexandria Utility Library for PL/SQL.

Performance

On my test server (not my laptop), it takes about 35 seconds to process 12,000 rows in CSV format. I don't consider this super-fast, but probably fast enough for many CSV processing scenarios.

If you have any performance-enhancing tips, do let me know!

Bonus: Exporting CSV data

You can also use this package to export CSV data, for example by using a query like this.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

select csv_util_pkg.array_to_csv (t_str_array(company_id, company_name, company_type)) as the_csv_data from company order by company_name   THE_CSV_DATA -------------------------------- 260,Acorn Oil & Gas,EXT 261,Altinex,EXT 262,Amerada Hess,EXT 263,Atlantic Petroleum,EXT 264,Beryl,EXT 265,BG,EXT 266,Bow Valley Energy,EXT 267,BP,EXT

This might come in handy, even in these days of XML and JSON ... :-)

Using TRUNC and ROUND on dates

Maybe this is old news to some, but I recently became aware that it is possible to use TRUNC and ROUND not just on a NUMBER, but also on a DATE value.

For example, you can get the start of the month for a given date (using TRUNC), or the "closest" start of the month, rounded forward or backwards in time appropriate (using ROUND):

?

1 2 3 4 5 6

select sysdate,   trunc(sysdate, 'YYYY') as trunc_year,   trunc(sysdate, 'MM') as trunc_month,   round(sysdate, 'MM') as round_month,   round(sysdate + 15, 'MM') as round_month2 from dual

The above gives the following results:

?

1 2 3

SYSDATE                   TRUNC_YEAR                TRUNC_MONTH               ROUND_MONTH               ROUND_MONTH2              ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- 06.04.2010 20:10:56       01.01.2010 00:00:00       01.04.2010 00:00:00       01.04.2010 00:00:00       01.05.2010 00:00:00

Somewhat related to this topic is the relatively obscure (?) EXTRACT function, which allows you to extract a part of a DATE:

?

1 2 3 4 5

select sysdate,   extract(day from sysdate) as extract_day,   extract(month from sysdate) as extract_month,   extract(year from sysdate) as extract_year from dual

Which gives the following results:

?

1 2 3

SYSDATE                   EXTRACT_DAY            EXTRACT_MONTH          EXTRACT_YEAR           ------------------------- ---------------------- ---------------------- ---------------------- 06.04.2010 20:13:01       6                      4                      2010

If you try to extract the "hour", "minute" or "second" from a DATE, however, you get an ORA-30076: invalid extract field for extract source.

For some reason, these only work on TIMESTAMP values, not on the DATE datatype (which seems like an arbitrary limitation to me). Nevertheless:

?

1 2 3 4 5

select systimestamp,   extract(hour from systimestamp) as extract_hour,   extract(minute from systimestamp) as extract_minute,   extract(second from systimestamp) as extract_second from dual

The above gives the following results:

?

1 2 3

SYSTIMESTAMP  EXTRACT_HOUR           EXTRACT_MINUTE         EXTRACT_SECOND         ------------- ---------------------- ---------------------- ---------------------- 06.04.2010 20.17.12,047000000 +02:00 18                     17                     12,047

jQGrid Integration Kit for PL/SQL and Apex

I started developing applications back in the good (?) old client/server days. I was fortunate enough to discover Delphi quite early. Even from the start, the lowly 16-bit Delphi version 1 had a kick-ass DBGrid control which allowed you to quickly and easily build data-centric applications. Just write a SQL statement in a TDataSet component, connect it to the grid, and voila! Instant multi-row display and editing out of the box, without any coding.

Fast forward a decade. While I do enjoy building web applications (with PL/SQL and Apex) these days, I've always missed the simplicity of that DBGrid in Delphi. Creating updateable grids with Apex is pretty tedious work (not being entirely satisfied with the built-in updateable tabular forms, I've employed a combination of the apex_item API, page processes for updates and deletes, and custom-made Javascript helpers). It doesn't help that you have to refer to the tabular form arrays by number, rather than by name (g_f01, g_f02, etc.), and that you are restricted to a total of 50 columns per page.

Enter jQGrid, "an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web".

jQGrid can be integrated with any server-side technology, so I decided to integrate it with PL/SQL and Apex.

Features

As of version 1.0, the jQGrid for PL/SQL and Apex has the following features:

• Single line of PL/SQL code to render grid


• Populate data based on REF CURSOR or SQL text (with or without bind variables). The REF CURSOR support is based on my REF Cursor to JSON utility package.


• Define display modes (read only, sortable, editable) and edit types (checkbox, textarea, select list) per column


• Store grid configuration in database, or specify settings via code (for read-only grids)


• Ajax updates (insert, update, delete) based on either automatic row processing (dynamic SQL) or against your own package API


• Multiple grids per page


• Integrated logging and instrumentation


• Usable without Apex (for stand-alone PL/SQL Web Toolkit applications) or with Apex, optionally integrated with Apex session security

The jQGrid Integration Kit for PL/SQL is free and open source. Download and try it now!.

REF Cursor to JSON

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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...

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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:

?

1 2	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:

?

1 2 3 4 5 6 7 8

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

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

?

1 2 3 4 5 6 7 8 9 10

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

?

1 2 3 4 5 6 7 8 9 10 11

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.. !

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

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:

?

1 2 3 4

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:

?

1 2	create type t_str_array as table of varchar2(4000); /

My first Apex 4 plugin: Flight Info from Web Service

One of the exciting new features in Apex 4 is the support for plugin regions and items. This feature has huge potential, and will make development with Apex even more efficient, productive, and fun. There are already several plugins out there, and I think we will see a lot of interesting work in this area after Apex 4 is released.

Here is my own first attempt at a (useful) plugin: A region plugin that displays up-to-date flight information for airports in Norway, based on public flight data provided by Avinor, the company that operates the Norwegian airport network.

Avinor has a simple web service that provides flight information in XML format.

I am sure there are similar (web) services for flight information in other countries (feel free to leave a comment below if you know of any).

Here is the PL/SQL code behind the plugin:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

procedure render_my_plugin ( p_region              in apex_plugin.t_region, p_plugin              in apex_plugin.t_plugin, p_is_printer_friendly in boolean ) as l_clob clob; l_airport_code varchar2(20) := p_region.attribute_01; l_direction    varchar2(20) := p_region.attribute_02; begin   l_clob := apex_web_service.make_rest_request( p_url => 'http://flydata.avinor.no/XmlFeed.asp', p_http_method => 'GET', p_parm_name => apex_util.string_to_table('airport:direction'), p_parm_value => apex_util.string_to_table(l_airport_code || ':' || l_direction ) );   if l_direction = 'D' then htp.p('<p><b>Departures from ' || l_airport_code || '</b></p>'); else htp.p('<p><b>Arrivals to ' || l_airport_code || '</b></p>'); end if;   htp.p('<table width="100%">'); htp.p('<tr><td>AIRLINE</td><td>FLIGHT</td><td>AIRPORT</td><td>TIME</td><td>GATE</td></tr>');   for l_rec in ( SELECT * FROM XMLTABLE ('//airport/flights/flight' PASSING XMLTYPE(l_clob) COLUMNS unique_id       varchar2(100) path '@uniqueID', airline         varchar2(10) path 'airline', flight_id       varchar2(20) path 'flight_id', airport         varchar2(20) path 'airport', schedule_time   varchar2(100) path 'schedule_time', gate            varchar2(100) path 'gate') ORDER BY airline, flight_id) loop   htp.p('<tr><td>' || l_rec.airline || '</td><td>' || l_rec.flight_id || '</td><td>' || l_rec.airport || '</td><td>' || l_rec.schedule_time || '</td><td>' || l_rec.gate || '</td></tr>');   end loop;   htp.p('</table>');   htp.p('<a href="http://www.avinor.no">Flight data from Avinor.</a> Last updated: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));     end render_my_plugin;

The code illustrates several concepts:

• How to render a region plugin using the PL/SQL Web Toolkit (HTP.P) calls
• How to retrieve values from the attributes defined for the plugin
• Using the new APEX_WEB_SERVICE.MAKE_REST_REQUEST function to retrieve a web page as a CLOB
• Using the XMLTABLE function to transform XML into a recordset that can be used in a SELECT

An export of my plugin can be downloaded here, and installed into your own Apex 4 application.

After the plugin has been installed, using the plugin is as simple as adding a Region (of type Plugin) to the page, and configuring the values for Airport and Direction (the plugin attributes) in the region definition.



You can see a live demo of the plugin here (public page, does not require authentication):

http://tryapexnow.com/apex/f?p=test4ea:plugin_demo:0

Note that for this page, I've also taken advantage of the built-in region caching feature of Apex. The region cache duration is set to 10 minutes, which prevents us from hitting the remote web service for every page view. I really like that you can switch on region caching in Apex without writing a single line of code.

Conclusion: Apex 4 plugins rock!

ODP.NET minimal, non-intrusive install

This might be of interest for those who use .NET to connect to Oracle databases. (Including yours truly, who wrote the Thoth Gateway, a mod_plsql replacement that runs on Microsoft IIS, using C# and ODP.NET.)

A while back, Microsoft officially deprecated their ADO.NET driver for Oracle (System.Data.OracleClient).

Fortunately, Oracle offers its own .NET driver, known as the Oracle Data Provider for .NET (ODP.NET). This driver is a better choice for Oracle connectivity, since it supports a wider range of Oracle-specific features, and improved performance.

However, ODP.NET, unlike, say, the thin JDBC drivers, still requires the normal Oracle client to be present on the machine. This Oracle client can be something of a beast, with the install package upwards of 200 megabytes. Couple this with the fact that you may have several diffent Oracle client versions installed on your machine (or application server), all specific to some application that you dare not touch for fear of it breaking.

A non-intrusive install

So, here is how you can use ODP.NET with the following advantages:

• Small footprint (between 30 and 100 megabytes)
• XCopy deployment
• No dependency on shared files, all files in your own application's folder
• No registry or system environment changes required
• No tnsnames.ora file required
• No interference from other Oracle client installs on the same machine

Sounds good, doesn't it? Let's see how this can be accomplished...

1. Download ODP.NET (xcopy version)

Download from here:

http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html

Unzip the file and locate the following 2 files:

• OraOps11w.dll
• Oracle.DataAccess.dll

Copy these files to your application's "bin" folder.

2. Download Oracle Instant Client

Download from here:

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

You have a choice between the following two versions of the Instant Client

a) Instant Client Basic (approx. 100 megabytes)

Unzip the file and locate the following 3 files:

• oci.dll
• orannzsbb11.dll
• oraociei11.dll

b) Instant Client Basic Lite (approx. 30 megabytes): This version is smaller but only supports certain character sets (WE8MSWIN1252 and AL32UTF8 are among them). It only has English messages, so in case you wonder what "ORA-06556: The pipe is empty" sounds like in your own language, go for the non-Lite version.

Unzip the file and locate the following 3 files:

• oci.dll
• orannzsbb11.dll
• oraociicus11.dll

Whichever version you choose, copy these files to your application's "bin" folder. You now have a total of 5 new files in your "bin" folder.

3. Connection string

In your .NET program, use a connect string in the following format, to make sure you don't need to rely on any network configuration files (tnsnames.ora, etc.).

?

1	(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=database_host_name)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=database_service_name)))

4. Configuration

This is mostly relevant if you have other Oracle client installations already on the same machine/server.

In your configuration file (web.config), you can explicitly set the path to the Oracle DLLs you want to use. Set the "DllPath" parameter to the name of your "bin" folder.

?

1 2 3 4 5 6 7 8 9 10 11 12 13

<configuration> <oracle.dataaccess.client> <settings> <add name="DllPath" value="c:\my_app_folder\bin"></add> <add name="FetchSize" value="65536"></add> <add name="PromotableTransaction" value="promotable"></add> <add name="StatementCacheSize" value="10"></add> <add name="TraceFileName" value="c:\temp\odpnet2.log"></add> <add name="TraceLevel" value="0"></add> <add name="TraceOption" value="0"></add> </settings> </oracle.dataaccess.client> </configuration>

5. That's it!

You should now be able to run your ODP.NET application from your "bin" folder.



References

• Installing Oracle Data Provider for .NET
• ODP.NET Configuration
• http://alderprogs.blogspot.com/2009/04/deploying-odpnet-with-oracle-instant.html