This discussion is archived
4 Replies Latest reply: Dec 13, 2012 9:09 PM by 979676 RSS

replace関数について

979676 Newbie
Currently Being Moderated
皆様こんにちは。
困っていることがあるのですが、助けていただけますでしょうか。

断片化を確認するために、以下のSQLを作成しました。
OWNERとOBJECT_NAMEを標準入力するようにしています。

======================================================================
SELECT A.OWNER,A.OBJECT_TYPE,A.OBJECT_NAME,B.PARTITION_NAME,B.FILE_ID, B.BLOCK_ID, B.BLOCKS
FROM DBA_OBJECTS A, DBA_EXTENTS B
WHERE A.OWNER = B.OWNER AND
A.OBJECT_NAME = B.SEGMENT_NAME AND
NVL(A.SUBOBJECT_NAME,' ') = NVL(B.PARTITION_NAME,' ') AND
A.OBJECT_TYPE = B.SEGMENT_TYPE AND
A.OWNER = '&OWN' AND
A.OBJECT_NAME in (&OBJNM)
ORDER BY 1,2,3,4,5,6;
======================================================================


「&OBJNM」には複数オブジェクトが入るので、指定する際には、「'TEST1','TEST2'」など、カンマとシングルクォーテーションも
記載する必要があります。
ただ、面倒だなと思い、「TEST1 TEST2」のように、オブジェクト名+スペース+オブジェクト名のような記載方法に変更したいと思いました。
そして、以下のSQLを作成したのですが、実行してみると、「o rows selected」と返ってきます。(オブジェクトは存在しています。)

======================================================================
SELECT A.OWNER,A.OBJECT_TYPE,A.OBJECT_NAME,B.PARTITION_NAME,B.FILE_ID, B.BLOCK_ID, B.BLOCKS
FROM DBA_OBJECTS A, DBA_EXTENTS B
WHERE A.OWNER = B.OWNER AND
A.OBJECT_NAME = B.SEGMENT_NAME AND
NVL(A.SUBOBJECT_NAME,' ') = NVL(B.PARTITION_NAME,' ') AND
A.OBJECT_TYPE = B.SEGMENT_TYPE AND
A.OWNER = '&OWN' AND
A.OBJECT_NAME in (replace('&OBJNM',' ',''','''))
ORDER BY 1,2,3,4,5,6;
======================================================================

オブジェクトを1つのみ記載し実行すると正常に返ってくるのですが(実際には変換が実行されていない)、2つ以上記載すると返ってきません。
ただ、SELECTなどで確認すると、スペースは「','」にきちんと変換されていることが確認できます。
念の為、
owner in (''''||replace('TEST TEST001',' ',''',''')||'''')
なども試してみたのですが、ダメでした。

すみませんが、ご存知の方がいらっしゃいましたら、原因と対策を教えていただけますでしょうか。
  • 1. Re: replace関数について
    Blueloco Explorer
    Currently Being Moderated
    「'TEST1', 'TEST2'」の全体で1つの文字列として解釈されてしまうので、「'TEST1', 'TEST2'」と一致するものしか選択されません。
    つまり、カンマを複数の文字列の区切りとして解釈させることはできません。

    数が2個と決まっているなら、

    in ('&OBJNM1', '&OBJNM2')

    などとして別々に入力する必要がありますし、数が決まってないならSQL文全体を文字列とし、動的SQL文にするしかありません。
    動的SQL文を使うにはPL/SQLなどのプログラム(無名のPL/SQLブロックやストアド等)にする必要がありますね。
  • 2. Re: replace関数について
    506331 Newbie
    Currently Being Moderated
    SQL*Plusの置換変数による置換は、SQLを解析する前に実行されます。この時点では、SQL文をただの文字列と同様にどうとでも加工できます。
    A in (&XXX)に対して、XXXに「'A','B'」を入れた場合、A in ('A','B')というSQL文になります。
    この、SQL*Plusの置換変数の置き換えは割と特殊なケースなので、注意が必要です(通常は、バインド変数/ホスト変数しかない)

    A in (replace('&OBJNM',' ',''','''))と書いて、「A B」を渡した場合、A in (XXX)のXXXが、「'A','B'」という値になるだけで、「inの()内の値は1つ」という構造は変わりません(すでに構造の解析する処理を過ぎている)。

    やるのなら、SQL*Plusの置換変数の中に求める値を作り出す必要があります。
    -----
    column sep_objname new_value sepobjnm noprint
    select replace('&OBJNM',' ',''',''') as sep_objname from dual;

    select ~
    A.OBJECT_NAME in (&&sepobjnm)

    -----
    みたいな感じになるのでないでしょうか(未検証)
  • 3. Re: replace関数について
    tmtsmic Explorer
    Currently Being Moderated
    こんにちは。
    以下、weykさんのアドバイスを検証しただけですが・・・
    user@ora 10gR2>column sep_objname new_value sepobjnm noprint
    user@ora 10gR2>SELECT REPLACE('&objnm',' ',''',''') AS SEP_OBJNAME FROM DUAL;
    objnmに値を入力してください: ほげ10 ほげ30
    旧   1: SELECT REPLACE('&objnm',' ',''',''') AS SEP_OBJNAME FROM DUAL
    新   1: SELECT REPLACE('ほげ10 ほげ30',' ',''',''') AS SEP_OBJNAME FROM DUAL
    
    
    user@ora 10gR2>WITH TEST AS (
    2  SELECT 10 AS CD, 'ほげ10' AS NM FROM DUAL
    3  UNION ALL
    4  SELECT 20 AS CD, 'ほげ20' AS NM FROM DUAL
    5  UNION ALL
    6  SELECT 30 AS CD, 'ほげ30' AS NM FROM DUAL)
    7  SELECT * FROM TEST
    8  WHERE NM IN ('&&sepobjnm');
    旧   8: WHERE NM IN ('&&sepobjnm')
    新   8: WHERE NM IN ('ほげ10','ほげ30')
    
            CD NM
    __________ ________________
            10 ほげ10
            30 ほげ30
    (&&sepobjnm) ⇒ ('&&sepobjnm')
    シングルで括った方がいいですね。
  • 4. Re: replace関数について
    979676 Newbie
    Currently Being Moderated
    皆様

    お忙しいところ早速ご連絡をくださり、ありがとうございます。
    シングルクォーテーションとカンマがあっても、解析処理が既に終わっているため、1つの文字列と認識しているとのこと、とても勉強になりました。
    皆様に教えていただきました方法で実施したところ、無事欲しい結果を得ることができました。

    助けてくださり、本当にありがとうございました!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points