how to retrieve dbms_output PL/SQL information in ASP

Tue, 08 Sep 2009 15:06:32 -0700

Posted a 'new message on the Active Server Pages forum.

I have an ASP page that runs a simple PL/SQL "begin-end" and writes a variable to the DBMS_OUTPUT with a "put_line" command. (see code)

DECLARE
strText varchar2(50) := 'Testing Testing Testing';
BEGIN
dbms_output.enable(10000);
dbms_output.put_line(strText);
END;

This code runs fine in TOAD and also in my ASP page (not ASP.net). Well, at least i'm not getting any errors related to my ADO connection to ORACLE. However, I'm not able to display the strText variable. I created an ADO paramater to go along with the ADO command object i use to run this PL/SQL, but nothing is displyed. Any suggestions? Here is my ASP code:

set cnn = nothing
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "Driver={Microsoft ODBC for Oracle}; Server=xxxx; Uid=xxxx; Pwd=xxxx"
set cmd = Server.CreateObject("ADODB.Command")

strSQL = "DECLARE " & chr(13)
strSQL = strSQL & "strText varchar2(50) := 'Testing Testing Testing'; " & chr(13)
strSQL = strSQL & "BEGIN " & chr(13)
strSQL = strSQL & "dbms_output.enable(10000); " & chr(13)
strSQL = strSQL & "dbms_output.put_line(strText); " & chr(13)
(13)
strSQL = strSQL & "END;"

set prm0 = server.CreateObject("ADODB.Parameter")
set prm0 = cmd.CreateParameter("@strText", 200, 3, 50, "")

cmd.Parameters.Append prm0

cmd.ActiveConnection = cnn
cmd.CommandText = strSQL
cmd.CommandType = 1

cmd.Execute

if err.number <> 0 then
Response.Write "Error occurred - error number is " & err.number & "<BR>" & " and message is " & err.Description & "<BR>"
end if

Response.Write "strText = " & prm0.Value & "<BR><BR>"

set prm0 = nothing
set cmd = nothing
cnn.Close
set cnn = nothing

n2singing's Feed - Programmer's Heaven

how to retrieve dbms_output PL/SQL information in ASP