Home » Open Source » Programming Interfaces » plsql output to STDOUT (11gr2)
plsql output to STDOUT [message #672216] Mon, 08 October 2018 03:15 Go to next message
hudo
Messages: 163
Registered: May 2004
Senior Member
I got a cronjob, where a perl script is started. This script uses the DBI Modul to connect to the Oracle database. The essential part looks like

eval {
	my $func = $dbora->prepare( q {

DECLARE
	l_sql VARCHAR2(100) ;	

BEGIN

	-- here timestamp to stdout
 	MY_PACKAGE.PROCEDURE_1;  
	-- here timestamp to stdout
 	MY_PACKAGE.PROCEDURE_2;  
	-- here timestamp to stdout
 	MY_PACKAGE.PROCEDURE_3;  	

	-- here timestamp to stdout
	
NULL;
END;	                                   
} ); 

$func->execute;	

I wonder, if there is a way to print the timestamp to stdout, and perl receives this message and can process it.
I know, I could write the timestamp in a table and read with perl from that table, but I'm looking for a shorter "on the fly" method.




Re: plsql output to STDOUT [message #672226 is a reply to message #672216] Mon, 08 October 2018 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 66641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PL/SQL does not write to STDOUT or terminal, PL/SQL just compute, it runs internally inside the database server.
You could define a bind variable and set it in PL/SQL and read it in the caller but you will have it only when PL/SQL returns the control to the caller. A PL/SQL block is a single statement for the caller.

Also we are still waiting for your feedback in your previous topics.

Re: plsql output to STDOUT [message #672247 is a reply to message #672226] Mon, 08 October 2018 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>I know, I could write the timestamp in a table and read with perl from that table, but I'm looking for a shorter "on the fly" method.
Other sessions can NOT see uncommitted DML changes.

What problem are you really trying to solve?
Re: plsql output to STDOUT [message #672255 is a reply to message #672247] Tue, 09 October 2018 01:35 Go to previous messageGo to next message
hudo
Messages: 163
Registered: May 2004
Senior Member
@BlackSwan: As I told, I was curious, if there is a way to forward messages from within plsql to stdout.
Quote:
What problem are you really trying to solve?
I like to time each procedure. I solved this now by running only one procedure in an eval-block, then take the timestamp with perl and go to the next eval-block.
Re: plsql output to STDOUT [message #672256 is a reply to message #672255] Tue, 09 October 2018 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So declare a variable for each time you want to get and do as I said.

Re: plsql output to STDOUT [message #672258 is a reply to message #672255] Tue, 09 October 2018 02:05 Go to previous message
hudo
Messages: 163
Registered: May 2004
Senior Member
@Michel Cadot: Regarding my previous topic, your approach with SUBSTR and INSTR helped, thx. Anyway it was a bad design.
Previous Topic: SQL query returning wrong values
Next Topic: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges
Goto Forum:
  


Current Time: Thu Oct 17 00:16:43 CDT 2019