[20190531]如何实现与执行.txt

[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测试就清楚了.