如何创建Oracle SQL脚本假脱机文件 [英] How to create a oracle sql script spool file

问题描述

我有一个关于假脱机程序结果的问题.我的示例sql脚本如下所示.

I have a question about spooling the the results of my program. My sample sql script looks like this.

  whenever sqlerror exit failure rollback
  set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

 declare
 ab varchar2(10) := 'Raj';
 cd varchar2(10);
 a number := 10;
 c number;
 d number;
 begin
 c := a+10;
 select ab,c into cd,d from dual;
 end;

 SPOOL 
 select cd,d from dual;
 SPOOL OFF
 EXIT;

上面的脚本不起作用，但是我想做这样的事情，其中​​在begin end块中我们计算一些值，并且我要假脱机处理那些结果.

The above script does not work, but I want to do something like this where in the begin end block we compute some values and i want to spool those results.

谢谢.

推荐答案

这会将匿名块的输出假脱机到位于本地PC C:驱动器根目录中的名为output_<YYYYMMDD>.txt的文件中，其中<YYYYMMDD>是当前日期:

This will spool the output from the anonymous block into a file called output_<YYYYMMDD>.txt located in the root of the local PC C: drive where <YYYYMMDD> is the current date:

SET SERVEROUTPUT ON FORMAT WRAPPED
SET VERIFY OFF

SET FEEDBACK OFF
SET TERMOUT OFF

column date_column new_value today_var
select to_char(sysdate, 'yyyymmdd') date_column
  from dual
/
DBMS_OUTPUT.ENABLE(1000000);

SPOOL C:\output_&today_var..txt

DECLARE
   ab varchar2(10) := 'Raj';
   cd varchar2(10);
   a  number := 10;
   c  number;
   d  number; 
BEGIN
   c := a+10;
   --
   SELECT ab, c 
     INTO cd, d 
     FROM dual;
   --
   DBMS_OUTPUT.put_line('cd: '||cd);
   DBMS_OUTPUT.put_line('d: '||d);
END; 

SPOOL OFF

SET TERMOUT ON
SET FEEDBACK ON
SET VERIFY ON

PROMPT
PROMPT Done, please see file C:\output_&today_var..txt
PROMPT

希望有帮助...

在为每个游标的迭代输出一个值后(我意识到在此示例中每个值都将是相同的，但是您应该了解我在做什么的要点)后发表评论:

After your comment to output a value for every iteration of a cursor (I realise each value will be the same in this example but you should get the gist of what i'm doing):

BEGIN
   c := a+10;
   --
   FOR i IN 1 .. 10
   LOOP
      c := a+10;
      -- Output the value of C
      DBMS_OUTPUT.put_line('c: '||c);
   END LOOP;
   --
END; 

这篇关于如何创建Oracle SQL脚本假脱机文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！