Listing 17.6. continued

28:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
29:           /* Execute the SQL code  */
30: LOOP
31:      IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
32:           EXIT;
33:      END IF;
34:
35: /*  The next four rows are used for seeing the progress for
36:     fetching rows  */
37:
38:      v_TOTROW := DBMS_SQL.LAST_ROW_COUNT;
39:      v_MyROWID := DBMS_SQL.LAST_ROW_ID;
40:      DBMS_OUTPUT.PUT_LINE(`The last row count is: ` ||
41:           v_TOTROW || ` The last ROWID is: ` || v_MyROWID);
42:
43:      DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
44:      DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText);
45:
46:      DBMS_OUTPUT.PUT_LINE(v_MyNum || ` ` || v_MyText);
47:
48: END LOOP;
49:
50: EXCEPTION
51:      WHEN OTHERS THEN
52:                RAISE; -- raise if some other unknown error
53:
54:      DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
55:
56: END; -- End PL/SQL block

Your output should be

OUTPUT
The last row count is: 1 The last ROWID is: 000000DF.0004.0002
1 One
The last row count is: 2 The last ROWID is: 000000DF.0004.0002
2 Two
The last row count is: 3 The last ROWID is: 000000DF.0004.0002
3 Three
The last row count is: 4 The last ROWID is: 000000DF.0004.0002
4 Four
The last row count is: 5 The last ROWID is: 000000DF.0004.0002
4 Four

ANALYSIS The LAST_ROW_ID is the same for all the entries because the buffer was able to hold all the fetched rows from lines 30 to 48. The inception-to-date count for tracking total rows fetched also worked as stated.

LAST_ERROR_POSITION

The LAST_ERROR_POSITION function returns the location in the SQL statement where the error occurred. This is only used if PARSE was unsuccessful. The syntax for the LAST_ERROR_POSITION function is as follows:

FUNCTION LAST_ERROR_POSITION RETURN INTEGER;

LAST_SQL_FUNCTION_CODE

The LAST_SQL_FUNCTION_CODE function returns the SQL function code associated with the SQL statement. You can find a list of these function codes in the Oracle7 Server Application Developer's Guide.

The syntax for the LAST_SQL_FUNCTION_CODE function is as follows:

FUNCTION LAST_SQL_FUNCTION_CODE RETURN INTEGER;

Fetching Long Data

In a table, a long column can hold up to 2 gigabytes of data. The maximum storage in a
PL/SQL LONG type variable is 32,768 bytes. The difficulty lies in how to retrieve 2 gigabytes' worth of data into a single 32,768-byte field. Because you can't possibly fit the whole 2 gigabytes' worth into a 32KB field, the only solution is to break up the field into smaller 32KB size components and then retrieve them in the same order. You can do this by using the procedures DEFINE_COLUMN_LONG and COLUMN_VALUE_LONG. By the way, it would take up to 63 loops to retrieve up to 2 gigabytes in 32KB increments.

DEFINE_COLUMN_LONG

This procedure is almost identical in use to DEFINE_COLUMN described earlier in the chapter. The purpose of DEFINE_COLUMN was to associate a variable name with a column of a table.

The Syntax for the DEFINE_COLUMN_LONG Procedure

PROCEDURE DEFINE_COLUMN_LONG(cursor_id IN INTEGER,
                                    position IN INTEGER);

cursor_id is the cursor ID supplied by OPEN_CURSOR. position is the relative position in the row that you are trying to define. If this column were the first field in the row, it would be position 1. If there were two columns, the first one 4 bytes long, this would be position 5.

COLUMN_VALUE_LONG

After the data is in the buffer, you need to retrieve it into a local variable. For LONG variables, you would use COLUMN_VALUE_LONG.

The Syntax for the COLUMN_VALUE_LONG Procedure

PROCEDURE COLUMN_VALUE_LONG(cursor_id IN INTEGER,
                                    position IN INTEGER,
                                    length IN INTEGER,
                                    offset IN INTEGER,
                                    variable_out OUT VARCHAR2,
                                    length_value OUT INTEGER);

cursor_id is the cursor ID supplied by OPEN_CURSOR. position is the starting position of the column in the row. If the column were the first field in the row, it would start at position 1. length is how much of the segment you are trying to retrieve, expressed in bytes. offset is used for the current location in the actual LONG field. This value starts at 0. If you were retrieving lengths in increments of 1000 bytes, the first offset would be 0, the next offset would be 1000, then 2000, and so on. The variable in which you want to store the output retrieved from the buffer is the variable_out parameter. Finally, length_value is how much data was actually retrieved in bytes. If length_value is less than the length you wanted to retrieve, you have come to the end of the data for the LONG field.

Summary

Today's lesson covered the DBMS_SQL package provided by Oracle. Before you can use this package, you must make sure that the package has been installed and that you have the appropriate privileges. Using this package, you can execute DDL statements, which allow you to create, drop, and alter items, and DML statements, which allow you to update, delete, and insert rows. You can also run queries from within PL/SQL and dynamically change these queries to suit a particular condition.

This package also provides a way to execute anonymous blocks from PL/SQL. The DBMS_SQL package provides additional error checking and the ability to store and retrieve LONG data up to 2 gigabytes to and from the buffer.

Q&A

Q What three types of statements can the DBMS_SQL package process?
A The DBMS_SQL process allows for the dynamic processing of SQL for non-query DDL and DML statements, SQL queries, and anonymous blocks of PL/SQL from within PL/SQL.
Q What is the one of the main differences between an anonymous PL/SQL block and a query using the DBMS_SQL package when retrieving data?
A With queries, you can use FETCH_ROWS to bring back multiple rows, whereas anonymous blocks can retrieve only one row. Attempting to store more than one row in the buffer will result in an error.
Q Should queries end with the appropriate punctuation of a semicolon?
A When using queries from within PL/SQL, you should not end the query with a semicolon.