使用動態SQL克隆數據庫對象

　　首先給出一個警告下面的技巧提示如果使用不小心會造成嚴重的安全漏洞當你在一個非測試環境下使用這種方法之前一定要完完全全地理解這樣做的後果
　　
　　如果你需要復制一個表並在（另一個用戶名下的）另一個方案（schema）制作它的一個副本那麼你可以使用 SQL*Plus 的 COPY 命令或者使用簡單的語句create table foo as select * from otherfoo;只要你在另一個用戶的表上有足夠的 select 權限
　　
　　然而如果你需要對任何其它數據庫對象做同樣的事情比如包過程函數或視圖就沒有這麼簡單的命令了你需要手工找出其代碼並在新實例中運行它如果能夠簡單克隆對象並讓另一個用戶的對象出現在自己的實例中那將是一件非常值得高興的事對於為開發過程創建測試方案來說這一點非常有用在這個例子中我將創建一個包使用該包可以對大多數對象進行克隆（具有一些限制）
　　
　　要處理的主要問題是獲得源代碼對象所有者通過視圖USER_SOURCE 可以訪問源代碼而對於其它用戶如果對象被授予了EXECUTE 權限給一個用戶那麼這個用戶就只能看到通過視圖ALL_SOURCE 選出的源代碼我們可以通過一個過程來封裝give me the source for your object（給我你的對象的源代碼）請求
　　
　　create or replace procedure get_source
　　(
　　p_type　　　　　varchar
　　p_name　　　　　varchar
　　p_cursor out　　sys_refcursor
　　)
　　as
　　begin
　　open p_cursor for
　　select text from user_source
　　where type = upper(p_type) and name = p_name
　　order by line;
　　end get_source;
　　/
　　show errors;
　　
　　注意我沒有用UPPER(name)這就意味著你必須匹配這個存儲過程的字母大小寫Java 存儲過程使用很多大小寫混合的名字
　　
　　如果這個過程是由對象所有者所有的那麼那個對象的源代碼就可以通過一個 REF CURSOR 變量導出如果這個過程被授予了其他用戶 EXECUTE 權限那麼這個用戶將能夠調用這個過程並查看任何數據庫對象的源代碼——即使是那些沒有授權給他們的對象和那些在 ALL_SOURCE 中不給出的對象比如 TYPE 聲明為了說明這種方法可行請嘗試在 SQL*Plus 中輸入以下代碼
　　
　　SQL> connect scott/tiger
　　SQL> create function foo return varchar as begin return hello world; end;
　　SQL> /
　　SQL> @get_sourcesql
　　SQL> variable c refcursor;
　　SQL> exec get_source(FUNCTIONFOO:c);
　　SQL> print c
　　
　　有了從對象所有者手中得到的源代碼調用者就可以在創建自己的方案中創建對象了我們需要動態 SQL 來從文本字符串構建對象另外一個需要處理的問題是一些數據庫對象的源代碼的長度可能會超過個字符即超過 VARCHAR 字符串的最大長度限制這樣就不能使用簡單的 VARCHAR 字符串來保存 SQL在 Oracle 中有一個很少使用的變量DBMS_SQLPARSE可以使用它將源代碼存儲為一個由 VARCHAR 字符行所組成的表中這樣的表可以用來存儲超過長度限制的 SQL（在實際的應用中你可能還需要將任何大於個字符的代碼行包裝起來因為USER_SOURCE 最多只能存儲行字符）下面將其實現為一個帶有命令行參數的 SQL*Plus 腳本的代碼
　　
　　declare
　　ipls_integer := ;
　　l_source dbms_sqlvarchars;
　　l_line varchar();
　　l_cursorsys_refcursor;
　　c pls_integer;
　　r pls_integer;
　　begin
　　&get_source(&&l_cursor);
　　l_source(i) := create or replace;
　　loop
　　fetch l_cursor into l_line;
　　exit when l_cursor%notfound;
　　i := i + ;
　　l_source(i) := l_linetext;
　　end loop;
　　close l_cursor;
　　if i = then
　　raise_application_error(object does not exist);
　　end if;
　　c := dbms_sqlopen_cursor;
　　dbms_sqlparse(cl_sourceunttruedbms_sqlnative);
　　dbms_sqlclose_cursor(c);
　　end;
　　/
　　show errors;
　　
　　舉個例子假設一個方案需要克隆 SCOTT 的方案中的FOO的函數SCOTT 將擁有 CLONER 的一個副本並將 EXECUTE 權限授予允許克隆 SCOTT 的對象的用戶其它的用戶可以發出以下 SQL*Plus 命令
　　
　　SQL> connect ANOTHER USER
　　SQL> @clone SCOTT FUNCTION FOO
　　
　　這種做法可行但是依然需要 SQL*Plus 會話和腳本我想將所有東西都放在 SQL 中以使得任何應用程序都可以執行這一功能為了實現這一想法我們需要將前面的 SQL*Plus 腳本包裝成另外一個動態 SQL 語句在這個語句中我們可以加上所有者的名稱並將所有者和類型參數組合可以用以下過程來實現
　　
　　create or replace procedure clone_obj
　　(
　　p_owner　　　　 varchar
　　p_type　　　　　varchar
　　p_name　　　　　varchar
　　)
　　authidcurrent_user
　　is
　　lf　　　　　char := chr();
　　begin
　　execute immediate
　　declare || lf
　　|| 　　ipls_integer := ; || lf
　　|| 　　l_source dbms_sqlvarchars; || lf
　　|| 　　l_line varchar(); || lf
　　|| 　　l_cursorsys_refcursor; || lf
　　|| 　　c pls_integer; || lf
　　|| 　　r pls_integer; || lf
　　|| begin || lf
　　|| 　　||p_owner||get_source(::l_cursor); || lf
　　|| 　　l_source(i) := create or replace; || lf
　　|| 　　loop || lf
　　|| 　　　　fetch l_cursor into l_line; || lf
　　|| 　　　　exit when l_cursor%notfound; || lf
　　|| 　　　　i := i + ; || lf
　　|| 　　　　l_source(i) := l_line; || lf
　　|| 　　end loop; || lf
　　|| 　　close l_cursor; || lf
　　|| 　　if i = then || lf
　　|| 　　　　raise_application_error(
　　|| object does not exist); || lf
　　|| 　　end if; || lf
　　|| 　　c := dbms_sqlopen_cursor; || lf
　　|| 　　dbms_sqlparse(cl_sourceunt
　　|| truedbms_sqlnative); || lf
　　|| 　　dbms_sqlclose_cursor(c); || lf
　　|| end; || lf
　　using p_typep_name;
　　end clone_obj;
　　/
　　show errors;
　　
　　注意使過程具有足夠的權限來創建數據庫對象我必須添加AUTHID CURRENT_USER現在你可以用任何能夠調用 Oracle 存儲過程的產品來調用這個過程下面這個例子與前面的例子相同只不過這個例子是寫在 SQL*Plus 中的
　　
　　SQL> @clone_obj
　　SQL> exec clone_obj(SCOTTFUNCTIONFOO);
　　
　　在這裡會有一些安全問題但是不多只有被授予對 GET_SOURCE 有 EXECUTE 權限的用戶才能讀取他們常規情況下無法看到的對象的源代碼在理想情況下你可以創建一個只包含GET_SOURCE和一組模板對象的用戶
　　
　　上面的程序還不完整但是還是可以作為一個例子來用的除了需要將個字符的源代碼包裝成個字符長的目標行之外可能還需要對其進行擴展以掃描對象的名稱並插入一個所有者名稱以使得 DBA 所有者能夠將對象從一個用戶克隆到其他用戶
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22241.html