yes, someone has a dbms_output.enable in there somewhere.

Turn on SQL_TRACE and trace a session -- you might find it there.

there must be a dbms_output.enable - else this would not happen. something is turning it on.

as an Oracle job? or as a "cron job"

total background process to me means "snp0" or "j001" as in totally an oracle background process. and job means dbms_job. I've a feeling that you mean "we have a sqlplus script we run in the background and it is our "JOB"


If this is not "dbms_job", then someone has put "set server output on" in

a) a login.sql in the current working directory of your "job"
b) a login.sql in the $SQLPATH path
c) the glogin.sql file ($OH/sqlplus/admin)

so, check those out (and if you are running a sqlplus script, you could always just "set serveroutput OFF" in your script to undo whatever a login.sql did)

and if you stuff a dbms_output.disable at the beginning of the job while trying to diagnose this, does that in fact get you over the hurdle? (because if that does not, well -- somewhere in your code, there has to be an enable)

the *only* way I know for it to happen is when it is enabled, sorry -- don't know what else to say....


if you are willing to try (caution, I would do this in test first -- it will invalidate code, you'll want to review the side effects -- do NOT drop this into a running production system)

In YOUR SCHEMA (not sys -- YOUR schema only!!!) install this dummy package:

create or replace package dbms_output as

procedure enable (buffer_size in integer default 20000);
procedure disable;
procedure put(a varchar2);
procedure put(a number);
procedure put_line(a varchar2);
procedure put_line(a number);
procedure new_line;
procedure get_line(line out varchar2, status out integer);
procedure get_lines(lines out sys.dbms_output.chararr, numlines in out integer);
end;
/

create or replace package body dbms_output
as
procedure enable (buffer_size in integer default 20000)
is
begin
raise_application_error( -20001, 'I was called, the stack is found in the alert log' );
end;

procedure disable is begin sys.dbms_output.disable; end;
procedure put(a varchar2) is begin sys.dbms_output.put(a); end;
procedure put(a number) is begin sys.dbms_output.put(a); end;
procedure put_line(a varchar2) is begin sys.dbms_output.put_line(a); end;
procedure put_line(a number) is begin sys.dbms_output.put_line(a); end;
procedure new_line is begin sys.dbms_output.new_line; end;
procedure get_line(line out varchar2, status out integer) is begin sys.dbms_output.get_line(line,status); end;
procedure get_lines(lines out sys.dbms_output.chararr, numlines in out integer) is begin sys.dbms_output.get_lines(lines,numlines); end;
end;
/


Now, if someone is calling dbms_output.enable -- they'll call yours and you'll see it.

you won't find it that way, dbms_output raises an ora-20000

ops$tkyte@ORA9IR2> set serveroutput on size 2000
ops$tkyte@ORA9IR2> begin
  2  loop dbms_output.put_line( 'x' );
  3  end loop;
  4  end;
  5  /
x
x
x
...
x
x
begin
*
ERROR at line 1:<b>
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes</b>
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 2


 

</code> http://asktom.oracle.com/~tkyte/runstats.html <code>

benchmark it!

(but also ask, what cost is it to take them out. What if Oracle took out

v$ tables
timed statistics
sql_trace
all events
all diagnostic stuff

would Oracle run

a) faster
b) slower

???


I say "B, obviously". It would definitely be "B".

Without the diagnostic stuff, we would never get it running fast in the first place and we'd never be able to figure out what went wrong when it does.

Leave the debug in your procedure code so you can turn it on when necessary.


Here is some of my old production C code:

sprintf( plsql_block, "%s%s%s%s", BLOCK1, BLOCK2, BLOCK3, BLOCK4 );
debugf(( ctx, "length of pl/sql block is %d bytes", strlen(plsql_block)))

start = get_hsecs(ctx);
debugf(( ctx, "Attempting to describe '%s'", objname ))

if ( oparse(&ctx->cda, plsql_block, -1, 0, 2 )) die(ctx,oerr_cda(ctx));
debugf(( ctx, "describe pl/sql block parsed..." ))

if ( obndrv( &ctx->cda, "ObjectName", -1, objname, strlen(objname)+2,
STRING_TYPE, -1, 0, 0, -1, -1) ) die(ctx,oerr_cda(ctx));
debugf(( ctx, "Bound 'ObjectName'" ))

