How to create a oracle sql script spool file

Question

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    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   Thanks

User · Answer

To spool from a BEGIN END block is pretty simple  For example if you need to spool result from two tables into a file  then just use the for loop  Sample code is given below   BEGIN  FOR x IN        SELECT COLUMN1 COLUMN2 FROM TABLE1     UNION ALL     SELECT COLUMN1 COLUMN2 FROM TABLEB       LOOP     dbms output put line x COLUMN1           x COLUMN2   END LOOP   END

User · Answer

In order to execute a spool file in plsql Go to File- New- command window -  paste your code-  execute  Got to the directory and u will find the file

User · Answer

This will spool the output from the anonymous block into a file called output  lt YYYYMMDD gt  txt located in the root of the local PC C  drive where  lt YYYYMMDD gt  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  amp 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  amp today var  txt PROMPT   Hope it helps     EDIT   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

User · Answer

With spool     set heading off   set arraysize 1   set newpage 0   set pages 0   set feedback off   set echo off   set verify off  variable cd varchar2 10   variable d number    declare  ab varchar2 10      Raj    a number    10   c number   begin  c    a 10   select ab c into  cd  d from dual   end    SPOOL   select  cd  d from dual   SPOOL OFF  EXIT

[oracle] How to create a oracle sql script spool file

Examples related to oracle

Examples related to plsql

Examples related to sql-scripts

Examples related to spool