This content has been marked as final. Show 6 replies
it works for me
[oracle@localhost ~]$ sqlplus user1/user1 SQL*Plus: Release 220.127.116.11.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 18.104.22.168.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>
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.
So maybe I can not do dbms_output.put_line in a batch? However I could do sql and save it the information.
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.
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.
/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
hh**** wrote: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.
I want to run some plsql in a batch job.
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.