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

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.