On Creating, Dating, and Binding

Our technologist creates XML, translates dates, and binds variables.

May/June 2002

I want to create a file (a spool from a table) in an XML layout. What is the best way to do this? Does Oracle provide a utility to create XML? I asked Sean Dillon, our local XML guru, to answer this. Here's what he said:

Download and install the latest Oracle XML Developer's Kit (XDK). What you are trying to do is relatively simple—and with the XDK, you can generate XML from SQL queries, using PL/SQL, Java, C, or C++. With the Java XDK installed, the following generates an XML document containing the rows of the SCOTT.EMP table:

$ java OracleXML getXML -user "scott/tiger" "select * from emp" > emp.xml
$ cat emp.xml
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
...
<ROW num="14">
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>4/2/1981 0:0:0</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>

There are options available with this OracleXML Java class as well; you can discover all of the options by entering:

$ java OracleXML

This returns a summary of all the commands and what they do. This Java solution is external from the database, however, and it might not fit your needs at all. Alternatively, you could use the equivalent PL/SQL functionality to generate XML from a SQL query, by using the package DBMS_XMLGEN , as shown in Listing 1.

Code Listing 1: Using DBMS_XMLGEN to Generate XML

SQL> var my_xml clob
SQL> set autoprint on
SQL> declare
  2    l_ctx  dbms_xmlgen.ctxHandle;

  3    l_sql  varchar2(400);
  4  begin
  5    l_sql := 'select * from emp';
  6    l_ctx := dbms_xmlgen.newContext(l_sql);
  7    :my_xml := dbms_xmlgen.getXml(l_ctx);
  8  end;

  9  /

PL/SQL procedure successfully completed.

MY_XML
———————————————————————
<?xml version="1.0"?>

<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
...
</ROW>
</ROWSET>


SQL> set autoprint off

The DBMS_XMLGEN package has a getXml() function that takes a SQL query and returns a CLOB without having to create a context and go into the overhead. But I recommend using the context handle in the PL/SQL block. It gives you an enormous amount of functionality (most of which is absolutely necessary in a production application), such as binding variables to your query, generating a DTD or schema for your XML, modifying the names of the ROWSET and ROW elements, and limiting the number of rows returned.

You can use DBMS_XMLGEN to generate XML directly from PL/SQL, but dbms_xmlgen.newContext() accepts simple static SQL as a parameter. From prior "Ask Tom" articles (and asktom.oracle.com questions), you all know that static SQL without bind variables creates slow, nonscalable applications, so you have to use bind variables. So, is there a way to generate XML from a SELECT statement that passes an argument cursor? As it turns out, there are a couple of ways to handle this. Luckily, in DBMS_XMLQUERY you have the SETBINDVALUE procedure, so you could code it as shown in Listing 2. Listing 2 shows how to set the query with bind variables (:DEPTNO is a bind variable). After I set the query, I can call dbms_xmlquery.setbindvalue to specify a value for that bind variable. This makes the use of shared SQL very nice, effectively reusing that parsed query plan regardless of the values of :DEPTNO I need to supply.

Code Listing 2: Using DBMS_XMLQUERY with SETBINDVALUE

SQL> create or replace function get_emps_in_dept( p_deptno in number )
  2    return clob
  3  is
  4    l_ctx dbms_xmlquery.ctxType;

  5    l_sql varchar2(100);
  6    l_xml clob;
  7  begin
  8    l_sql := 'select * from emp where deptno = :deptno';
  9    dbms_lob.createtemporary( l_xml, true, dbms_lob.session );
 10    l_ctx := dbms_xmlquery.newContext( l_sql );

 11
 12    dbms_xmlquery.setbindvalue( l_ctx, 'deptno', p_deptno );
 13
 14    dbms_xmlquery.setRaiseNoRowsException( l_ctx,false );
 15
 16    — get the xml fm the context
 17    l_xml := dbms_xmlquery.getXML( l_ctx );

 18
 19    dbms_xmlquery.closeContext(l_ctx);
 20    return l_xml;
 21  exception
 22    when others then
 23      dbms_xmlquery.closeContext(l_ctx);
 24      raise;
 25  end get_emps_in_dept;

 26  /
Function created.

In the case where your interface doesn't have the ability to set bind variables, you can use cursor sharing, available with Oracle8i Release 2 (8.1.6) and up, to do this:

execute immediate 'alter session set
cursor_sharing = force';

Next, execute the API (that does not use bind variables). Then issue the following statement:

