package_exec_plsql (public)
package_exec_plsql [ -var_list var_list ] package_name object_name
Defined in packages/acs-subsite/tcl/package-procs.tcl
Calls a pl/[pg]sql proc/func defined within the object type's package. Use of this Tcl API proc avoids the need for the developer to write separate SQL for each RDBMS we support.
- Switches:
- -var_list
(optional)- A list of pairs of additional attributes and their values to pass to the constructor. Each pair is a list of two elements: key => value
- Parameters:
- package_name - The PL/[pg]SQL package
object_name - The PL/[pg]SQL function within the package
- Returns:
- empty string for procs, function return value for funcs
Example:
set var_list [list [list group_id $group_id]] package_exec_plsql -var_list $var_list group delete- Author:
- Don Baccus <dhogaza@pacifier.com>
- Created:
- 12/31/2003
- Partial Call Graph (max 5 caller/called nodes):
- Testcases:
- auth_authenticate
Source code: # Ugly hack for the case where a proc has params named "package_name" or "object_name". set __package_name $package_name set __object_name $object_name foreach arg [package_plsql_args -object_name $__object_name $__package_name] { set real_params([string toupper $arg]) 1 } # Use pieces to generate the parameter list to the new # function. Pieces is just a list of lists where each list contains only # one item - the name of the parameter. We keep track of # parameters we've already added in the array param_array (all keys are # in uppercase) set pieces [list] foreach pair $var_list { lassign $pair __key __value if { ![info exists real_params([string toupper $__key])] } { # The parameter is not accepted as a parameter to the # pl/sql function. Ignore it. ns_log Warning "package_exec_plsql: skipping $__key not found in params for $__package_name $__object_name" continue } lappend pieces [list $__key] set param_array([string toupper $__key]) 1 # Set the value for binding set $__key $__value } if { [package_function_p -object_name $__object_name $__package_name] } { return [db_exec_plsql exec_func_plsql {}] } else { db_exec_plsql exec_proc_plsql {} }Generic XQL file: packages/acs-subsite/tcl/package-procs.xql
PostgreSQL XQL file: <fullquery name="package_exec_plsql.exec_func_plsql"> <querytext> select ${__package_name}__${__object_name}([plpgsql_utility::generate_attribute_parameter_call \ -prepend ":" \ ${__package_name}__${__object_name} \ $pieces]) </querytext> </fullquery>packages/acs-subsite/tcl/package-procs-postgresql.xql
Oracle XQL file: <fullquery name="package_exec_plsql.exec_func_plsql"> <querytext> BEGIN :1 := ${__package_name}.${__object_name}([plsql_utility::generate_attribute_parameter_call \ -prepend ":" \ -indent [expr [string length $__package_name] + 29] \ $pieces] ); END; </querytext> </fullquery> <fullquery name="package_exec_plsql.exec_proc_plsql"> <querytext> BEGIN ${__package_name}.${__object_name}([plsql_utility::generate_attribute_parameter_call \ -prepend ":" \ -indent [expr [string length $__package_name] + 29] \ $pieces] ); END; </querytext> </fullquery>packages/acs-subsite/tcl/package-procs-oracle.xql