8-11 Storing Dynamic SQL in Large Objects
Problem
The SQL code that you need to assemble at runtime is likely to exceed the 32KB limit that is bound to VARCHAR2 types. You need to be able to store dynamic SQL text in a type that will allow more for a large amount of text.
Solution #1
Declare a CLOB variable, and store your SQL string within it. After the CLOB has been created, execute the SQL. This can be done using either native dynamic SQL or the DBMS_SQL package. For the example, assume that a block of text is being read from an external file, and it will be passed to a procedure to be processed. That text will be the SQL string that will be dynamically processed within the procedure. Since the external text file can be virtually any size, this text must be read into a CLOB data type and then passed to the procedure in this example for processing. The following procedures processes the CLOB as dynamic SQL.
The first example demonstrates the parsing and execution of a dynamic SQL statement that has been stored in a CLOB using the DBMS_SQL package. Note that this procedure does not return any value, so it is not meant for issuing queries but rather for executing code.
CREATE OR REPLACE PROCEDURE execute_clob(sql_text CLOB) AS
sql_string CLOB;
cur_var BINARY_INTEGER;
ret_var INTEGER;
return_value VARCHAR2(100);
BEGIN
sql_string := sql_text;
cur_var := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_var, sql_string, DBMS_SQL.NATIVE);
ret_var := DBMS_SQL.EXECUTE(cur_var);
DBMS_SQL.CLOSE_CURSOR(cur_var);
END;
Solution #2
The second example is the same procedure written to use native dynamic SQL. You will notice that the code is a bit shorter, and there is less work that needs to be done in order to complete the same transaction.
CREATE OR REPLACE PROCEDURE execute_clob_nds(sql_text IN CLOB) AS
sql_string CLOB;
BEGIN
sql_string := sql_text;
EXECUTE IMMEDIATE sql_string;
END;
As noted previously, the native dynamic SQL is easier to follow and takes less code to implement. For the sake of maintaining a current code base, use of native dynamic SQL would be encouraged. However, DBMS_SQL is still available and offers different options as mentioned in the first recipes in this chapter.
How It Works
Oracle added some new features for working with dynamic SQL into the Oracle Database 11g release. Providing the ability to store dynamic SQL into a CLOB is certainly a useful addition. Prior to Oracle Database 11g, the only way to dynamically process a string that was larger than 32KB was to concatenate two VARCHAR types that were at or near 32KB in size. The largest string that could be processed by native dynamic SQL was 64KB. With the release of Oracle Database 11g, the CLOB (character large object) can be used in such cases, mitigating the need to concatenate two different variables to form the complete SQL.
Using DBMS_SQL and its PARSE function, SQL that is stored within a CLOB can be easily processed. The following lines of code are the lines from the first solution that read and process the CLOB:
cur_var := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_var, v_sql, DBMS_SQL.NATIVE);
ret_var := DBMS_SQL.EXECUTE(cur_var);
DBMS_SQL.CLOSE_CURSOR(cur_var);
The first line opens a new cursor using DBMS_SQL.OPEN_CURSOR. It assigns an integer to the cur_var variable, which is then passed to the DBMS_SQL.PARSE procedure. DBMS_SQL.PARSE also accepts the SQL CLOB and a constant DBMS_SQL.NATIVE that helps discern the dialect that should be used to process the SQL. The dialect is also referred to as the language_flag, and it is used to determine how Oracle will process the SQL statement. Possible values include V6 for version 6 behavior, V7 for Oracle database 7 behavior, and NATIVE to specify normal behavior for the database to which the program is connected. After the SQL has been parsed, it can be executed using the DBMS_SQL.EXECUTE function. This function will accept the cursor variable as input and execute the SQL. A code of 0 is returned if the SQL is executed successfully. Lastly, remember to close the cursor using DBMS_SQL.CLOSE_CURSOR and passing the cursor variable to it.
The example in Solution #2 of this recipe demonstrates the use of native dynamic SQL for execution of dynamic SQL text that is stored within a CLOB. Essentially no differences exist between the execution of SQL text stored in a VARCHAR data type as opposed to SQL text stored within a CLOB for native dynamic SQL. The code is short and precise, and it is easy to read.