Search code examples
sqloracleplsqlsqlplusoracle-ebs

Write to Oracle concurrent request output / log from a SQLPlus program

I have an Oracle concurrent request that calls a SQLPlus program. The program itself is working correctly, but I would like to add some logging information to the concurrent request output / log in EBS.

I have tried a number of variations of:

set heading off
--set pagesize 0 embedded on
set pagesize 50000
set linesize 32767
set feedback off
set verify off
set term off
set echo off
set newpage none
set serveroutput on

dbms_output.enable(1000000);

--prepare data
EXECUTE program (&1,&2,&3,&4,&5);

--extract data
@"path/file.SQL";

fnd_file.put_line(FND_FILE.LOG,'do some logging here');
fnd_file.put_line(FND_FILE.OUTPUT,'do some logging here');

/

But everything I've tried so far results with either

  • no logging added to request output or log
  • no request output whatsoever
  • errors like: SP2-0734: unknown command beginning "dbms_outpu..." - rest of line ignored. and PLS-00103: Encountered the symbol "ENABLE" when expecting one of the following: := . ( @ % ;

Is it possible to write to the request output or log from a SQLPlus script that is called from concurrent manager?

Solution

  • I have resolved this problem. The solution is incredibly simple - and now I'm bent out of shape because it took so long to realize.

    Step 1 - SET ECHO ON

    Step 2 - PROMPT whatever you want written to concurrent request output

    The following sample writes 'Output is written to this folder' to the concurrent request output.

    set heading off
--set pagesize 0 embedded on
set pagesize 50000
set linesize 32767
set feedback off
set verify off
set term off
set echo on
set newpage none
set serveroutput on

prompt Output is written to this folder

--prepare data
EXECUTE program (&1,&2,&3,&4,&5);

--extract data
@"path/file.SQL";

/

    This is exactly what I was looking for. Maybe this will be useful to someone in another galaxy.