Oracle Built−in Packages- P15: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Kind of ugly isn t it All of those single quotes glommed together three consecutive single quotes at the end of a string result in one single quote around the literal values stuffed into the SQL statement the concatenation datatype conversions etc. This is the tradeoff for not using the programmatic interface provided by DBMS_SQL. You will also call BIND_VARIABLE for every placeholder in a dynamic PL SQL block even if the placeholder is an OUT argument or is otherwise simply receiving a value. Consider the following PL SQL procedure which performs an assignment dynamically when it could simply do it explicitly CREATE OR REPLACE PROCEDURE assign_value newval_in IN NUMBER IS cur PLS_INTEGER fdbk PLS_INTEGER local_var NUMBER Receives the new value BEGIN cur BEGIN container newval END cur newval newval_in cur container 1 fdbk cur cur container local_var END Notice that even though the container placeholder s value before execution is irrelevant I still needed to bind that placeholder to a value for the PL SQL block to execute successfully. The procedure With PL SQL8 you can use the new BIND_ARRAY procedure to perform bulk selects inserts updates and deletes to improve the performance of your application. This same procedure will allow you to use and manipulate index-by tables previously known as PL SQL tables within dynamically constructed PL SQL blocks of code. To perform bulk or array processing you will associate one or more index-by tables with columns or placeholders in your cursor. The BIND_ARRAY procedure establishes this association for you. Call this procedure after PARSE but before calls to EXECUTE and EXECUTE_AND_FETCH PROCEDURE c IN INTEGER name IN VARCHAR2 table_variable IN datatype index1 IN INTEGER index2 IN INTEGER The .
