By Tom Kyte
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.
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';
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.
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.
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.
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 (firstname.lastname@example.org) has worked with the Oracle Services Industry Technology Group, in Reston, Virginia, since 1993.