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 (required)

The PL/[pg]SQL package

object_name (required)

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