Old PL/SQL Scott's Blog

Scott Schwab's Blog about SQL, PL/SQL, and other stuff. This is blog will not be updated, since I have much less work with PL/SQL, SQL and databases in general.

Thursday, June 01, 2006

Parsing XML in PL/SQL DOM and extractValue

I pulled this post from an PL/SQL-XML forum post I wrote a couple of weeks ago. I don't know if it answered the original question, but I thought it good enough to repost here.

I have extracted data from XML in two ways

Using DOM to pull text values out by a value type document, with a list of , I have done the following (note, this is just a code snippet)

--tested with 9i
procedure proc(search_xml in out XMLTYPE)
search_doc DBMS_XMLDOM.DOMDocument;
term_nodelist DBMS_XMLDOM.DOMNodeList;
term_node DBMS_XMLDOM.DOMNode;
child_node DBMS_XMLDOM.DOMNode;
term_count NUMBER(5,0);
term_place NUMBER(5,0);
text VARCHAR2(2000);

search_doc := DBMS_XMLDOM.newDOMDocument(search_xml);
term_nodelist := DBMS_XMLDOM.getElementsByTagName('parent','child');

-- isNull() is important when checking the node list
IF NOT DBMS_XMLDOM.isNull(term_nodelist) THEN
term_count := DBMS_XMLDOM.getLength(term_nodeList);
term_place := 0; --double check to make sure this is should not be 1
EXIT WHEN term_place >= term_count;
term_node := DBMS_XMLDOM.item(term_nodelist, term_place);
IF DBMS_XMLDOM.hasChildNodes(term_node) THEN
child_node := DBMS_XMLDOM.getFirstChild(term_node);
text := DBMS_XMLDOM.getNodeValue(child_node);
-- other processing

The second way, is using XPATH and a SQL, the following cursor can
be used.

-- test with 10gR2
CURSOR grab_one_value(xt IN xmltype, path_to IN VARCHAR2) IS
SELECT extractvalue(xt, path_to, c_namespace)
FROM dual;

OPEN grab_one_value(xml_in, tag_in);
FETCH grab_one_value
INTO v_out;
CLOSE grab_one_value;
RETURN v_out;

I have not compared the preformance of these two methods, so I cannot say which is faster.

One note, if you follow the DOM model (first example), the DOM API is very similiar to Java or other DOM implementations. It is not a perfect match, but I have found looking at Java examples of DOM processing more useful then the Oracle examples.

Tuesday, May 23, 2006

QUTE unit-testing Tip #1

I have been using Qute for a while to test my PL/SQL.  Someday soon I plan to write up a how-to for my fellow users, but till then I thought I would just post a quick hint now and then
Q) You built a Unit-Test, and now are adding Test-Cases.  Darn the table you want to check a value of does not appear in the Outcomes "Check contents of test element ..." pull down.  How can you add it?
A) Cancel out of Outcomes GUI and go up to the Unit Test parent of your test case.  Under each unit test there is "Test Elements" section.  Right-Click on the label, and select "Add Test Element".  A dialog now appears:
- Leave the
Element Type CUSTOM
- Set usage to in/out
Usage: IN OUT
- In the name field, enter the table name
- Set the data type to table,  
Data Type: TABLE
- Set the data type as Declaration to table, as well
Data Type as Declaration: TABLE
Close the dialog.  Go back to and start defining an outcome again.  The table you just defined will be in the pull down list.
These elements don't have to be tables, you can create variable you wish to check in a outcome condition.  The variable value can be set in some customization section, before the success of the test is evaluated.

Monday, May 15, 2006

To Go or Not to Go

Over the last few weeks it has become apparent that my project planning and time estimations skills need improvement. Since we don't have much of a training budget these days, I have been reading and taking some online classes through the ACM. Reading about requirement generation and UML for defining and documenting the high level design.

