Page 417
ANALYSIS The order is to first use OPEN_CURSOR in line 14 to retrieve an assigned cursor ID. You then assign the anonymous PL/SQL block to a variable called v_MatchRecord in line 15. This string codes the PL/SQL block with proper syntax. Before, in the examples of queries and DDL and DML statements, you had to remove the semicolon. You can see that now the block of SQL statements in lines 16 to 18 ends in a semicolon. You then call DBMS_SQL.PARSE in line 21 to check for syntax errors. After binding the appropriate variables in lines 24 and 25, you execute the anonymous PL/SQL block in line 27. You then retrieve from the buffer the values and store them into the variables using DBMS_SQL.VARIABLE_VALUE in lines 31 and 32. The output is displayed in line 35 with DBMS_OUTPUT.PUT_LINE. Finally, after the entire process has completed, in line 42 you close the cursor with CLOSE_CURSOR.
Now you can experiment to show how the buffer truly operates. You will change the code from Listing 17.4 to select values from MyRow = 4. See Listing 17.5 for the change and then execute.
INPUTListing 17.5. Errors with anonymous blocks.
1: DECLARE
2: /* This procedure calls an anonymous block which performs a
3: query to look up the description for the row id value = 4.
4: This demonstrates the use of an anonymous PL/SQL block
5: within PL/SQL */
6:
7: v_CursorID NUMBER; -- Variable assigned to value from OPEN_CURSOR
8: v_MatchRecord VARCHAR2(500); -- SQL stored as string to select one
Ârecord
9: v_NUMRows INTEGER; -- Number of rows processed - of no use
10: v_MyNum INTEGER;
11: v_MyText VARCHAR2(50);
12:
13: BEGIN
14: v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
15: v_MatchRecord := `BEGIN -- Start of Anonymous PL/SQL Block
16: SELECT MyRow,MyDesc
17: INTO :MyRow, :MyText FROM MyTable
18: WHERE MyRow = 4; -- Notice has ending semicolon
19: END;'; -- End of Anonymous PL/SQL Block
20:
21: DBMS_SQL.PARSE(v_CursorID,v_MatchRecord,DBMS_SQL.V7);
22: /* Perform syntax error checking */
23:
24: DBMS_SQL.BIND_VARIABLE(v_CursorID, `:MyRow',v_MyNum);
25: DBMS_SQL.BIND_VARIABLE(v_CursorID, `:MyText',v_MyText,50);
26:
27: v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
28: /* Execute the SQL code */
29:
30:
continues
Page 418
Listing 17.5. continued
31: DBMS_SQL.VARIABLE_VALUE(v_CursorId,':MyRow',v_MyNum); 32: DBMS_SQL.VARIABLE_VALUE(v_CursorId,':MyText',v_MyText); 33: /* Defines variables to hold output */ 34: 35: DBMS_OUTPUT.PUT_LINE(v_MyNum || ` ` || v_MyText); 36: 37: 38: EXCEPTION 39: WHEN OTHERS THEN 40: RAISE; -- raise if some other unknown error 41: 42: DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor 43: 44: END; -- End PL/SQL block
When you execute the code, your output will appear as
OUTPUT
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 30
ANALYSIS Why did you receive the error? When using anonymous blocks, the buffer will only hold one row of data. If you recall, you have two records with a value of 4, which are selected by the SQL statements in lines 16 to 18. Because you can't use FETCH_ROWS, but can only return one row, the error occurs. This is the primary difference between queries and anonymous blocks.Error Handling
In addition to exceptions, there are many other ways to handle errors with the DBMS_SQL package. You can check to see if the cursor is open with IS_OPEN. You can also check to see information provided by the DBMS_SQL package concerning the last set of rows retrieved with the FETCH_ROWS function with the functions LAST_ROW_COUNT and LAST_ROW_ID. Other functions that provide information on errors are LAST_ERROR_POSITION and LAST_SQL_FUNCTION_CODE.
IS_OPEN
The IS_OPEN function enables you to see if a cursor is open. Some possible uses for this are to check for an open cursor, and if still open, close the cursor. You can also use this to reopen a closed cursor.
The Syntax for the IS_OPEN Function
FUNCTION IS_OPEN(cursor_id IN INTEGER) RETURN BOOLEAN;The cursor_id parameter is the cursor ID assigned from calling the OPEN_CURSOR function. If the cursor is open, it returns true; otherwise, if the cursor is closed, it passes the value false.
Page 419
LAST_ROW_COUNT
The LAST_ROW_COUNT function passes the total number of rows fetched from the cursor to
date. This should be called immediately after FETCH_ROWS to receive accurate results. The syntax for the LAST_ROW_COUNT function is as follows:FUNCTION LAST_ROW_COUNT RETURN INTEGER;If the call is made before FETCH_ROWS, you will receive a value of zero.
LAST_ROW_ID
The LAST_ROW_ID function returns the ROWID of the last row processed. Again, this should be called immediately after FETCH_ROWS for it to be of any usefulness. The syntax for the LAST_ROW_ID function is as follows:
FUNCTION LAST_ROW_ID RETURN ROWID;Testing LAST_ROW_ID and LAST_ROW_COUNT
Enter and execute the code in Listing 17.6 to test these two functions. Make sure that you have entered SET SERVEROUTPUT ON at the SQL*Plus prompt.INPUTListing 17.6. Checking the progress of fetched rows.
1: DECLARE 2: /* The purpose of this PL/SQL block is to demonstrate 3: executing queries within PL/SQL through the use of the 4: DBMS_SQL package. We will simply display the output to 5: screen with the DBMS_OUTPUT package. We also demonstrate 6: the use of tracking the progress of fetching rows*/ 7: 8: v_CursorID NUMBER; -- Variable assigned to value from OPEN_CURSOR 9: v_SelectRecords VARCHAR2(500); -- SQL stored as string to select Ârecords 10: v_NUMRows INTEGER; -- Number of rows processed - of no use 11: v_MyNum INTEGER; 12: v_MyText VARCHAR2(50); 13: v_MyROWID ROWID; 14: v_TotRow INTEGER; 15: 16: BEGIN 17: v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID 18: v_SelectRecords := `SELECT * from MyTable'; -- SQL to view records 19: 20: 21: DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7); 22: /* Perform syntax error checking */ 23: 24: 25: DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum); 26: DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50); 27:continuesPrevious | Table of Contents | Next