The Final Tip is Coming Soon

All,

Sorry about the cliffhanger on the final tip of my five part series; the Windows installation my DB was on crashed, and I have to recover the OS so I can get the database back up and running. That’s why I just love the Mac.

There will be an update soon, promise.

Wouldn’t it be fantastic if you could simply pull data from the database in XML? Many shops use XML for data transfer, web services, reports, and more. Oracle’s XML Publisher product can retrieve XML from an HTTP feed and use it to generate rich reports with graphs, images, and other content, and then mail, fax, print, or FTP them.

All we need is the actual XML!

For this, we have the DBMS_XMLGEN package. This package generates XML on the fly using any query you want; in addition, it’s extremely easy to use from either the SQL prompt or in code, as it’s just a simple query.

Generating XML From the Database

Take this standard query:

SQL> select employee_id, first_name, last_name, phone_number
  2  from employees where rownum < 6

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 PHONE_NUMBER
----------- -------------------- ------------------------- --------------------
        100 Steven               King                      515.123.4567
        101 Neena                Kochhar                   515.123.4568
        102 Lex                  De Haan                   515.123.4569
        103 Alexander            Hunold                    590.423.4567
        104 Bruce                Ernst                     590.423.4568

We get our standard output with no frills. Behold as we transform this into XML!

