Oracle スキーマ内のオブジェクトの全コンパイル

Oracle内スキーマの、全オブジェクトをコンパイルするコマンド。

begin
  UTL_RECOMP.RECOMP_SERIAL('TARGET_SCHEMA_NAME'); 
  --TARGET_SCHEMA_NAME is compile target schema name 
end;

https://gist.github.com/icoxfog417/5793956

OracleからSqlServerへ接続する

OracleからSqlServerへ接続する方法をまとめ。基本的にはODBCデータソースとDBLinkを使用する。イメージ的には、 SqlServer -> ODBCデータソース -> DBLink -> Oracle という形になる。

１．ODBCデータソースの登録
コントロールパネル > 管理ツール > データ ソース (ODBC) を選択。
ここでデータソースの追加ボタンを押し、SqlServerかSqlServerNativeClientのどちらかでデータソースを作成する。
接続するサーバーは サーバー名(IPアドレス)\インスタンス名 が基本だが、サーバー名だけでも良いようだ。SqlServerでインスタンスを作成すると自動的に作成されるようなので、SqlServerを立てている側のサーバーのデータソース設定を見ると正確。

他のログイン設定は、適宜設定。


２．Oracle Database Gatewayの登録
ORACLE_HOME\hs\admin　のフォルダにDatabaseGatewayの設定ファイルがある。
ファイル名は init<SID>.ora とする。SIDは任意の名称。dg4・・・とするのが慣例？のようだ(dgはDatabaseGatewayの略と思われる)。

内容は以下の通り。

HS_FDS_CONNECT_INFO = <odbc data_source_name>
HS_FDS_TRACE_LEVEL = <trace_level>

<odbc data_source_name>は先ほど作成したデータソース名、<trace_level>はOFFかONを設定。ONにしておくとoracle_home:[tg4rdb.log].にログを書き出してくれるので、ONにしておくのを推奨。


３．リスナーへの登録
OracleのリスナーにGatewayを登録する。ORACLE_HOME\network\admin にあるlistener.oraにGateway用の以下の記述を追記。

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=gateway_sid)
         (ORACLE_HOME=oracle_home_directory)
         (PROGRAM=dg4odbc)
      )
   )

gateway_sidは２で設定したSID(ファイル名の一部)。oracle_home_directoryはoracle_homeの値となる。

この設定を終えたのち、リスナーの再起動を行う。
※一度リスナーの再起動に失敗したことがあった。プロセスを見てみるとODBCデータソースがリスナーが起動するのに必要なポートを占有していたようなので、これを切ったところうまく立ち上がった（普通に起きる出来事なのかは定かでない・・・）。


４．接続名の登録
listener.oraと同フォルダにあるtnsnames.oraで定義されているOracleの接続名に追記を行う。

SQL_SRV=
   (DESCRIPTION=
    (ADDRESS= (PROTOCOL=TCP)(HOST=odbc_data_source_host)(PORT=1521))
    (CONNECT_DATA=(SID=gateway_sid))
    (HS=OK)
)

ホスト名が、相手先のSqlServerのホストでなく、ODBCデータソースのホスト(要するに自サーバー)である点に注意。


５．DBLinkの登録
ここまでくればあと一歩である。
以下のコマンドでDBlinkを作成する。なお、作成にはCREATE DATABASE LINK関連の権限が必要なため注意（こちら参考）。

CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO user_name IDENTIFIED BY password USING 'service_name';

サービス名は文字列としてシングルクォーテーションでくくらないといけない点に注意。PUBLICをつけるかどうかは場合によりけり。

これでDBLINKの作成が完了したので、以下のSQL文で動作確認を行う。

select 'X' from dual@dblink_name

これで出力が返ってくれば完成！である。


※一度相手先のSqlServerがとまっていたことがあり、このときOracle側のリスナーも起動が失敗した。再現実験ができていないのだが、もしかしたらリスナー起動時にGatewayの生死判定があり、相手先が止まっていた場合自分も起動しないという迷惑判定があるかもしれないので要注意。


＜参考＞
Configuring Oracle Database Gateway for ODBC

D Heterogeneous Services Initialization Parameters

Oracle SqlServer 関数/データ型変換表

Oracle/Sqlserver分について、関数・データ型について変換表を作成してみました。これが必要なくなる日を祈る(一刻も早く業界団体を作成し統一求む・・・)。


各種DB方言変換表
※変換表の追加や他のDBへの変換の追記など歓迎です


参考サイト
Oracle SQLServer 関数対比表

ASP.NET OracleProvidersによるフォーム認証がIIS上でうまくいかない

ASP.NETでは、各種認証機能がデフォルトで実装されている。とはいえそれはSqlServer用なので、Oracleで使用する場合はOracleProvidersをインストールする必要がある。

このインストール方法と使用方法についてはOracleから詳しいガイドが提供されているが、問題なのはこの通りにやった場合開発サーバー上ではうまく動くがIIS上ではうまく動かないということだ(後述するが、仮想パスの設定によってはうまく動く場合もある)。

