Get Oracle DBMS Output? - 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

2019-03-28T12:35:43Z

Hi,

I have a bunch of scripts running through VC that basically run a block of PL/SQL code against on Oracle database. The code runs perfectly, but I'm having trouble figuring out how to pull any messages it produces back to VC. Ideally, what I'd like to do is something like this:


BEGIN

	DBMS_OUTPUT.PUT_LINE('This is some output');
	
END;

And have the Output variable of the SQL task contain "This is some output". That doesn't seem to work though, so I'm wondering if there is a way to do this that I'm not aware of. The only way I've been able to do it is to have my PL/SQL code write the messages to a table within the Oracle database, and later on in the VC job, have a separate SQL task that queries for those messages. I'd prefer not to do it that way though, because it assumes that I actually have a table available to me where I can write these messages to. For some applications, I do, but for others I don't.

Thanks.

Edited by moderator 2019-03-31T13:28:48Z | Reason: Not specified

Sponsor

Forum information

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

Gary_W
Free support

2019-03-28T20:14:31Z

You can get query results from a stored procedure into StdOut by constructing a stored procedure like this. It works for a message too:

CREATE OR REPLACE PROCEDURE schema_name_here.get_msg_test(prc OUT SYS_REFCURSOR) IS
BEGIN
  OPEN prc FOR
    SELECT 'This is some new output'
    FROM dual;
END get_msg_test;

The SQL task in Visualcron should call it like this: name of procedure: thcxtbl.get_msg_test
with no parameters. It will work for a single message if that's what you are after.
You could collect your output into a varchar(4000) as it runs and select it from dual into the sys_refcursor at the end. You methodology would have to change a bit into calling stored procedures instead of anonymous blocks via scripts, but I would argue it's cleaner to do it this way anyway. Give it a try and post back your opinion.

Edited by user 2019-03-28T21:38:03Z | Reason: Not specified

jrtwynam
Free support Topic Starter

2019-04-01T12:18:21Z

Hi,

Thanks for the reply. I would do something like that if I could, but since I'm not a DBA I don't have access to create stored procedures in the database. That's the main reason why I'm just doing an anonymous block. I've tried the sys refcursor in the anonymous block, but it doesn't seem to do what I need like that. I tried this, which ran in SQL Developer and displayed output, but when I ran it in VC it didn't store the output:


VARIABLE rc refcursor

DECLARE
  
  vTest VARCHAR(500);
  
BEGIN

  vTest := 'This is some output';
  
  OPEN :rc FOR SELECT vTest FROM DUAL;
  
END;
/

PRINT rc

The output it displayed in SQL Developer is this:


anonymous block completed
RC
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
:B1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
This is some output

Not exactly ideal, but I suppose if I could at least get VC to see that, then I could parse it and live with it.