Dbms_output not generating in Automic REP log

[Norman Nation]

Hi all,

I'm trying to figure out why dbms_output.put_line not printing out to the REP log in Automic. The only change I can think of is that we switched to a JDBC connection from using the Oracle specific connection driver.

Have everyone else experience dbms_output abruptly stopping to print out to the REP log?

[Nicole Boeger]

Dear Norman,

you are right, I think switching to jdbc connection is the reason.

There might be possibilities to get the output, look at following links (I did not work through the blogs or test anything, so no guarantee) and you might need to do some java programming:
https://blog.jooq.org/2017/12/15/how-to-fetch-oracle-dbms_output-from-jdbc/
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45027262935845

If you just need some return status of the script it might be easier to use a stored function that returns the status upon completion.


------------------------------
Regards, Nicole
------------------------------

[Norman Nation]

Hi Nicole,

Thanks for the quick feedback! If I wanted to return status from a stored function how would I go about that after executing the Stored procedure?

[Nicole Boeger]

Dear Norman,

you can use a stored function in a select statement, like this:

select yourfunction() from dual;

The return of your function should appear in the REP Report of the SQL Job. The table dual is a kind of dummy table in Oracle that every user can select and that has one row. It's a common use case with oracle for retrieving the output of a function.


------------------------------
Regards, Nicole
------------------------------