db_exec_plsql (public)

db_exec_plsql [ -dbn dbn ] statement_name sql [ args... ]

Defined in packages/acs-tcl/tcl/00-database-procs.tcl

Oracle: Executes a PL/SQL statement, and returns the variable of bind variable :1.

PostgreSQL: Performs a pl/pgsql function or procedure call. The caller must perform a select query that returns the value of the function.

Examples:

    # Oracle:
    db_exec_plsql delete_note {
        begin  note.del(:note_id);  end;
    }

    # PostgreSQL:
    db_exec_plsql delete_note {
        select note__delete(:note_id);
    }
    

If you need the return value, then do something like this:

    # Oracle:
    set new_note_id [db_exec_plsql create_note {
        begin
        :1 := note.new(
          owner_id => :user_id,
          title    => :title,
          body     => :body,
          creation_user => :user_id,
          creation_ip   => :peeraddr,
          context_id    => :package_id
        );
        end;
    }]

    # PostgreSQL:
    set new_note_id [db_exec_plsql create_note {
        select note__new(
                         null,
                         :user_id,
                         :title,
                         :body,
                         'note',
                         now(),
                         :user_id,
                         :peeraddr,
                         :package_id
                         );
    }]
    

You can call several pl/sql statements at once, like this:

    # Oracle:
    db_exec_plsql delete_note {
        begin
        note.del(:note_id);
        note.del(:another_note_id);
        note.del(:yet_another_note_id);
        end;
    }

    # PostgreSQL:
    db_exec_plsql delete_note {
        select note__delete(:note_id);
        select note__delete(:another_note_id);
        select note__delete(:yet_another_note_id);
    }
    

If you are using xql files then put the body of the query in a yourfilename-oracle.xql or yourfilename-postgresql.xql file, as appropriate. E.g. the first example transformed to use xql files looks like this:

yourfilename.tcl:

    db_exec_plsql delete_note { }

yourfilename-oracle.xql:

    <fullquery name="delete_note">      
      <querytext>
        begin
        note.del(:note_id);
        end;
      </querytext>
    </fullquery>

yourfilename-postgresql.xql:

    <fullquery name="delete_note">      
      <querytext>
        select note__delete(:note_id);
      </querytext>
    </fullquery>

Switches:

-dbn (optional)

The database name to use. If empty_string, uses the default database.

Parameters:

statement_name
sql

See Also:

• /doc/db-api-detailed.html

Source code:

    db_exec_plsql__arg_parser

    ad_arg_parser { bind_output bind } $args

    # Query Dispatcher (OpenACS - ben)
    set full_statement_name [db_qd_get_fullname $statement_name]

    if { [info exists bind_output] } {
	return -code error "the -bind_output switch is not currently supported"
    }

    set driverkey [db_driverkey $dbn]
    switch $driverkey {
        postgresql {
            set postgres_p 1
        }

        oracle -
        nsodbc -
        default {
            set postgres_p 0
        }
    }

    if { ! $postgres_p } {
        db_with_handle -dbn $dbn db {
            # Right now, use :1 as the output value if it occurs in the statement,
            # or not otherwise.
            set test_sql [db_qd_replace_sql $full_statement_name $sql]
            if { [regexp {:1} $test_sql] } {
                return [db_exec exec_plsql_bind $db $full_statement_name $sql 2 1 ""]
            } else {
                return [db_exec dml $db $full_statement_name $sql]
            }
        }
    } else {
        # Postgres doesn't have PL/SQL, of course, but it does have
        # PL/pgSQL and other procedural languages.  Rather than assign the
        # result to a bind variable which is then returned to the caller,
        # the Postgres version of OpenACS requires the caller to perform a
        # select query that returns the value of the function.

        # We are no longer calling db_string, which screws up the bind
        # variable stuff otherwise because of calling environments. (ben)

        ad_arg_parser { bind_output bind } $args

        # I'm not happy about having to get the fullname here, but right now
        # I can't figure out a cleaner way to do it. I will have to
        # revisit this ASAP. (ben)
        set full_statement_name [db_qd_get_fullname $statement_name]

        if { [info exists bind_output] } {
            return -code error "the -bind_output switch is not currently supported"
        }

        db_with_handle -dbn $dbn db {
            # plsql calls that are simple selects bypass the plpgsql 
            # mechanism for creating anonymous functions (OpenACS - Dan).
            # if a table is being created, we need to bypass things, too (OpenACS - Ben).
            set test_sql [db_qd_replace_sql $full_statement_name $sql]
            if {[regexp -nocase -- {^\s*select} $test_sql match]} {
                # ns_log Debug "PLPGSQL: bypassed anon function"
                set selection [db_exec 0or1row $db $full_statement_name $sql]
            } elseif {[regexp -nocase -- {^\s*create table} $test_sql match] || [regexp -nocase -- {^\s*drop table} $test_sql match]} {
                ns_log Debug "PLPGSQL: bypassed anon function for create/drop table"
                set selection [db_exec dml $db $full_statement_name $sql]
                return ""
            } else {
                # ns_log Debug "PLPGSQL: using anonymous function"
                set selection [db_exec_plpgsql $db $full_statement_name $sql  $statement_name]
            }
            return [ns_set value $selection 0]
        }
    }

XQL Not present:

Generic, PostgreSQL, Oracle