execute immediate 'alter session set
cursor_sharing = exact';

Date Functions and Internationalization

How can I use the same value for [name_of_day] as [next_day]? I want a unique [name_of_day] for the next_day function, independent of the NLS_LANGUAGE parameter. My function must work in different databases with different languages, but since next_day expects the name of a day, what can I do? Here is a trick I've used. I select a default character string to be the name of Monday in the current language. I pick some day I know is Monday and let the Oracle database teach me how to spell that day in the current language, as shown in Listing 3.

Code Listing 3: Spelling Monday

SQL> alter session set nls_language = english;

SQL> declare
  2      l_monday varchar2(255) default to_char( to_date( '20011231', 'yyyymmdd' ), 'day' );
  3      l_until  date;

  4  begin
  5      l_until := next_day( sysdate, l_monday );
  6      dbms_output.put_line( l_until  || ' ' || l_monday );
  7  end;
  8  /
14-JAN-02 monday


PL/SQL procedure successfully completed.

SQL> alter session set nls_language = french;

Session altered.


SQL> declare
  2      l_monday varchar2(255) default to_char( to_date( '20011231', 'yyyymmdd' ), 'day' );
  3      l_until  date;
  4  begin
  5      l_until := next_day( sysdate, l_monday );
  6      dbms_output.put_line( l_until  || ' ' || l_monday );
  7  end;
  8  /

14-JAN-02 lundi

PL/SQL procedure successfully completed.

I picked a date I know is a Monday: 20011231 in the example in Listing 3 is Monday, December 31, 2001 in yyyymmdd format. And I ask Oracle to "spell Monday" for me in whatever language is currently in effect.

I have multiple users on an Oracle7 Release 7.3 instance. How do I give all users the privilege to execute the following statement:

alter tablespace tablespace_name coalesce

I would like to give only the coalesce option; I don't want to grant any other privileges. This is exactly what stored procedures are great for. They run with the privileges of the definer by default, so you can do things like this easily and securely.

In Listing 4, I create a procedure that grants the ALTER TABLESPACE privilege with only the coalesce option. Then I just grant the EXECUTE privilege on that procedure to whomever I want to run it.

Code listing 4: Granting Privileges in a Procedure

create or replace procedure coalesce_tablespace( p_tablespace in varchar2 )
is
    cursor_name     pls_integer default dbms_sql.open_cursor;
    ignore          pls_integer;
BEGIN

    dbms_sql.parse(cursor_name,
                   'alter tablespace ' || p_tablespace || ' coalesce',
                    dbms_sql.native);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
END;

Note that in Oracle8i and later releases, I don't need to use the DBMS_SQL package, so I can replace the code in Listing 4 with this:

execute immediate 'alter tablespace ' ||
p_tablespace || 'coalesce';

Also note that in Oracle8i and later releases, if you use locally managed tablespaces you won't need to coalesce.

If after coding this you get an "ORA-01031: insufficient privileges" error, remember that roles are never enabled during the compilation and execution of a procedure.

The only exception is the special case of invoker rights, which was a new feature in Oracle8i Release 1. But you wouldn't use invoker rights here, since you don't want to grant the privilege in the first place!

This fact is documented in the application developer's guide as follows:

Privileges to Create Procedures and Functions

You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.

To create without errors (that is, to compile the procedure or package successfully), requires the following additional privileges:

• The owner cannot obtain required privileges through roles.

  If the privileges of a procedure's or package's owner change, the procedure must be reauthenticated before it is run. If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be run.

• The owner of the procedure or package must have been explicitly granted all the necessary object privileges for all objects that are referenced within the body of the code.

When using dynamic SQL, especially for Data Definition Language (DDL) operations like the above, I recommend you always test statements as follows:

SQL> set role none; SQL> "statement you want to test?

If you can do this in SQL*Plus with no roles, you can do it in a procedure. If you can't, you must have the privilege from a role, and, you won't be able to do it in a procedure— unless you're using invoker rights in Oracle8i.

See the PL/SQL documentation for more information on this feature, and make sure you understand the ramifications. To be able to perform that operation in a typical procedure, you need to have that privilege granted directly to you.

As I understand it, bind variables are critical in dynamic SQL. Please explain accidentally hard-coding variables when using dynamic SQL and why this issue does not arise when we use static SQL.

Given the number of times it comes up, I sometimes think I could write a book simply titled Bind Variables. Maybe it comes up so much because I've been on a crusade to get people to use them.

