Oracle PL/SQL XMLデータをFunctionで受け取る２

前回はXMLDOMを使用した形式だったが、Xpathを使用しての処理も可能。


・特定ノードの件数を取得(件数をlvCountに設定・XMLTYPEのデータをpvXmlとする)

 SELECT count(*) INTO lvCount FROM TABLE(XMLSequence(pvXml.extract('/STUDENTS/STUDENT')));

  各要素へは以下のようにアクセス可能

FOR i IN 1 .. lvCount LOOP
  pvXml.extract('/STUDENTS/STUDENT['|| i || ']');
END LOOP;

・テキストノードの値取得(注意事項あり。他記事参照)

pvXml.extract('/STUDENTS/STUDENT/NAME/text()').getStringVal();

・属性値の取得

pvXml.extract('/STUDENTS/STUDENT/@ID').getStringVal();

※なお、単純なextractの結果はXMLTYPEとして扱われる。複数の形式のデータが混在する場合は、これを使用し切り分けることが可能

xmlTeacher := pvXml.extract('/PEOPLE/TEACHERS');
xmlStudent := pvXml.extract('/PEOPLE/STUDENTS');

Oracle XPathを使用してテキストノードの値を取得する場合の注意点

Pl/SQLでもXpathを使用して簡単にXMLノードの値を取得することが可能だが、以下のような空ノードの値を取得する際は注意が必要。

<document>
<content></content>
</document>

この状態で extract('/document/content/text()').getStringVal()を行うと「ORA-30625: NULL SELF引数のメソッド・ディスパッチは使用できません」のエラーが発生する。

これを回避したい場合は一旦EXISTSNODEでノード内に値があるかを確認し、ある場合に取得するといった対応が必要となる。

以下は、そのための簡単なFUNCTIONの例。

・安全にテキストノードの値を取得するための関数

  FUNCTION GET_TEXT_NODE_VALUE(xmlData XMLTYPE,xpathStr VARCHAR2 ) RETURN VARCHAR2 IS
lvJudge NUMBER(1);
  BEGIN
lvJudge := xmlData.existsNode(xpathStr);
IF lvJudge = 0 THEN --空ノードの場合
RETURN NULL;
ELSE
RETURN xmlData.extract(xpathStr).getStringVal();
END IF;
  END;

・使い方
GET_TEXT_NODE_VALUE(xmlData,'/document/content/text()');


参考リンク
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス
10g リリース2（10.2） - 198 XMLTYPE

Oracle PL/SQL XMLデータをFunctionで受け取る

PL/SQLでXMLデータを受け取り、処理する方法。
ASP.NET側で入力フォームの情報をXML化し、そのXMLをPL/SQLで受け取って更新処理をするという寸法。
・OracleへのアクセスはODP.NETを使用。更新対象のテーブルはOracleのデフォルト環境scott/tigerにあるDEPTテーブルとしている。

ASP.NET側 ※msgLabelに処理結果を出力する想定

'XML型のデータを作成し更新を行う
Dim blr As StringBuilder = New StringBuilder()

'Oracle更新処理
Dim conStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
Dim ocon As OracleConnection = New OracleConnection(conStr)
Dim oCommand As OracleCommand = New OracleCommand("XML_UPDATER.XML_UPDATE", ocon)
oCommand.CommandType = CommandType.StoredProcedure

Dim rv As OracleParameter = oCommand.Parameters.Add("rv", OracleDbType.Varchar2)
rv.Direction = ParameterDirection.ReturnValue
rv.Size = 10000 'サイズは適当

Dim p1 As OracleParameter = oCommand.Parameters.Add("p1", OracleDbType.XmlType)
p1.Direction = ParameterDirection.Input

