もっと前のEntry
September 22, 2003
September 19, 2003
August 11, 2003
NCHARやNVARCHAR2の列と文字列リテラルを返す列をUNIONするようなばあい、そのままではエラーになります。
SQL> DESC Table1;
名前 NULL? 型
---------------- -------- ---------------------------
COLUMN1 NCHAR(10)
COLUMN2 NVARCHAR2(30)
SQL> SELECT COLUMN1, COLUMN2
2 FROM Table1
3 UNION
4 SELECT 'あああああ' AS COLUMN1,
5 'いいいいい' AS COLUMN2
6 FROM DUAL;
SELECT COLUMN1, COLUMN2
*
行1でエラーが発生しました。:
ORA-12704: character set mismatch
文字列リテラルは、前に'N'をつけてやると、UNICODEとして記述できますので、同じケースなら以下のようにすると、うまくいきます。
SQL> SELECT COLUMN1, COLUMN2 2 FROM Table1 3 UNION 4 SELECT N'あああああ' AS COLUMN1, 5 N'いいいいい' AS COLUMN2 6 FROM DUAL; COLUMN1 COLUMN2 ---------- ---------- えええええ くくくくく あああああ いいいいい
EXTRACT という関数がある。Oracle や PostgreSQL に実装されているが、Oracleでの挙動が少々わかりにくい。
注意: 抽出を実行するフィールドは、datetime_value_expr または interval_value_expr フィールドである必要があります。たとえば、DATE 値からは、YEAR、MONTH およびDAY のみを抽出できます。同様に、TIMESTAMP WITH TIME ZONE データ型からは、TIMEZONE_HOUR および TIMEZONE_MINUTE のみを抽出できます。
という注意書きが付いているが意味が良くわかんないのである。確かに、SELECT EXTRACT(SECOND FROM SYSDATE) FROM DUAL を実行しようとすると
ORA-30076: 抽出ソースの抽出フィールドが無効です
と叱られる。じゃあいったい SECOND ってキーワードは何なの? である。
このあいだ、使おうと思ったときに思い出せなかったので。
Oracle で、SQL を書く時に、各行について、「ある列が5以上かどうか」を真偽値でとりたい場合がある。こういうのは、DECODE 関数と LEAST 関数、GREATEST 関数なんかを駆使すると可能。たとえば、EMP表のSALが1000未満の人数、1000以上3000未満の人数、3000以上5000未満の人数、5000の人数を求めたいような場合を考えてみる。(もちろん、WHERE句で条件を指定して、COUNTした結果をUNIONすればいいのだけれど。)
SELECT SUM( DECODE( LEAST( SAL, 1000 ), 1000, 0, 1 ) ) AS "1000未満", SUM( DECODE( LEAST( SAL, 1000 ), 1000, DECODE( LEAST( SAL, 3000 ), 3000, 0, 1 ) ) ) AS "1000以上3000未満", SUM( DECODE( LEAST( SAL, 3000 ), 3000, DECODE( LEAST( SAL, 5000 ), 5000, 0, 1 ) ) ) AS "3000以上5000未満", SUM( DECODE( LEAST( SAL, 5000 ), 5000, 1, 0) ) AS "5000以上" FROM EMP /
ODBC を使ったサンプルは、巷にごろごろしてるのですが、SQLAllocEnv といった、推奨されない古い関数を使用したものが多いようです。新しい関数を使おうとすると、サンプルが少ないうえに、ドキュメントが英語で苦労しました。共通関数化したものをアップしておきます。使用例はそのうち追記します。
追記(2004/08/17)。トランザクションを明示的にコミットまたはロールバックするには、データソース接続後に自動コミットをオフにする必要があります。SDKドキュメントには、接続前でもいいと書かれていますが、後でないと期待した動きにならないようです。サンプルは未修正です。
追記(2004/08/18)。データソースに接続した後、自動コミットをオフにする処理を追加しました。
oo4o Class Library をインクルードした C++ のアプリをテストする環境を構築しようとしたのですが、テスト環境はHDが4.3GB(いまどき！) で、空き容量が600MB、Oracle9i Client (9.2.0.1.0) の「管理者」は798MBもあるため、すべて入れるのは無理な感じでした。以前「ランタイム(193MB)」でインストールして、oo4o が使用できないのは確認済みですが、管理者インストールは見るからに不要なものがいっぱい含まれていますし、多分、「ランタイム」にちょろっと追加すれば動きそうな気はします。そこで、カスタムでインストールできないかと思い、oo4o を使用するには最低限何が必要かということをググってみました。
ビンゴ！は、個人サイトですが、こちら。自分用メモということなので、以下に引用しておきます。
Orecle9i Clientランタイムだけだと接続できない？
CD Packに入っていた「Oracle9i Client」のCDを利用し、インストールタイプはランタイムを選択しました。 インストールも無事終わり、アプリケーションから接続を試みると「ORACLM32.dllが見つからない」のエラーになってしまった。
今度は、インストール時にカスタムを選択し、「Oracle Windows Interfaces 9.2.0.1.0」を追加インストールしたところアプリケーションからの接続ができました。 ODBC経由の接続も大丈夫です。
一見、ランタイムというとoo4oとかODBC関係もインストールされるものと思いましたが、上記のように、Windows専用のインターフェイスが必要ということでした。
とりあえず、インストールはできました・・・が、この空き容量でテスト、、、ですな、、、、、。
Oracle の表と列にコメントをつけることができます
SQL> comment on table MY_TABLE is '私のテーブル'; コメントが作成されました。 SQL> comment on column MY_TABLE.MY_COLUMN is '私のカラム'; コメントが作成されました。
こうして付けたコメントは USER_TAB_COMMENTS と USER_COL_COMMENTS というデータディクショナリで参照することができます。こんな感じのテーブルになってます。
SQL> desc USER_TAB_COMMENTS 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) COMMENTS VARCHAR2(4000) SQL> desc USER_COL_COMMENTS 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) COMMENTS VARCHAR2(4000)
MSDEのデータを手軽にExport/ImportするWSHスクリプトです。'.js'拡張子をつけて保存し、ダブルクリックするかコンソールからCSCRIPT Export.jsなどとして実行します。わざわざ実行速度の遅いJScriptで作っている理由は、単に try{...} catch( e ) {...} でトランザクション処理がしたかったというだけのことです 笑。
MSDE用になっているのは接続文字列だけなので、その部分のみ変更すればOracleやJETなど、ADOで接続可能なDBであれば使用可能だと思います。(でもWSHが利用可能な環境でしか実行できません。)
もう、8になってこの設定はいらなくなったんだけど、この前たまたま7のインストールされたマシンを使うことがあって困って調べたので、一応書いておくことにします。
Object Browserはあると何かと便利なので、よく使ってるし、この設定自体はものすごく何度もやってるはずなんだけどね。覚えるほどのことでもないし。
「BDE設定」をスタートメニューから起動し、ツリーの「環境設定」ってタブを選択。「 Drivers > Native > Oracle 」のDLL32とVENDOR INITを以下に設定します。
| データベース | DLL32 | VENDOR INIT |
|---|---|---|
| Ver 7.3 | SQLORA32.DLL | ORA73.DLL |
| Ver 8.03 | SQLORA32.DLL | ORA803.DLL |
| Ver 8.04以降 | SQLORA8.DLL | OCI.DLL |
UDLファイルを作成して、Microsoft OLE DB Provider for Oracleを使用してOracleに接続するテストを行い、成功することを確認したのですが･･･ASPより接続すると以下のエラー
-2147467259(Microsoft OLE DB Provider for Oracle) Oracle クライアントとネットワーク コンポーネントが見つかりません。これらのコンポーネントは Oracle Corporation から提供され、Oracle Version 7.3.3 (またはそれ以降) のクライアント ソフトウェアの一部としてインストールされます。 プロバイダは、これらのコンポーネントがインストールされるまで機能しません。
原因は、c:\oracle\ora92 ディレクトリに、Administrators, Authenticated Users, SYSTEMのみアクセス許可になっていたためでした。IUSR_(コンピュータ名)を追加すると、接続可能になりました。
開発環境がFAT32、実機がNTFSだったのが敗因でした:-(
実機セットアップのため、Windows 2000 Serverに、新たにMSDEとOracle Clientをインストールして、MSDEにデータベースを作成し、Oracleのリンクサーバを作成しました。
そして、MSDEから使用するためOracleの表をビューとして作成しようとするとこんなエラーが。
1>SELECT * FROM DB_SVR..USER_FOO.TABLE_BAR 2>GO メッセージ 7302、レベル 16、状態 1、サーバー TESTSRV、行 1 OLE DB プロバイダ 'MSDAORA.1' のインターフェイスを作成できませんでした。 1>
インターフェースと言う言葉から想像するに、COM回りのエラーであろうと想像は付くのですが(OLE DBってCOMですから)MDACの再インストールを行っても、MSDEの再インストールを行っても解決しませんでした。
一応、
280106 - [HOWTO] SQL Server で Oracle へのリンク サーバーのセットアップおよびトラブルシューティングを行う方法[support.microsoft.com]
に情報がありますが、REGSVR32で確認する前にMDACの再インストールをしたので、あまり有用な情報とは思えませんでした。
だいぶん長いこと試したのですが、結局は、
で、ビューが作成できるようになりました。Everyoneでなく、MSDEサービスがどのユーザの権限で実行されるのか調べて設定し直さなくてはいけないのですが、それはまたあとで。とりあえず原因は権限不足、だったみたいです。
2003.12.08 追記
SYSTEMアカウントに対し、権限を追加すればOKです。
HTMLに出力することを考えて、NULL項目は' 'と出力させようとしたSQLをテストしようとSQL*Plusで実行したのですが
SQL> SELECT NVL(emp_cd, ' ') AS emp_cd 2 FROM emp_tbl 3 / nbspに値を入力してください:
&は変数として扱われるの?そんなん知りませんでした。これはSQL*Plusの置換変数と言って、実行時に値を聞いてきます。ちなみに、&&var、と言うように&を2つ重ねると、そのSQL文内の後続の&varすべてを置換します。
で、置換変数を無効にするにはこうするそうです。
SQL> SET DEFINE OFF
こういうのもありましたね･･･
SQL> SELECT NVL(TRIM(' '), 'null!') FROM DUAL
2 /
NVL(T
-----
null!
オラクルで、CHAR(8)で年月日の区切りなしで日付を格納してる列を、TO_DATE, TO_CHARでSELECTすると、こんなエラーが出ました。
ORA-01841: (周)年は-4713と+9999の間で指定してください。0であってはなりません。
エラーメッセージを見てもさっぱりわけがわからないのですがたまたまデータが空白の行でした。そりゃあそうですね。
こんな感じで実行すると、同じエラーになりました。
CREATE TABLE SAMPLE_TABLE(
CHAR_DATE CHAR(8)
)
/
INSERT INTO SAMPLE_TABLE
VALUES(' ')
/
SELECT
TO_CHAR(TO_DATE(CHAR_DATE, 'YYYYMMDD'), 'YYYY/MM/DD') AS FORMATED_DATE
FROM SAMPLE_TABLE
/
このエラーのわけがわからなくて、Web上の情報をあさったところ、面白い事例がありました。
プロバイダがOraOLEDB.Oracle.1なら可能みたい。
MSDAORA.1ではできなかった。
ASPで書くのが面倒な処理はDB側でプロシージャにしておくと便利な場合がある。
MSDEでプロシージャを作ってASPから呼び出してみた。
'ASP
Dim adoCmd
Set adoCmd = Server.CreateObject("ADODB.Command")
With adoCmd
.CommandText = "pCheckValid"
.CommandType = adCmdStoredProc
Set .ActiveConnection = adoConGRDB
.Parameters("@emp_cd") = strEmpCd
.Parameters("@require_admin") = "1"
Call .Execute()
End With
If Not IsNull(adoCmd.Parameters("@ret_msg")) Then
Call Response.Write(adoCmd.Parameters("@ret_msg"))
Set adoCmd = Nothing
Exit Sub
End If
ポイントは、CommandText, CommandTypeを設定後にActiveConnectionを設定すること。そうしないと、パラメータがうまくバインドしない。(なんで?)
ちなみに、パラメータにEmptyを渡すとそのパラメータは「省略された」物として扱われる。ASPのCOOKIEやRequest.Formなどの初期値もEmptyなので、その値をパラメータとして渡す場合は、プロシージャ側で初期値を設定しておくのがお勧めである。
※ プロシージャ側でデフォルト値が指定されていないパラメータを省略すると実行時エラーになってしまう。
プロシージャの中身は、こんな感じ(実際に使ったものをいろいろ省略したため変なプロシージャだが)
SQLServerやMSDEの場合は、SHOWPLAN_ALLまたはSHOWPLAN_TEXTオプションを設定すると、以降発行するSQLはすべて実際には実行されず、実行計画を表示する、という感じになります。
C:\>osql -U username -P password -S servername 1> SET SHOWPLAN_TEXT ON 2> GO 1>通常の実行モードに戻すのは、ONをOFFに変えて、同じ操作を行います。
Oracleの場合はPLAN_TABLEをあらかじめ作っておく必要があります。
C:\>cd %ORACLE_HOME%\rdbmsxx\admin C:\...\admin>sqlplus sys/oracle@mylisner SQL*Plus: Release 9.2.0.1.0 - Production on 金 Sep 26 11:14:17 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL>@utlxplan.sql . . SQL>GRANT SELECT ON SYS.PLAN_TABLE TO PUBLIC; SQL>GRANT INSERT ON SYS.PLAN_TABLE TO PUBLIC; SQL>GRANT UPDATE ON SYS.PLAN_TABLE TO PUBLIC; SQL>GRANT DELETE ON SYS.PLAN_TABLE TO PUBLIC; SQL>CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE; SQL>実際に実行計画を取得するのは以下のようにします。
/* 実行計画をPLAN_TABLEに取得します */ TRUNCATE PLAN_TABLE; EXPLAIN PLAN SET STATEMENT_ID='PLANTEST' FOR -- コマンドの構文（文頭部) SELECT e.empno, e.ename, d.dname FROM scott.emp e, scott.dept d WHERE e.deptno=d.deptno; -- ターゲットSQL文 /* テーブルの中身を整形して表示します。 */ SELECT DECODE(id,0,'', LPAD(' ',3*(level-1))||level||'.'||position)||' '||operation||' '||options||' '||object_name||' '|| object_type||' '||DECODE(id,0,'Cost = '||position) QUERY FROM plan_table CONNECT BY PRIOR id = parent_id AND statement_id = 'PLANTEST' START WITH id = 0 AND statement_id = 'PLANTEST';
よくあるTipsですが、EDコマンドで起動するエディタを変更するには
SQL>SET define_editor = '任意のエディタ'とします。
define_editor = '任意のエディタ'