[20190531]如何實現與執行.txt

[20190531]如何實現與執行.txt
--//連結
--//我的提問：
How to realize the execution?
SQL> select * from user_scheduler_jobs
2 @pr

--//實在搞不懂如何實現這樣的功能?以前也在itpub上問過,連結如下:
--//http://www.itpub.net/thread-2108426-1-1.html

--//實際上Here is the pr.sql script. It came from Tanel Poder via an AskTOM original
--//原來在Tanel Poder的網站就有.

--//修改程式碼如下,注意開頭的點可不是多餘的.注意_pr_tmpfile指向的目錄必須存在.
--//實際上就是利用sqlplus的編輯功能插入sql語句到中間。注意_pr_tmpfile=d:\tmp\pr.out目錄一定要存在.

===========prxx.sql
.

set termout off
def _pr_tmpfile=d:\tmp\pr.out

store set &_pr_tmpfile.set replace
set termout on

set serverout on size 1000000 termout off echo off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==============================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      ( rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

set termout off
@&_pr_tmpfile.set

get &_pr_tmpfile nolist
host del &_pr_tmpfile
set termout on
===========

--//方法1:
select * from v$database ;
--//首先執行1次,然後執行
@ prxx

--//方法2:
select * from v$database
@ prxx

--//另外在家裡12c上測試遇到1個問題,順便做一個記錄:

SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

SCOTT@test01p> show release
release 1202000100

SCOTT@test01p> select * from emp where rownum<=12
  2  @ prxx
PL/SQL procedure successfully completed.
--//沒有輸出，不大可能是dbms_output輸出快取不足.

SCOTT@test01p> select * from emp where rownum<=11
  2  @prxx
=============
EMPNO       : 7369
ENAME       : SMITH
JOB         : CLERK
MGR         : 7902
HIREDATE    : 1980-12-17 00:00:00
SAL         : 800
COMM        :
DEPTNO      : 20
...
============
EMPNO       : 7876
ENAME       : ADAMS
JOB         : CLERK
MGR         : 7788
HIREDATE    : 1987-05-23 00:00:00
SAL         : 1100
COMM        :
DEPTNO      : 20

PL/SQL procedure successfully completed.
--//我的測試輸出僅僅小於等於100行有輸出.
SYS@test> select rownum from dual connect by level<=50
@ prxx
--//有輸出.
--//如果註解====那行的輸出
999999          dbms_output.put_line( '=================' );;
為
999999          --dbms_output.put_line( '=================' );;

SYS@test> select rownum from dual connect by level<=101
  2  @ prxx
PL/SQL procedure successfully completed.

--//沒有輸出,如果level<=100有輸出.我估計是bug.上班在11g的sqlplus測試就清楚了.