This week weekend I was out enjoying some nice cool May weather, and I found this go-no go list in Mastering the Requirement Process. I plan to ask these questions more often, of my coworkers. So I don't forget them, and if any of the read my blog, to know what to expect, I thought I would document them here.

  • Is the product goal clear and unambiguous? Or does it contain fudge words?
  • Is it measurable? That is, will it give a clear indication you have successfully completed the project?
  • Does it indicate a benefit to the organization?
  • Is it viable? Is it possible to achieve the objectives of the project within the allotted time and budget?
  • Have you reached agreement on the scope of work?
  • Do some risks have a high probability of becoming problems?
  • Is the impact of these risks such that it makes the project unfeasible?
  • Is the cost reasonable given the product's benefit?
  • Are the stakeholders willing to be involved?
  • Do you have sufficient justification to invest in the project?
  • Do you have enough reasons not to invest in the project?
  • Is there any further investigation that you should do before launching the requirements project?
pg 64-65 of Mastering the Requirements Process 2nd Edition, Robertson & Robertson

Also, Qute 1.1 is out, so I am back testing and working on a batch and report generation setup for this tool.

Thursday, May 04, 2006

Eclipse verses JDeveloper

Over the last week, I have been juggling Java IDEs, JDeveloper and Eclipse. After a week of trying both, I still don't know which I should use.

If I was doing Java in the database, JDeveloper would be the easy choice. I am not, since the targeted Oracle edition is XE, and so no JVM.

I use SQLDeveloper, so the look, feel, and feature set of JDeveloper has a comfort level.

Using JDeveloper to create my UML diagrams has come in handing, as I look to delegate some of my work.

On the other hand, I am writing a web service to be hosted in JBoss, and apparently they have plug-ins for Eclipse to help.

I have a hard to use Java application, which some users use to interact with the web service, and building a replacement for it as an Eclipse Rich Client Application sounds fun.

Eclipse is widely used at the office.

I still don't know, maybe I'll stick to the try and true Emacs. It may not have the fancy GUI, but your hands never have to leave the keyboard.

-- a follow up --

About a month ago I stated that I was reading Mastering the Requirements Process. I stalled on that, and then a week ago found a copy of their second edition, at Borders. The second edition is worth the upgrade. What is new is the break down based on agility of the project, very (rabbit), some (horse), and little (elephant).

I had problems with the original version, as I tend to work in a quick, iterative development cycle, and the first edition's model did not map to it as well as I liked. The second edition gives a much easier mapping from their Volere model onto my development style.

Thursday, April 20, 2006

DbVisualizer for Oracle work

While I trend to stay with open source tools for Oracle work, I do have to recommend a commercial software package. DbVisualizer from Minq Software is saving me some significant time.

Yes it is another tool to run queries within, yes it is another browser of your schema, but two useful features I have found are
  1. Its ability to make entity relation graphs of just part of a schema, with easy controls for the layout and contents of the tables. Other tools may do it "better", but for convenience this rocks.
  2. The ability to view the XML I generate in the grid. It may not be important to some, but I push around plenty of XML with XMLType and SQL XMLElement(....) type statements. SQLDeveloper comes up short in this area.

A third feature, new in Version 5.0 is the graphic display of explain plans. I have only used this once or twice, so I don't know if it is that helpful, but it makes a pretty picture, and for some audiences that is worth a lot.

For a $100, it is not a bad investment.

Wednesday, April 12, 2006

Requirement writing

"Ah, I wish I knew what I should be doing", a statement I have made too often in my career. Oh well one way to address it is to have requirements, good requirements, testable requirements. Writing good requirements is hard, and so I have taken a break from my usual reading and have gone back to reading Mastering the Requirement Process.

The problem with reading this book is its competition, I have been playing too much XBox these days, but then again, is there too much XBox?

Oh by the way, my latest cool PL/SQL | SQL, is regex in queries, if your an old Unix shell or Perl hack, take a look. More on this later...

Thursday, April 06, 2006

Google Toolbar Installed

Google Toolbar Installed, testing sending blog entries from a tool bar button