⇧ amazing...
Oracle DatabaseにはCREATE TABLE IF NOT EXISTSが存在しない
テーブルの存在確認をしてからテーブルを作成することができる、
CREATE TABLE IF NOT EXSITS
⇧ のSQL文が、無料で利用できるMySQLにはあるのに、何故か有償のOracle Database には無いという...
何が困るって、
存在するテーブルを作成しようとすると、
SQL : ORA-00955: すでに使用されているオブジェクト名です。
⇧ エラーになるし、存在しないテーブルを削除しようとすると、
SQL : ORA-00942: 表またはビューが存在しません。
⇧ エラーになる...
だから、テーブルの存在チェックしたいのに、用意されてないという...
Oracle Database の仕様、残念過ぎるんだけど...
⇧ 上記サイト様が紹介してくださっているように、かなり面倒くさい。
実際の開発現場では、事前に、削除して良いテーブルかどうかは要確認ですかね。
そして、見事にハマったのが、Oracle Database 12cから、
マルチテナント・アーキテクチャを使用すると、Oracle Databaseをマルチテナント・コンテナ・データベース(CDB)として機能させることができます。
https://docs.oracle.com/cd/F32587_01/cncpt/CDBs-and-PDBs.html
⇧ となっていて、デフォルトでCDBが有効になってる気がする。
なので、普通に何も考えずに、sqlplusコマンドで、
sqlplus / as sysdba
⇧ とか実施すると、テーブルのあるPDBではなく、CDBに接続されてしまうので、テーブルが見つからんとか怒られるという...
というわけで、
SET SERVEROUTPUT ON;
DECLARE
-- 削除対象のテーブル
CURSOR table_list IS
SELECT
a.table_name
FROM
all_tables a
WHERE
a.owner = 'TS0818' -- OWNERを指定
AND a.table_name IN ( -- テーブルを指定
'BK_ADDRESS_DETAIL'
,'BK_AUTH_INFOMATION'
,'BK_CATEGORY'
,'BK_CATEGORY_DETAIL'
,'BK_ORDER_DETAIL'
,'BK_ORDERS'
,'BK_ORDER_STATUS'
,'BK_PRODUCT'
,'BK_PRODUCT_DETAIL'
,'BK_PURCHASEORDER'
,'BK_USER_DETAIL'
,'BK_USER_LEAVE'
,'BK_USERS'
)
ORDER BY
a.table_name;
version VARCHAR2(2);
BEGIN
-- Oracleのバージョンを取得
DBMS_OUTPUT.PUT_LINE('Oracleのバージョンを確認...');
SELECT
REPLACE(SUBSTR(version ,0,2) ,'.' ,'') AS major_version
INTO version
FROM
product_component_version
WHERE
LOWER(product) LIKE 'oracle%';
DBMS_OUTPUT.PUT_LINE('バージョンは ' || version);
-- 削除処理開始
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> START');
FOR vRec IN table_list LOOP
DBMS_OUTPUT.PUT_LINE('DROP TABLE ' || vRec.table_name || ' CASCADE CONSTRAINTS'); -- DDL確認用：DROP
-- Oracleのバージョンが10g以降⇒PURGEでテーブルを完全削除
IF TO_NUMBER(version) >= 10 THEN
DBMS_OUTPUT.PUT_LINE('PURGE TABLE ' || vRec.table_name); -- DDL確認用：PURGE
-- EXECUTE IMMEDIATE 'PURGE TABLE ' || vRec.table_name;
END IF;
-- EXECUTE IMMEDIATE 'DROP TABLE ' || vRec.table_name || ' CASCADE CONSTRAINTS';
END LOOP;
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> END');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('----エラー発生！----------------');
DBMS_OUTPUT.PUT_LINE('--SQLERRM:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('--SQLCODE:'||SQLCODE);
END;
/
で、削除がされんかったのよね…
よく確認してみたら、削除処理の部分がコメントアウトされてました...
コメントアウトを解除で、実行してみました。
DECLARE
-- 削除対象のテーブル
CURSOR table_list IS
SELECT
a.table_name
FROM
all_tables a
WHERE
a.owner = 'TS0818' -- OWNERを指定
AND a.table_name IN ( -- テーブルを指定
'BK_ADDRESS_DETAIL'
,'BK_AUTH_INFOMATION'
,'BK_CATEGORY'
,'BK_CATEGORY_DETAIL'
,'BK_ORDER_DETAIL'
,'BK_ORDERS'
,'BK_ORDER_STATUS'
,'BK_PRODUCT'
,'BK_PRODUCT_DETAIL'
,'BK_PURCHASEORDER'
,'BK_USER_DETAIL'
,'BK_USER_LEAVE'
,'BK_USERS'
)
ORDER BY
a.table_name;
version VARCHAR2(2);
BEGIN
-- Oracleのバージョンを取得
DBMS_OUTPUT.PUT_LINE('Oracleのバージョンを確認...');
SELECT
REPLACE(SUBSTR(version ,0,2) ,'.' ,'') AS major_version
INTO version
FROM
product_component_version
WHERE
LOWER(product) LIKE 'oracle%';
DBMS_OUTPUT.PUT_LINE('バージョンは ' || version);
-- 削除処理開始
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> START');
FOR vRec IN table_list LOOP
DBMS_OUTPUT.PUT_LINE('DROP TABLE ' || vRec.table_name || ' CASCADE CONSTRAINTS'); -- DDL確認用：DROP
-- Oracleのバージョンが10g以降⇒PURGEでテーブルを完全削除
IF TO_NUMBER(version) >= 10 THEN
DBMS_OUTPUT.PUT_LINE('PURGE TABLE ' || vRec.table_name); -- DDL確認用：PURGE
EXECUTE IMMEDIATE 'PURGE TABLE ' || vRec.table_name;
ELSE
EXECUTE IMMEDIATE 'DROP TABLE ' || vRec.table_name || ' CASCADE CONSTRAINTS';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> END');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('----エラー発生！----------------');
DBMS_OUTPUT.PUT_LINE('--SQLERRM:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('--SQLCODE:'||SQLCODE);
END;
/
エラーになってしまった...
⇧ 上記サイト様によりますと、スキーマ名が必要だった模様。
あと、
⇧ PURGEはあくまで、DROPした後の話であるっぽい、つまり、Oracle Databaseのバージョンに関係なく、テーブル削除にはDROPを使えってことみたい。
DROP後に完全に削除するのに、PURGEを使うってことかと。
SET SERVEROUTPUT ON;
DECLARE
-- 削除対象のテーブル
CURSOR table_list IS
SELECT
a.table_name, a.owner
FROM
dba_tables a
WHERE
a.owner = 'TS0818' -- OWNERを指定
AND a.table_name IN ( -- テーブルを指定
'BK_ADDRESS_DETAIL'
,'BK_AUTH_INFOMATION'
,'BK_CATEGORY'
,'BK_CATEGORY_DETAIL'
,'BK_ORDER_DETAIL'
,'BK_ORDERS'
,'BK_ORDER_STATUS'
,'BK_PRODUCT'
,'BK_PRODUCT_DETAIL'
,'BK_PURCHASEORDER'
,'BK_USER_DETAIL'
,'BK_USER_LEAVE'
,'BK_USERS'
)
ORDER BY
a.table_name;
version VARCHAR2(2);
BEGIN
-- Oracleのバージョンを取得
DBMS_OUTPUT.PUT_LINE('Oracleのバージョンを確認...');
SELECT
REPLACE(SUBSTR(version ,0,2) ,'.' ,'') AS major_version
INTO version
FROM
product_component_version
WHERE
LOWER(product) LIKE 'oracle%';
DBMS_OUTPUT.PUT_LINE('バージョンは ' || version);
-- 削除処理開始
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> START');
FOR vRec IN table_list LOOP
DBMS_OUTPUT.PUT_LINE('削除対象テーブル：' || vRec.owner || '.' || vRec.table_name);
-- DDL確認用：DROP
-- Oracleのバージョンが10g以降⇒PURGEでテーブルを完全削除
IF TO_NUMBER(version) >= 10 THEN
DBMS_OUTPUT.PUT_LINE('PURGE TABLE ' || vRec.owner || '.' || vRec.table_name);
-- DDL確認用：PURGE
EXECUTE IMMEDIATE 'DROP TABLE ' || vRec.owner || '.' || vRec.table_name || ' CASCADE CONSTRAINTS';
EXECUTE IMMEDIATE 'PURGE TABLE ' || vRec.owner || '.' || vRec.table_name;
ELSE
DBMS_OUTPUT.PUT_LINE('DROP TABLE ' || vRec.owner || '.' || vRec.table_name);
EXECUTE IMMEDIATE 'DROP TABLE ' || vRec.owner || '.' || vRec.table_name || ' CASCADE CONSTRAINTS';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> END');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('----エラー発生！----------------');
DBMS_OUTPUT.PUT_LINE('--SQLERRM:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('--SQLCODE:'||SQLCODE);
END;
/
⇧ 上記で削除できました。
それにしても、Oracle Databaseは何かと使い勝手が宜しくないように感じてしまうのだが...
毎度モヤモヤ感が半端ない...
今回はこのへんで。