The reason that you do not always have to use bind variables in static SQL is that static SQL is, by definition, fixed at compile time. Every time you run such a program, the static SQL will be the same, and it will be 100-percent shareable. So, using static SQL, a query such as:

select * from t where x = 'some value'

will always be

select * from t where x = 'some value'

No matter how many times you run that program, that same old query will be executed (reused).

Using dynamic SQL, you are able to construct statements like the following:

select * from t where x = 'a' select * from t where x = 'b' select * from t where x = 'c'

and so on. Each statement is brand new, unique, never before seen.

In the following example, I dynamically execute:

select count(*) from t where x = 1

(varying the 1 from 1 to 1,000) without using bind variables. And then, using bind variables with static PL/SQL, I execute the same.

I measure some important statistics and latches while running this, as well as using dbms_utility.get_time to time it. To build my example, I start by creating a table and view for our statistics collection, and I create a table T, as shown in Listing 5. Now I run two test cases: one without bind variables and one with binds, as shown in Listing 6.

Code Listing 5: Creating the Statistics Tables

SQL> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.

SQL> create or replace view stats
  2  as select 'STAT...' || a.name name, b.value

  3        from v$statname a, v$mystat b
  4       where a.statistic# = b.statistic#
  5      union all
  6      select 'LATCH.' || name,  gets
  7        from v$latch;
View created.


SQL> create table t ( x int );
Table created.

Code Listing 6: Testing without and with Bind Variables

SQL> declare
  2      l_start number;
  3          l_cnt   number;
  4  begin

  5      insert into run_stats select 'before', stats.* from stats;
  6
  7      l_start := dbms_utility.get_time;
  8      for i in 1 .. 1000
  9      loop
 10         execute immediate 'select count(*) from t where x = ' || i into l_cnt;

 11      end loop;
 12      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 13
 14      insert into run_stats select 'after 1', stats.* from stats;
 15
 16      l_start := dbms_utility.get_time;
 17      for i in 1 .. 1000

 18      loop
 19          select count(*) into l_cnt from t where x = i;
 20      end loop;
 21      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 22
 23      insert into run_stats select 'after 2', stats.* from stats;
 24  end;
 25  /

108 hsecs
18 hsecs

PL/SQL procedure successfully completed.

Here the use of bind variables is faster (18/100ths of a second versus 108/100ths of a second), but that's only half of the story. Since I captured the statistics and latches (and I ran this on a single-user system; no other sessions affected these numbers), you can see even more differences between the two tests, as shown in Listing 7.

Code Listing 7: Statistics and Latches for No Binds Versus Binds Test

SQL> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name

  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))

 12  /

NAME                               RUN1    RUN2    DIFF
————————————————————————————      —————   —————   —————
...
LATCH.cache buffers chains         8621    8222    -399
STAT...recursive calls             2002    1184    -818

STAT...parse count (hard)          1007    5       -1002
STAT...opened cursors cumulative   1034    15     -1019
STAT...parse count (total)         1048    15     -1033
STAT...session pga memory max      6656    8524    1868
LATCH.row cache objects            6254    115    -6139
STAT...session pga memory          15144   8524   -6620
LATCH.library cache                21344   2269   -19075
LATCH.shared pool                  24098   131    -23967


51 rows selected.

Take a look at the combined latches on the shared pool and library cache in Listing 7. There were 45,442 latches when not using binds (RUN1) and 2,400 latches using binds (RUN2). That's almost 20 times the number of latches.

Bearing in mind that latches are locks, and locks = serialization device, and serialization = less concurrent, and less concurrent = not scalable, you should be able to see which approach is clearly preferable.

Given the excessively high amount of latching that not using bind variables creates, scaling up without using the bind variables would be hard. As you add more and more users without using bind variables, the runtime differences compared with using bind variables will skyrocket.

Next Steps

• Beginning Oracle Programming (by Sean Dillon, Christopher Beck, Thomas Kyte, et al, Wrox Press, 2002, ISBN 186100690x). For the latest information on this new release, go to wrox.com.
• Expert One on One Oracle (by Thomas Kyte, Wrox Press, 2001, ISBN 1861004826). When four pages of "Ask Tom" is not enough, try the almost 1,300 pages of Tom in this book.
• Oracle Managing Technologist Tom Kyte answers your most difficult technology questions at asktom.oracle.com
• XML Developer's Kits are available on the Oracle Technology Network (OTN) at /technetwork/xml-developerkit