[20170726]11G 12c expand sql text 2.txt
--//原来写的脚本只能分别在11g,12c单独使用.上午花一点点时间.把两者整合起来.
--//讨论链接:http://www.itpub.net/thread-2088981-1-1.html
--//感谢solomon_007的指点,通过建立动态sql语句来实现.链接:http://blog.itpub.net/267265/viewspace-2141010/
--//qqjue给出建立建立类似c的宏来实现Conditional PL/SQL compilation,实际上这个也是我原来的想法.
--//重新写一个看看.
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ cat expand_sql_text2.sql
SET LONG 20000
SET SERVEROUTPUT ON
DECLARE
L_sqltext CLOB := NULL;
l_result CLOB := NULL;
BEGIN
SELECT sql_fulltext
INTO l_sqltext
FROM v$sqlarea
WHERE sql_id = '&&1';
$IF DBMS_DB_VERSION.VER_LE_11_2
$THEN
dbms_sql2.expand_sql_text (l_sqltext, l_result);
$ELSIF DBMS_DB_VERSION.VER_LE_12_1
$THEN
DBMS_UTILITY.expand_sql_text (l_sqltext, l_result);
$ELSIF DBMS_DB_VERSION.VER_LE_12_2
$THEN
DBMS_UTILITY.expand_sql_text (l_sqltext, l_result);
$END
DBMS_OUTPUT.put_line (l_result);
END;
/
SET SERVEROUTPUT OFF
--//测试通过!!做一个记录.