blr.Append("<?xml version=""1.0""?><depts>")
blr.Append("<dept>")
blr.Append("<dept><dname>DNAME_HOGE</DNAME><LOC ADRCODE=""1"">LOC_HOGE</LOC>
</DEPT>")
blr.Append("</DEPT>")
blr.Append("</DEPTS>")
p1.Size = blr.Length
p1.Value = blr.ToString

Try
ocon.Open()
oCommand.ExecuteNonQuery()
If IsDBNull(oCommand.Parameters("rv").Value) Or oCommand.Parameters("rv").Value.ToString = "null" Then
msgLabel.Text = "SUCCESS"
Else
msgLabel.Text = oCommand.Parameters("rv").Value.ToString
End If
Catch ex As Exception
msgLabel.Text = "Oracle:" + oCommand.Parameters("rv").Value.ToString + " ASP:" + ex.Message
Finally
ocon.Close()

End Try

PL/SQL側(パッケージを作成。パッケージヘッドは省略)

CREATE OR REPLACE PACKAGE BODY SCOTT.XML_UPDATER
IS
FUNCTION XML_UPDATE(pvXml XMLTYPE ) RETURN VARCHAR2 IS
xmlDoc dbms_xmldom.DomDocument;
nodeList dbms_xmldom.DomNodeList;
lvList dbms_xmldom.DomNode;
lvRow SCOTT.DEPT%ROWTYPE;
BEGIN
xmlDoc := xmldom.newDomDOcument(pvXml);
nodeList := xmldom.getElementsByTagName(xmlDoc, 'DEPT');

IF NOT xmldom.isNull(nodeList) THEN
FOR i IN 0 .. xmldom.getLength(nodeList) - 1 LOOP
lvList := xmldom.item(nodeList,i);
lvRow.DNAME := GET_ELEMENT_VALUE_BY_TAG_NAME(lvList,'DNAME');

lvRow.LOC := GET_ELEMENT_ATTR_BY_TAG_NAME(lvList,'LOC','ADRCODE');
lvRow.LOC := GET_ELEMENT_VALUE_BY_TAG_NAME(lvList,'LOC') || '-' || lvRow.LOC;

lvRow.DEPTNO := GET_ELEMENT_VALUE_BY_TAG_NAME(lvList,'DEPTNO');
IF lvRow.DEPTNO IS NULL OR lvRow.DEPTNO = '' THEN
SELECT MAX(DEPTNO) INTO lvRow.DEPTNO FROM SCOTT.DEPT;
lvRow.DEPTNO := lvRow.DEPTNO + 1;
INSERT INTO SCOTT.DEPT VALUES lvRow;
ELSE
UPDATE SCOTT.DEPT SET ROW = lvRow WHERE DEPTNO = lvRow.DEPTNO;
END IF;
END LOOP;
END IF;

COMMIT;
RETURN NULL;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN SQLERRM;
END ;

--テキストノードの値を取得
FUNCTION GET_ELEMENT_VALUE_BY_TAG_NAME(domEl dbms_xmldom.DomElement,tagName VARCHAR2) RETURN VARCHAR2 IS
tempList dbms_xmldom.DomNodeList;
oneItem dbms_xmldom.DomNode;
BEGIN
tempList := xmldom.getElementsByTagName(domEl,tagName);
oneItem := xmldom.item(tempList,0); --要素内に、タグ名に合致するノードは1つしかないと想定
RETURN xmldom.getNodeValue(xmldom.getFirstChild(oneItem));
END ;

FUNCTION GET_ELEMENT_VALUE_BY_TAG_NAME(domNd dbms_xmldom.DomNode,tagName VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN GET_ELEMENT_VALUE_BY_TAG_NAME(xmldom.makeElement(domNd),tagName);
END ;

--ノードの属性値を取得
FUNCTION GET_ELEMENT_ATTR_BY_TAG_NAME(domEl dbms_xmldom.DomElement,tagName VARCHAR2,attrName VARCHAR2) RETURN VARCHAR2 IS
tempList dbms_xmldom.DomNodeList;
oneItem dbms_xmldom.DomElement;
BEGIN
tempList := xmldom.getElementsByTagName(domEl,tagName);
oneItem := xmldom.makeElement(xmldom.item(tempList,0));
RETURN xmldom.getAttribute(oneItem,attrName);
END;

FUNCTION GET_ELEMENT_ATTR_BY_TAG_NAME(domNd dbms_xmldom.DomNode,tagName VARCHAR2,attrName VARCHAR2) RETURN VARCHAR2 IS

BEGIN
RETURN GET_ELEMENT_ATTR_BY_TAG_NAME(xmldom.makeElement(domNd),tagName,attrName);
END;

END XML_UPDATER;
/

ASP.NETからの、ODPを使用したOracleFunctionの呼出

ODPを使用する場合の一番のネックになるのは、最初に設定するパラメータは返り値でないといけないということ。
これをミスるとパラメータの長さが違いますみたいなエラーが延々と出てドハマリする

参考リンク

Oracle側

CREATE OR REPLACE FUNCTION SaveData(en varchar2) RETURN VARCHAR2 IS
hoge varchar2(100);
Begin
hoge := en || ' is inputed ';
Insert into TestSaveData values(hoge);
Return hoge;
End;

ASP.NET側

Try
Dim cnn As New Oracle.DataAccess.Client.OracleConnection("Data Source=OracleG;User Id=Scott;password=Tiger")
Dim cmd As New Oracle.DataAccess.Client.OracleCommand("SAVEDATA", cnn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("RS", Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.ReturnValue)
cmd.Parameters.Add("EN", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100, "INPUT", ParameterDirection.Input)

cnn.Open()
Dim ooo As Object = cmd.ExecuteScalar()
Dim q As Object = cmd.Parameters("RS").OracleDbType.ToString()
Dim s As String = q
cnn.Close()
Catch ex As Oracle.DataAccess.Client.OracleException
MsgBox(ex.Message())
End Try

Oracle PL/SQL パイプライン表関数の使い方について

・概要
パイプライン表関数は、率直に言えばFUNCTIONの返り値をそのままビュー出力として使える機能である。
(この時のFUNCTIONの返り値は当然レコードセット的なものである必要があるが、後述)

・前提
パイプライン表関数が返すレコードセットのデータ型はどこかで定義されている必要がある。
どこか→PACKAGEならPACKAGEヘッド、あとは素直にTYPEを作成しておく。

・作成方法
任意のSQLに対するカーソルを作成し、LOOP中にPIPE ROWでレコード出力
参考

・使いどころ
出力するデータ型を定義するのは手間であるため、項目が多い出力に向かない。
そのため、キー情報のみ取得しWHERE EXIST区で使うなどするほうがベターと思われる