if ( obndrv( &ctx->cda, "AllNames", -1, AllNames, sizeof(AllNames),
STRING_TYPE, -1, &AllNamesI,0,-1,-1) ) die(ctx,oerr_cda(ctx));
debugf(( ctx, "Bound 'AllNames'" ))

if ( obndrv( &ctx->cda, "HasDefault", -1, HasDefault, sizeof(HasDefault),
STRING_TYPE, -1, &HasDefaultI,0,-1,-1) ) die(ctx,oerr_cda(ctx));
debugf(( ctx, "Bound 'HasDefault'" ))

if ( obndrv( &ctx->cda, "CountOfTables", -1, CountOfTables,
sizeof(CountOfTables), STRING_TYPE, -1, &CountOfTablesI,
0, -1, -1) ) die(ctx,oerr_cda(ctx));
debugf(( ctx, "Bound 'CountOfTables'" ))

if ( obndrv( &ctx->cda, "TableNames", -1, TableNames, sizeof(TableNames),
STRING_TYPE, -1, &TableNamesI,0,-1,-1)) die(ctx,oerr_cda(ctx));
debugf(( ctx, "Bound 'TableNames'" ))

if ( obndrv( &ctx->cda, "qualifiedObj", -1, qualifiedObj,
sizeof(qualifiedObj), STRING_TYPE, -1, &qualifiedObjI,0,-1,-1))
die(ctx,oerr_cda(ctx));
debugf(( ctx, "Bound 'qualifiedObj'" ))

Every other line -- debug, it never let me down :)

absolutely - dbms_output is not limited to sqlplus, anything can make use of it and anything can get an exception from it.

I report on how many questions I've taken over the last 4 weeks on the home page :)

It varies from a few to two hundred or more. Depends on my schedule...

because when you call this job manually you are probably sitting in sqlplus and probably have issued:

SQL> set serveroutput on ......


enabling dbms_output - and the buffer overflows.  In the job queue, the dbms_output stuff is not enabled.


set serveroutput off

or

set serveroutout on size 1000000

or in 10gr2

set serveroutput on size UNLIMITED 

somebody is therefore setting it back on.

look for dbms_output.enable calls in the code.

got example?

..
EXCEPTION
  when OTHERS then
    dbms_output.put_line('Error: '||sqlerrm);
    dbms_output.put_line('EXPECTED RESULTS STATUS = FAILED');
END;
.....

fail, I will never understand this, I will never get this, I cannot fathom why people do this, I cannot even begin to imagine the "logic".

delete those lines of code, they are a bug in your code, just delete them and stay away from when others.

 open C_Primary;
  loop
    fetch C_Primary into varTabName,varNumObj;
    dbms_output.put_line('=== No of Primary Keys: '||varNumObj||' in Table: 
'||varTabName||' ==

  end loop;

that is called an infinite loop. You have no "exit when" conditions

suggestion: do not use explicit cursors if you don't have to - and you don't. Easier to code simply like this:

DECLARE
  cursor C_Primary is select table_name,count(1) cnt
 from USER_CONS_COLUMNS
 where constraint_name like '%CRPU_CARAT%_PK'
 group by table_name;
BEGIN
  dbms_output.enable(1000000);
  dbms_output.put_line('=== Test Case to che....===');
  for x in c_primary loop
    fetch C_Primary into varTabName,varNumObj;
    dbms_output.put_line('=== No of Primary Keys: '|| x.cnt ||
                         ' in Table: '|| x.table_name ||' ==' );
   end loop;
  dbms_output.put_line('EXPECTED RESULTS STATUS = PASSED');
END;
/

1) avoid infinite loops :) yes.

2) you did not have to, you never have to. never. not ever. Not even for a small test case (I cannot think of a single reason why being a test case would cause you to use when others)

just disabling (you should call dbms_output.disable) will work as well - and make your code a little more efficient if you don't need the debug output each time you run it.

unless you use sqlplus (or some tool like it) and "set serveroutput on" OR you explicitly call "dbms_output.enable" from your application - the buffering does not happen, the calls are just going to the equivalent of "/dev/null"

So, it shouldn't be a problem.