set pages 0
set linesize 150
set long 9999999
set head off
SQL> select dbms_xmlgen.getxml('select employee_id, first_name,
  2  last_name, phone_number from employees where rownum < 6') xml
  3  from dual

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>101</EMPLOYEE_ID>
  <FIRST_NAME>Neena</FIRST_NAME>
  <LAST_NAME>Kochhar</LAST_NAME>
  <PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>102</EMPLOYEE_ID>
  <FIRST_NAME>Lex</FIRST_NAME>
  <LAST_NAME>De Haan</LAST_NAME>
  <PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>103</EMPLOYEE_ID>
  <FIRST_NAME>Alexander</FIRST_NAME>
  <LAST_NAME>Hunold</LAST_NAME>
  <PHONE_NUMBER>590.423.4567</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>104</EMPLOYEE_ID>
  <FIRST_NAME>Bruce</FIRST_NAME>
  <LAST_NAME>Ernst</LAST_NAME>
  <PHONE_NUMBER>590.423.4568</PHONE_NUMBER>
 </ROW>
</ROWSET>

Beautiful! Fully compliant XML that can be easily integrated into any application, with ROWSET and ROW tags in place to identify nodes, and tags for each column you pulled out of the database.

All we had to do was wrap the query in the DBMS_XMLGEN.GETXML function call, selected from DUAL. The query remained exactly the same.

A More Advanced Use

Let’s take it one step further. Most XML has subnodes for each main node. For instance, what if we wanted to pull XML for every department, and a subnode for every employee under it? We can use the CURSOR function!

SQL> select department_id, department_name,
  2  cursor(select first_name, last_name
  3  from employees e
  4  where e.department_id = d.department_id) emp_row
  5  from departments d
  6* where rownum < 4

DEPARTMENT_ID DEPARTMENT_NAME                EMP_ROW
------------- ------------------------------ --------------------
           10 Administration                 CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

           20 Marketing                      CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Michael              Hartstein
Pat                  Fay

           30 Purchasing                     CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Den                  Raphaely
Alexander            Khoo
Shelli               Baida
Sigal                Tobias
Guy                  Himuro
Karen                Colmenares

6 rows selected.

The results don’t look too impressive at the SQL prompt. However, watch as we surround it with a call to DBMS_XMLGEN.GETXML:

SQL> select dbms_xmlgen.getxml('
  2  select department_id, department_name,
  3  cursor(select first_name, last_name
  4  from employees e
  5  where e.department_id = d.department_id) emp_row
  6  from departments d
  7  where rownum < 4
  8* ') from dual

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Jennifer</FIRST_NAME>
    <LAST_NAME>Whalen</LAST_NAME>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
 <ROW>
  <DEPARTMENT_ID>20</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Michael</FIRST_NAME>
    <LAST_NAME>Hartstein</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Pat</FIRST_NAME>
    <LAST_NAME>Fay</LAST_NAME>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
 <ROW>
  <DEPARTMENT_ID>30</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Den</FIRST_NAME>
    <LAST_NAME>Raphaely</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Alexander</FIRST_NAME>
    <LAST_NAME>Khoo</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Shelli</FIRST_NAME>
    <LAST_NAME>Baida</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Sigal</FIRST_NAME>
    <LAST_NAME>Tobias</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Guy</FIRST_NAME>
    <LAST_NAME>Himuro</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Karen</FIRST_NAME>
    <LAST_NAME>Colmenares</LAST_NAME>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
</ROWSET>

Note that I didn’t change the query in any way. But check out the results! We have each DEPARTMENT as a ROW tag, and the cursor we created gives us an EMP_ROW node containing recurring EMP_ROW_ROW nodes.

Conclusion

This can be extremely useful for quick retrieval of web records. Simply make a page that accepts input, such as DEPARTMENT_ID, and passes it into the query above. Display the response directly to the screen, and you have an easy XML display program. The best part comes with reports. XML Publisher is made to accept XML that looks just like this and form extremely detailed reports using templates made in Microsoft Word. With queries such as these and XML Publisher you can have a full reporting suite that easily pulls data, forms it into a PDF, DOC, XLS, or HTML report, and distributes it anywhere you would like it to go.

Today’s (well, yesterday’s really) trick deals with the storage of data. Let me first say that the methods I’m about to show you are NOT useful for where clauses or in normal database design. However, they can be very useful when you need a reference table that can store anything in a simple column.

For instance, take a look at Google. Google is an amazing search engine, capable of parsing billions of sites and enabling fast searching of the data it harvests.

In addition, Google is able to parse prices, mileage, dates, and other important elements, then use them as they were meant to be used; for instance, using a price to power Froogle’s bargain hunting tool.

If we were trying to duplicate the same concept into Oracle, we’d somehow need to create a table with an ID and a token, the token being the word that was parsed out of a complete document. However, what if we want that column to be able to store numbers, characters, dates…and we want them to act natively?

Enter ANYDATA. ANYDATA is a “self describing data instance type.” All this truly means is that the type not only holds a value, but it also holds its own datatype within. It also employs many methods that allow us to perform various functions with it.

So, back to our parser! We’ll start off with a very simple table:

create table search_words (
token_id number not null primary key,
token sys.anydata
);

Now we must populate the table, but normal inserts unfortunately wont work. We must use ANYDATA’s built-in Convert methods to perform our inserts. We’ll use the phrase “Make 50 dollars on 12/01/06″

INSERT INTO SEARCH_WORDS
VALUES (1, sys.anydata.convertVarchar2('Make');
INSERT INTO SEARCH_WORDS
VALUES (2, sys.anydata.convertNumber(50);
INSERT INTO SEARCH_WORDS
VALUES (3, sys.anydata.convertVarchar2('dollars');
INSERT INTO SEARCH_WORDS
VALUES (4, sys.anydata.convertVarchar2('on');
INSERT INTO SEARCH_WORDS
VALUES (5, sys.anydata.convertDate(to_date('12/01/2006', 'MM/DD/YYYY');
commit;

And now we have data! Let’s query it and see what it looks like.

SQL> select * from search_words; TOKEN_ID TOKEN()
---------- --------------------
1 ANYDATA()
2 ANYDATA()
3 ANYDATA()
4 ANYDATA()
5 ANYDATA()

Oops! That’s not how we want our data to look. Unfortunately, Oracle is not yet sophisticated enough to understand which data type it is querying. Because of this, we have to make a function.

CREATE OR REPLACE FUNCTION getTokens(pv_token in sys.anydata)
RETURN VARCHAR2 IS

lv_number      NUMBER;
lv_date     DATE;
lv_varchar2 VARCHAR2(4000);

BEGIN

CASE pv_token.getTypeName
WHEN 'SYS.NUMBER' THEN
IF (pv_token.getNumber(lv_number) = dbms_types.success) THEN
lv_varchar2 := lv_number;
END IF;
WHEN 'SYS.DATE' THEN
IF (pv_token.getDate(lv_date) = dbms_types.success) THEN
lv_varchar2 := lv_date;
END IF;
WHEN 'SYS.VARCHAR2' THEN
IF (pv_token.getVarchar2(lv_varchar2) = dbms_types.success) THENa
NULL;
END IF;
ELSE
lv_varchar2 := 'unknown datatype';
END CASE;

RETURN lv_varchar2;

END getTokens;

Notice that we used the ANYDATA methods again, but this time with getVarchar2, getNumber, and getDate. Finally, let’s go ahead and query the table using the function we created.

SQL> column token format a20
SQL> select token_id, getTokens(token) token from search_words;

TOKEN_ID TOKEN
---------- --------------------
1 Make
2 50
3 dollars
4 on
5 01-DEC-06

And we have our data! Since we have to use a function, you can see why this would not be very good in a WHERE clause. However, for storing multiple datatypes in a single table, it’s tops.

You can find more information about ANYDATA in the Database Application Developer’s Guide, and information about its methods in the PL/SQL Packages and Types Reference.

Join me next time to learn about pulling XML from the database…with any query!

It seems I’ve run out of time in the day…I had hoped to provide an article today on Trick #3, yet work has piled up and I was not able to get it done.

Rest assured, despite my previous article, that I did not spend all day playing with my new iPod!

Tomorrow (well, today really, as it’s past midnight) I will try to catch up with Tricks 3 and 4. Stay tuned!

I had made a post about the 80GB iPod, how much I loved it, and how great it was, and now suddenly it’s deleted. Ah well, c’est la vie, no?

Anyways, I don’t remember everything that was there, but suffice it to say that the iPod is amazing. You can store around 20,000 songs, over a hundred videos, TV shows, and the new iPods even have games in brilliant color.

Not only that, but the new 80GB iPod has a better battery life with up to 20 hours of music playback and 6.5 hours of video playback which is GREAT for plane rides without having to pull out the laptop. If you run out of battery, you can plug the iPod into your laptop via USB and leech battery power from it. Perfection!

The new iPod also has clarity enhancements. Videos can be larger and squeezed onto the iPod’s screen for great playback on a TV. The brightness has also been adjusted, which allows a beautiful retina-burning display of video.

Take a look at the old vs. new pics below:

While the actual video watching is not THAT bright, it is what my camera picked up, and this camera was using NO flash. A visual feast, the likes of which will make you want to stare at the sun to cool your eyes off after a movie.

The best part is that Apple lowered their prices at the same time. The new 80GB iPod is only $350.00, whereas the old 60GB one was $400. You can’t beat the price for this much space and such great features.

There are many Oracle tools that can wreak havoc on an unsuspecting DBA or developer, but all in all they are for the common good. Though Oracle never puts procedures in place that are made for practical jokes, the DBMS_ADVANCED_REWRITE package comes close.

DBMS_ADVANCED_REWRITE allows you to transform queries on the fly. An unsuspecting developer may try

select ename from emp;

And instead end up with

select 'Haha, no ename for you' from dual;

Sounds like a barrel of laughs to me, and a great practical joke item. However, the package gives us some technical benefit as well.

For instance, we can rewrite a query to use dimension tables where appropriate to give us the best possible joins. We, the DBAs, can change application code if the developers are having trouble tracking it down. Using this package, we can customize queries on OUR side, instead of theirs.

Some other uses may include adding hints, re-arranging join orders, or computing as necessary. Take a sales application, for instance. You store sale prices in your database, and the application has a bit of compiled code that adds sales tax on the fly.

Uh oh! The state changed the sales tax from 3.5% to 5% (pesky state). The developers have no original code, just a compiled module, and it will take time to implement new code. All we know is that the query always comes in as:

select price + (price * 0.035) as tax
from sales_table;

We, the DBA, can change it where the developer cannot. Simply change the query to:

select price + (price * 0.05) as tax
from sales_table;

Whenever it comes in.

Okay, enough theory. Let’s go ahead and see how it works. This tax example is a great one.

create table sales_table (
item_id number primary key,
price number (10,2)
);

insert into sales_table values (1, 14.95);
insert into sales_table values (2, 17.50);
insert into sales_table values (3, 21.35);
commit;

SQL> select price + (price * 0.035) as tax from sales_table;

TAX
----------
15.47325
18.1125
22.09725

begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
'new_tax',
'select price + (price * 0.035) as tax from sales_table',
'select price + (price * 0.05) as tax from sales_table',
false);
end;
/

SQL> select price + (price * 0.035) as tax from sales_table;

TAX
----------
15.6975
18.375
22.4175

The results we wanted with little fuss!

If you haven’t gathered, it’s pretty easy to use. Simple give a name, an original query, and a target query, and you’re off.

Once you’ve made all of these, you can always look for them and delete as necessary. Simply query DBA_REWRITE_EQUIVALENCES. You can use the DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE as necessary.

Yes, this package can be potentially harmful. But like nuclear weaponry, hot coffee, and the Internet, it can also be very useful for your needs.

Join me tomorrow when we go over another trick! We’ll actually see how to store any data in a column, no matter what type of data it is.

Yesterday I announced that I would be doing a five day series entitle “The 5 Oracle Tricks You Never Knew.” Today’s trick is pretty simple, and it allows you to copy user accounts from one instance to another with the same password, grants, roles, etc, without using the exp/imp tools.

Everyone knows that you can use “create user username identified by password” to create a new user. What most people don’t know is that you can actually copy the password of a user from one database to another as well.

You wont be able to see the password (sorry hackers), but you can copy it in its encoded form.

Instead of using:

create user test identified by password;

You will use:

create user test identified by values 'encoded password';

The encoded password will actually be the encrypted password stored in the database that is visible to the DBA eye. This is a 16 character password you will find in the DBA_USERS view in the PASSWORD column.

You can also use:

alter user test identified by values 'encoded password';

If you have already created the user and need to change the password to what it might have been on another system.

This is extremely useful for DBAs that are copying their production database to development, or migrating a database from one instance to another. Too often, DBAs are forced to remember the details they have, copy them from the DBA_USERS view, and try to create the new users as close as possible to the original.

But we’re more sophisticated! Instead, we will use the DBMS_METADATA package to pull the user information.

set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl('USER', username) || '/' usercreate
from dba_users;

USERCREATE
--------------------------------------------------------------

CREATE USER "SYS" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "SYSTEM" TEMPORARY TABLESPACE "TEMP"
/

...

Do you want to get all their roles and grants as well? Nothing easier! Look at the following:

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SYS') FROM DUAL;

From this, we can form our Unified User Copy-o-matic with the following query:

set head off
set pages 0
set long 9999999
spool user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS;
spool off;

And voila! All of our users and grants all in one simple script.

If you would like simple alter commands instead, we can always skip using DBMS_METADATA. Instead, use this query:

select 'alter user ' || username ||
' identified by values ''' || password || ''';'
from dba_users;

Note that in the case above, there are three single quotes to the left and right of password. Don’t use double quotes.

That’s it for today; a rather easy trick that you can use many times during your DBA career. If you already knew this trick, don’t worry! There’s more to come, the rest a bit more advanced and a bit more obscure. Join me tomorrow and we’ll talk about how to transform any query into any other on the back-end, sometimes with very amusing results!

Hello all!  Over the next few days I’ll be updating my blog with 5 Oracle tricks that you may not even know about.  They’re all pretty neat, and actually useful to a DBA (one of them is even useful for practical jokes)!

These tips will be presented daily in the mornings, so check back each day!  I’ll be starting tomorrow with one that’s common…one you might actually know about: copying user accounts the RIGHT way.

It has been quite a while since I made a post, but things have been extremely hectic!

I took a road trip with my wife and two kids to Oklahoma City from Virginia Beach…this was a three day drive for us, as kids always slow you down as well as make the drive seem much longer.  Are we there yet?  We had a blast, two weeks in OK City with in-laws and having a great time.  From there, it was Dallas for a week, then the long drive home.

Also during this time, I was awarded the Oracle ACE achievement.  You can see my ACE page on Oracle’s site.  OCM, ACE, OpenWorld speaker…they REALLY need an Oracle Alchemist award too.

Lastly, I’ve been working with the Hampton Roads Oracle User’s Group and the Greater Richmond Oracle User’s Group on a one day conference we’re putting on in Williamsburg, VA called Oracle Conference on the James.  Check it out!  We’re going to have some great speakers and vendors.

Anyways, that’s it for me at the present, there will be more to come.  I’m not settled back in my familiar recliner, typing away into the night.