Oracleから提供されているガイド：OracleR Database 2日で.NET開発者ガイド(11g R2)

上記ガイドの通りに設定しても、IIS上では永久にログインできない。IIS上でもまともに動くようにするには、web.configに設定されている各認証要素(membership・profile・roleManager)の設定を変更する必要がある。

具体的には、provider要素内のapplicationNameにサイト名を指定する。それと、ハッシュアルゴリズムにSHA1を設定しておく(これは必須かは分からない)。

<membership defaultProvider="SomeOracleMembershipProvider" hashAlgorithmType="SHA1" >

<providers>

<clear/>

<add name="SomeOracleMembershipProvider" type="Oracle.Web.Security.OracleMembershipProvider, Oracle.Web, Version=2.111.5.10, Culture=neutral, PublicKeyToken=89b483f429c47342" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" connectionStringName="ConnectionString" applicationName="/webSite" />

</providers>

</membership>

認証情報はサイト名（上記の場合webSite）で管理されている。そして、これは仮想パスによって検索されるようだ。

そのため、仮想パスが/webSiteであるならうまくいくが(開発サーバーでの起動はこれに該当)、サーバー上に配置して仮想パスがこれとずれた場合(app/webSiteなど)、認証情報が検索できずうまくいかないという現象が起こるらしい。

これを回避するには、明示的にアプリケーション名を指定しそちらで認証情報を取得するようにする・・・ということのようだ。

参考サイト

Always set the "applicationName" property when configuring ASP.NET 2.0 Membership and other Providers

OracleMembershipProvider Form Authentication

これを発見するのに丸一日費やした。ASP.NET＋Oracleはかくも茨の道なのか・・・

ASP.NET Oracle Providers for ASP.NET設定時にconnectionStringでエラー

Oracle Providers for ASP.NETの設定時に、接続文字列関連のエラーが発生する場合、以下点をチェックする。

１．サーバー以外に、ローカルのmachine.configを編集しているか
通常はローカルのVisualStudioからASP.NETの構成を参照するので、ローカルのmachine.configを修正しないとまずアクセスが出来ない。※これとは別に、当然サーバー上の設定も必要。


２．.NETフレームワークのバージョンは適切か
machine.configに該当の設定があるのは2.0と4.0の二つある。マニュアル上では2.0で紹介されているが、.NET Framework4.0を入れている場合そちらの修正が必要

参考ドキュメント(P104あたり)
Oracle Database 2日で.NET開発者ガイド(11g R2)

Oracle DBConsoleが起動しない場合の対処法

1.状況確認用コマンド
emctl status dbconsole

2.起動用コマンド
emctl start dbconsole　停止の場合startをstopに

3.リポジトリの再構築(方法が2つある？)
1.構成情報を再構築？
set oracle_sid=XXXXX(SID)
emca -deconfig dbcontrol db
emca -config dbcontrol db

2.構成情報を削除/再作成(1がうまくいかなかったときに試すと良い。これまでの設定が消えるらしいので要注意)
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

もしくは

emca -deconfig dbcontrol db
emca -config dbcontrol db -repos recreate

単純に削除しても失敗する場合は、SYSMANのユーザーなどを明示的にDROPする必要がある(参考のOracleDBConsoleの再構築参照)

4.参考

　DBConsole for Oracle Enterprise Manager

21 Enterprise Manager Configuration Assistant（EMCA）
OracleDBConsoleの再構築

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 ソース内の全文検索方法

プロシージャ、ファンクション等のソース内を、指定した単語で全文検索する方法

【例】
「あいうえお」と記述しているソース名、オブジェクトタイプ、行番号、ソース行内容を一覧表示

　select name,type,line,text   from user_source
　　where text like '%あいうえお%'   order by 1,2,3;

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;
/

Oracleクライアントインストールディレクトリ

いつからか、クライアントのインストール先が変わっている模様

(少なくとも)8まで
C:\oracle\

(少なくとも)11g以降
C:\app\(ユーザー名)\product
→ODP.NETを入れている場合、有用なサンプルコードが以下フォルダにあるので要チェック
　C:\app\\(ユーザー名)\product\11.2.0\client_3\odp.net\samples

Oracleでテーブルの列定義を取得する

以下SQLで可能。

select * from USER_TAB_COLUMNS

ORDER BY COLUMN_ID　をつけると、実際の定義順でデータを取得できる。
ALL_TAB_COLUMNS だとそれ以外のテーブル定義も取れる模様。

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でLINQ/EntityDataFrameworkを使用する

今までサポートがなかったがとうとう公式対応したもよう。ただ、ベータ版で「本番機には使用するな」との注意書きつき

32-bit ODAC (11.2.0.2.30) Entity Framework and LINQ to Entities

なお、これまではLINQのみオープンソースのツールがあった(それ以外は有償のツールのみ)
DBlinq

2012/2/3追記
2011/12/28に正式版がリリースされていた模様。宛先はこちら。
32-bit Oracle Data Access Components (ODAC)

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

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

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

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

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