Community forum

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


  1. Forum
  2. Support
  3. General problem solving
  4. Get Oracle DBMS Output?
jrtwynam
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.
 Reply
 View All Posts by User
Sponsor
Forum information
Gary_W
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.
 Reply
 View All Posts by User
jrtwynam
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.
 Reply
 View All Posts by User
Similar Topics
Users browsing this topic
  1. Forum
  2. Support
  3. General problem solving
  4. Get Oracle DBMS Output?
 

© VisualCron

Scroll to Top