Home » SQL & PL/SQL » SQL & PL/SQL » Capture DBMS_OUTPUT in Windows script
Capture DBMS_OUTPUT in Windows script [message #243497] Thu, 07 June 2007 09:44 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
I am executing oracle sqlplus from windows VBScript.

Any help on an example on how can we capture the DBMS_OUTPUT messages in windows when we run the procedures in sqlplus?

Thank you!
Yog


Re: Capture DBMS_OUTPUT in Windows script [message #243520 is a reply to message #243497] Thu, 07 June 2007 10:51 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Any help on an example on how can we capture the DBMS_OUTPUT messages in windows when we run the procedures in sqlplus


You want to Diplay some message when some error occur in database.

like this

Not such a record found in database
Re: Capture DBMS_OUTPUT in Windows script [message #243522 is a reply to message #243497] Thu, 07 June 2007 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean by "capture"?

Regards
Michel
Re: Capture DBMS_OUTPUT in Windows script [message #243529 is a reply to message #243497] Thu, 07 June 2007 11:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
DBMS_OUTPUT also knows GET_LINE
From the package spec:
Quote:
-- A trigger might want to print out some debugging information. To do
-- do this the trigger would do
-- dbms_output.put_line('I got here:'||:new.col||' is the new value');
-- If the client had enabled the dbms_output package then this put_line
-- would be buffered and the client could, after executing the statement
-- (presumably some insert, delete or update that caused the trigger to
-- fire) execute
-- begin dbms_output.get_line(:buffer, :status); end;
-- to get the line of information back. It could then display the
-- buffer on the screen. The client would repeat calls to get_line
-- until status came back as non-zero. For better performance, the
-- client would use calls to get_lines which can return an array of
-- lines.
Re: Capture DBMS_OUTPUT in Windows script [message #243531 is a reply to message #243529] Thu, 07 June 2007 11:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I now see that you called sqlplus from your VBScript, so you have to make sure sqlplus does not get the lines itself..

Further text from the specs
Quote:
-- SQL*DBA and SQL*PLUS, for instance, implement a 'SET SERVEROUTPUT
-- ON' command so that they know whether to make calls to get_line(s)
-- after issuing insert, update, delete or anonymous PL/SQL calls
-- (these are the only ones that can cause triggers or stored procedures
-- to be executed).

So, make sure to set serveroutput off.
Next problem is how to get the lines from that other session.....
Re: Capture DBMS_OUTPUT in Windows script [message #243904 is a reply to message #243497] Sun, 10 June 2007 23:48 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
    Dim objShell, objWshScriptExec, objStdOut, objStdErr, strLine

     Set objShell = CreateObject("WScript.Shell") 

     Set objWshScriptExec = objShell.Exec(lcsCommand & " " & lcsParams) 

     Set objStdOut = objWshScriptExec.StdOut 

     Set objStdErr = objWshScriptExec.StdErr



What I meant was in the procedures and functions if we output to DBMS_OUTPUT under the exception block...how do we capture those messages in VBScript when executing ..


e.g.
create procedure proca
begin
--execute logics
exception
when others then
DBMS_OUTPUT.put_line ("Proc A failed") ;
--SQLERRM
end;

Thank you
Yog Smile
Re: Capture DBMS_OUTPUT in Windows script [message #243915 is a reply to message #243904] Mon, 11 June 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you use Oracle Object for OLE (OO4O), create a database object and directly execute your procedure with this object and get the exception.
This is a better option than creating a shell object to execute a sqlplus which will execute your procedure...

Regards
Michel


Re: Capture DBMS_OUTPUT in Windows script [message #244059 is a reply to message #243915] Mon, 11 June 2007 08:42 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Thank you Michel.

Is there an example of using OLE?
Re: Capture DBMS_OUTPUT in Windows script [message #244064 is a reply to message #244059] Mon, 11 June 2007 09:00 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many years I didn't work with OO4O.
A search on Oracle Technology Network gives me that: OO4O
Maybe the technology is now Oracle Provider for OLEDB but I don't know anything about it.

Regards
Michel
Previous Topic: Limitation of Oracle XE
Next Topic: Assigning records to varchar2
Goto Forum:
  


Current Time: Wed Dec 07 02:48:42 CST 2016

Total time taken to generate the page: 0.12613 seconds