how to retrieve dbms_output PL/SQL information in ASP - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

how to retrieve dbms_output PL/SQL information in ASP

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 & "
" & " and message is " & err.Description & "
"
end if

Response.Write "strText = " & prm0.Value & "

"

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

Comments

Sign In or Register to comment.