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
