As Published In
Oracle Magazine
May/June 2002

TECHNOLOGY: Ask Tom


On Creating, Dating, and Binding

By Tom Kyte Oracle Employee ACE

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

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.

Web Locator


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

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.

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.

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.

Selective Privilege Granting

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.

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.

Get More

 

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.

 

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

Dynamic SQL and Bind Variables


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.

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.

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.


Columnist Tom Kyte (thomas.kyte@oracle.com) has worked with the Oracle Services Industry Technology Group, in Reston, Virginia, since 1993.


Send us your comments