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.
Selective Privilege GrantingI 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.
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 VariablesAs 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
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.