db_exec_plsql (public)

 db_exec_plsql [ -dbn dbn ] statement_name sql [ -bind bind ]

Defined in packages/acs-tcl/tcl/01-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.
-bind
(optional)
Parameters:
statement_name
sql
See Also:

Partial Call Graph (max 5 caller/called nodes):
%3 test_db_bind_var_substitution db_bind_var_substitution (test acs-tcl) db_exec_plsql db_exec_plsql test_db_bind_var_substitution->db_exec_plsql db_driverkey db_driverkey (public) db_exec_plsql->db_driverkey db_exec db_exec (public) db_exec_plsql->db_exec db_exec_plpgsql db_exec_plpgsql (private) db_exec_plsql->db_exec_plpgsql db_qd_get_fullname db_qd_get_fullname (public) db_exec_plsql->db_qd_get_fullname db_qd_replace_sql db_qd_replace_sql (public) db_exec_plsql->db_qd_replace_sql acs_cr_scheduled_release_exec acs_cr_scheduled_release_exec (private) acs_cr_scheduled_release_exec->db_exec_plsql acs_sc::contract::new acs_sc::contract::new (public) acs_sc::contract::new->db_exec_plsql acs_sc::contract::operation::new acs_sc::contract::operation::new (public) acs_sc::contract::operation::new->db_exec_plsql acs_sc::impl::alias::new acs_sc::impl::alias::new (public) acs_sc::impl::alias::new->db_exec_plsql acs_sc::impl::binding::new acs_sc::impl::binding::new (public) acs_sc::impl::binding::new->db_exec_plsql

Testcases:
db_bind_var_substitution
[ show source ]
Show another procedure: