Here is my db_exec function. The subst happens after the database call so the database sees the bind variables but developer support sees the values. It's kludgy and is easily fooled but works 99+% of the time. I'm sure it could be done better but I think this runs fast ( I use this in production ). Picking up the dbms_output is helpfull also since you can output from large plsql functions and see the stuff at the bottom of your page. Otherwise debuging large plsql blocks is nearly impossible.
barry ad_proc -private db_exec { type db statement_name sql args } { A helper procedure to execute a SQL statement, potentially binding depending on the value of the $bind variable in the calling environment (if set). } { set start_time [clock clicks] set out "" catch { set out [ns_ora exec_plsql $db { declare status number; line varchar2(4000); ret varchar2(32000); begin status := 0; ret := ' '; while status = 0 loop dbms_output.get_line(line,status); if ( status = 0 ) then ret := ret || line || '<br>'; end if; end loop; :1 := ret; end; } ] } err if { $out != " " } { ds_comment "dbmsOutput: $out" } ns_ora dml $db { begin dbms_output.enable(100000); end; } set vars "test" set errno [catch { upvar bind bind if { [info exists bind] && [llength $bind] != 0 } { if { [llength $bind] == 1 } { append vars "$bind" return [eval [list ns_ora $type $db -bind $bind $sql] $args] } else { set bind_vars [ns_set create] foreach { name value } $bind { ns_set put $bind_vars $name $value append vars "$name=$value" } return [eval [list ns_ora $type $db -bind $bind_vars $sql] $args] } } else { append vars "none" return [uplevel 2 [list ns_ora $type $db $sql] $args] } } error] regsub -all {:([a-zA-z])} $sql {$\1} sql catch { set sql [uplevel 2 [list subst $sql]] } err ad_call_proc_if_exists ds_collect_db_call $db $type $statement_name $sql $start_time $errno $error if { $errno == 2 } { return $error } global errorInfo errorCode return -code $errno -errorinfo $errorInfo -errorcode $errorCode $error } -----Original Message----- From: Andrew Piskorski [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 8:53 AM To: [EMAIL PROTECTED] Subject: Re: [AOLSERVER] turn database verbosity on/off? On Tue, May 28, 2002 at 03:59:55AM -0500, Barry Books wrote: > I patched the ACS database routines somthing like > > catch { > regsub {:} $sql {$} sql > set sql [subst $sql] > } err > > The regsub makes the bind variables TCL variables and subst replaces them > with the value. The catch fixes dumb problems like where a = ':abc'. This > goes right before the developer support call in the db routines. I also > patched them so you can do dbms_output and capture it. I can dig up the > exact patch later if you need it. Barry, so you effectively eliminated all use of bind variables? That doesn't seem like a good idea - I WANT to use bind variables. I'd just like some convenient way of including the values of the bind vars in any error message. Perhaps there's a simpler way, but I suspect I'd need to add that to the database driver (Oracle in this case). -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com