6 Replies Latest reply: Nov 13, 2012 1:08 PM by Billy~Verreynne RSS

    Can you perform plsql in a batch job.  I am having problems with sample.

    hh*394375*91
      I want to run some plsql in a batch job. My question is can I do this and output the results

      The following code does not error.
      BEGIN AND END display.

      However myTest is not outputed. I am testing this because I want to do some counts and updates
      using this process.

      SET TERMOUT OFF
      SET LINESIZE 100
      SET ECHO OFF
      SET FEEDBACK OFF
      SET HEADING OFF
      SET VERIFY OFF
      SPOOL &&3

      SELECT 'BEGIN' from dual;

      declare
      myTest varchar2(11) := 'Hello World';

      begin
      dbms_output.put_line(myTest);

      end;
      /
      SELECT 'END' from dual;

      SPOOL OFF
      SET TERMOUT ON

      Quit



      Howard
        • 1. Re: Can you perform plsql in a batch job.  I am having problems with sample.
          AlbertoFaenza
          Hi,

          Try setting
          SET SERVEROUTPUT ON SIZE UNLIMITED
          Regards.
          Al
          • 2. Re: Can you perform plsql in a batch job.  I am having problems with sample.
            sb92075
            it works for me
            [oracle@localhost ~]$ sqlplus user1/user1
            
            SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 13 10:08:17 2012
            
            Copyright (c) 1982, 2010, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            
            SQL> set serveroutput on
            SQL> 
            SET TERMOUT OFF
            SET LINESIZE 100
            SET ECHO OFF
            SET FEEDBACK OFF
            SET HEADING OFF
            SET VERIFY OFF
            SPOOL &&3
            
            SELECT 'BEGIN' from dual;
            
            declare
            myTest varchar2(11) := 'Hello World';
            begin
            dbms_output.put_line(myTest);
            
            end;
            /
            SELECT 'END' from dual;
            
            SPOOL OFF
            SET TERMOUT ON
            
            QuitSQL> SQL> SQL> SQL> SQL> SQL> SQL> Enter value for 3: not spooling currently
            SQL> 
            BEGIN
            SQL> SQL>   2    3    4    5    6    7  Hello World
            SQL> 
            END
            SQL> SQL> not spooling currently
            SQL> SQL> SQL> 
            • 3. Re: Can you perform plsql in a batch job.  I am having problems with sample.
              hh*394375*91
              It outputs it to the screen if I run it usinig sql developer

              However when I run it doing the following from the command prompt.

              @echo start backup of tables
              sqlplus %1/%2 @getcomments test ID C:\MYTEST

              @echo end of job
              @echo you done


              It only outputs the following to the MYTEST file.

              BEGIN

              END

              So maybe I can not do dbms_output.put_line in a batch? However I could do sql and save it the information.

              Howard
              • 4. Re: Can you perform plsql in a batch job.  I am having problems with sample.
                sb92075
                having DBMS_OUTPUT inside a batch job is meaningless.
                • 5. Re: Can you perform plsql in a batch job.  I am having problems with sample.
                  John Spencer
                  I'm not sure what you are trying to do exactly, but it looks like you may be trying to generate a script of some sort. You can certainly call PL/SQL blocks in a script, and generate the output to a file, but you would need to run the file you generated in sqlplus, perhaps at the end of the batch file.
                  /export/home/oracle> cat t.sql
                  set serveroutput on
                  SET TERMOUT OFF
                  SET LINESIZE 100
                  SET ECHO OFF
                  SET FEEDBACK OFF
                  SET HEADING OFF
                  SET VERIFY OFF
                  SPOOL &&1
                  SELECT 'BEGIN' from dual;
                  declare
                  myTest varchar2(11) := 'Hello World';
                  begin
                  dbms_output.put_line(myTest);
                  end;
                  /
                  SELECT 'END' from dual;
                  SPOOL OFF
                  exit
                  
                  /export/home/oracle> sqlplus -s / @t.sql t.txt
                  /export/home/oracle> cat t.txt
                  
                  BEGIN
                  Hello World
                  
                  END
                  If you wanted to execute the contents of my t.txt after generating it, then you could add a line at the end of the sql script (after the spool off) like @&&1 to run it.

                  John
                  • 6. Re: Can you perform plsql in a batch job.  I am having problems with sample.
                    Billy~Verreynne
                    hh**** wrote:
                    I want to run some plsql in a batch job.
                    Then consider doing it properly. As stored procedure code residing inside the database, that is scheduled for execution by the database, executes inside the database, and logs output data to database tables.

                    And not implement a poor hack outside the database using SQL*Plus.

                    Create a PL/SQL procedure or package to execute the desired steps. Use a table for logging output, run times, process stats and so on. Schedule that code as a job using either DBMS_JOB, or the more sophisticated DBMS_SCHEDULER.

                    Minimal moving parts. No external dependencies.