テーブルに登録されているデータをSELECTしてINSERT文を作りたい。

Question

前提・実現したいこと

いつも参考にさせていただいています。

現在、既存のテーブル(例としてUSERテーブル)に登録されているデータを
SELECT文で取得し、INSERT文として出力しようとしています。

VARCHAR2型の項目のデータがNULLの場合は、NVLを利用することで出力される
INSERT文の該当箇所に「NULL」と出力することができているのですが、
NUMBER型の場合にNVLで「NULL」を出力しようとするとエラーが発生し、処理がうまく動きません。

どのようにすればNUMBER型項目のところにNULLを出力することができるのでしょうか。

現在作業している環境ではツール類の導入が禁止されているため、利用できるのはSQL*Plusとメモ帳のみです。

SQLDeveloperなどのツールを使用してINSERT文をエクスポート出力すればNUMBER型の項目にもNULLと出力されているので、SQLでも同様のことがしたいと考えています。

ご教授のほど、よろしくお願いいたします。

発生している問題・エラーメッセージ

ORA-01722: 数値が無効です。

該当のソースコード

select 
'INSERT INTO USER VALUES('
 || NVL(COMPANYID, 'NULL') || ','
 || NVL(USERID, 'NULL') || ','
 || NVL(AGE, 'NULL') || ','    /* ここの「NULL」でエラーが発生 */
 || 'SYSDATE' || ','
 || ');'
from USER

試したこと

〇NUMBER型項目のNVLの第2引数を「NULL(シングルクォートなし)」に変更

select 
'INSERT INTO USER VALUES('
 || NVL(COMPANYID, 'NULL') || ','
 || NVL(USERID, 'NULL') || ','
 || NVL(AGE, NULL) || ','   /* 'NULL' → NULL　に変更 */
 || 'SYSDATE' || ','
 || ');'
from USER

結果：INSERT文は取得できるが、「AGE」の部分に何も出力されないため、このままではSQL実行時にエラーとなる。

INSERT INTO USER VALUES('1','0000001',,SYSDATE);

補足情報（FW/ツールのバージョンなど）

Windows 10 EnterPrise
Oracle Database 19c

Comments

[m.ts10806]

見た感じAGE以外も「NULLという文字列」入れようとしてるように見えますが、そもそも0じゃダメなんでしょうか。

[kekosh]

ご覧いただきありがとうございます。

＞見た感じAGE以外も「NULLという文字列」入れようとしてるように見えますが、そもそも0じゃダメなんでしょうか。

AGE以外の項目はVARCHAR2型なので、DB上NULLの場合はNULL文字列を出力しています。

AGEについて、カラム定義でNOT NULL制約をつけてあればよかったのですが、現状NOT NULL制約がついていない、またつけることは許されていません。
そのためDB上NULLとなっている場合は、出力するInsert文に「NULL」を出力したいと考えています。

Answer 1

NVLではなくNVL2を利用すればよいと思います。

select 
'INSERT INTO USER VALUES('
 || NVL(COMPANYID, 'NULL') || ','
 || NVL(USERID, 'NULL') || ','
 || NVL2(AGE, TO_CHAR(AGE), 'NULL') || ',' 
 || 'SYSDATE' || ','
 || ');'
from USER

Comments

[kekosh]

やりたかった形でデータを出力することができました。
ありがとうございました。