Get DBMS Output from Oracle Script? - VisualCron

Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.

Welcome Guest! To enable all features please Login or Register.

jrtwynam
Free support Topic Starter

2018-06-15T17:08:34Z

Hi,

I have an SQL task set up to run a text script against an Oracle database. The script is basically in this form:


DECLARE
     <insert my variables here>
BEGIN
     <do a whole bunch of stuff here>
END;

The script runs fine, but part of the code between the BEGIN and END statements involves constructing a string containing a log of what happened during the run. I'm not sure how to get it to return that string. Oracle won't let me simply use a RETURN statement because this isn't a function, and it doesn't like when I do

SELECT vMsg FROM DUAL;

(where vMsg is my variable holding the result string).

Any thoughts on how I can get this string out of Oracle and into VC, without having to write the string to the database? I had thought about writing it to a MSG_LOG table and then doing something like

SELECT my_msg FROM msg_log

, which I think would appear in the VC StdOut variable or something, but I'd rather not have to write the message to the database if I can avoid it.

Thanks.

Sponsor

Forum information

Wanna join the discussion?! Login to your Forum forum account or Register a new forum account

thomas
Free support

2018-06-18T11:25:07Z

Haven't used oracle in many years, but maybe you could try dms_output and see if VC picks it up, eg:

DBMS_OUTPUT.PUT_LINE('something here');

jrtwynam
Free support Topic Starter

2018-06-18T12:18:02Z

If only everything in life were that easy. :)

My script already has a number of DBMS_OUTPUT.PUT_LINE calls in it, but I tried another task with a very simple script:


DECLARE

  vCtr    NUMBER(5);
  
BEGIN

  vCtr := 1;
  
  WHILE vCtr <= 5
    LOOP
  
    DBMS_OUTPUT.PUT_LINE('This is iteration ' || vCtr || '.');
  
    vCtr := vCtr + 1;
    
    END LOOP;
  
END;

When I run this script in Oracle SQL Developer, I can clearly see the dbms output, but I don't know how to get VC to pick it up.

thomas
Free support

2018-06-18T13:04:55Z

ok. I don't have oracle installed so i cannot test. Last attempt: try adding this at the top of your script

SET SERVEROUTPUT ON

https://stackoverflow.co...-of-a-variable-in-oracle

jrtwynam
Free support Topic Starter

2018-06-18T13:11:57Z

The funny thing is that I already have that line in the script when I run it in SQL Developer, but I didn't think to try it in VC. I just tried it, but it caused the script to fail:


Error in SQL query: ORA-00922: missing or invalid option

Exception in Task: ORA-00922: missing or invalid option