[20190531]如何实现与执行.txt
--//链接 https://connor-mcdonald.com/2019/05/28/dbms_job-the-joy-of-transactions/
--//我的提问：
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测试就清楚了.