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):
%3 test_auth_authenticate auth_authenticate (test acs-authentication) package_exec_plsql package_exec_plsql test_auth_authenticate->package_exec_plsql db_exec_plsql db_exec_plsql (public) package_exec_plsql->db_exec_plsql package_function_p package_function_p (private) package_exec_plsql->package_function_p package_plsql_args package_plsql_args (private) package_exec_plsql->package_plsql_args content::extlink::copy content::extlink::copy (public) content::extlink::copy->package_exec_plsql content::extlink::is_extlink content::extlink::is_extlink (public) content::extlink::is_extlink->package_exec_plsql content::extlink::new content::extlink::new (public) content::extlink::new->package_exec_plsql content::folder::delete content::folder::delete (public) content::folder::delete->package_exec_plsql content::folder::get_index_page content::folder::get_index_page (public) content::folder::get_index_page->package_exec_plsql

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

[ hide source ] | [ make this the default ]
Show another procedure: