1 FETCH Advances a cursor to the next row of its result table and retrieves the values from that row. When used with a list cursor, the FETCH statement places the cursor on a specified position within a list and retrieves a portion of that list. When embedded in precompiled host language programs, the FETCH statement assigns the values from the row to host parameters. In interactive SQL, the FETCH statement displays the value of the row on the terminal screen. 2 Environment You can use the FETCH statement: o In interactive SQL (except for the USING DESCRIPTION clause) o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module 2 Format FETCH -+---------------->-----------------+-+- ++ +> fetch-orientation-clause FROM --+ +> --+| +--------------------------<----------------------------+ ++------------------->---------------------------+-----> +--> INTO -+-+-> ------------+-+-+--+ | | +-> --+ | | | | +-> -------------+ | | | +--------- , <------------------+ | +--> USING DESCRIPTOR -----+ fetch-orientation-clause = -----+--> NEXT --------------------------------------+---> +--> PRIOR --------------------------------------+ +--> FIRST --------------------------------------+ +--> LAST ---------------------------------------+ +--> RELATIVE ----> simple-value-expression -----+ +--> ABSOLUTE ----> simple-value-expression -----+ 2 Arguments 3 cursor-name Specifies the name of the cursor from which you want to retrieve a row. Use a parameter if the cursor referred to by the cursor name was declared at run time with a dynamic DECLARE CURSOR statement. Specify the parameter used for the cursor name in the dynamic DECLARE CURSOR statement. You can use a parameter to refer to the cursor name only when the FETCH statement is accessing a dynamic cursor. 3 fetch_FROM Specifies the specific segment of the list cursor to fetch. These options are available only if you specified the SCROLL option in the DECLARE CURSOR statement. The choices are: o NEXT Fetches the next segment of the list cursor. This is the default. o PRIOR Fetches the segment immediately before the current segment of the list cursor. o FIRST Fetches the first segment of the list cursor. o LAST Fetches the last segment of the list cursor. o RELATIVE simple-value-expression Fetches the segment of the list cursor indicated by the value expression. For example, relative -4 would fetch the segment that is four segments prior to the current segment. o ABSOLUTE simple-value-expression Fetches the segment of the list cursor indicated by the value expression. For example, absolute 4 would fetch the fourth segment of the list cursor. 3 INTO Syntax options: INTO parameter | INTO qualified-parameter | INTO variable Specifies a list of parameters, qualified parameters (host structures), or variables to receive the values SQL retrieves from the row of the cursor's result table. The number of parameters or variables in the list must be the same as the number of values in the row. (If any of the parameters is a host structure, SQL counts the number of parameters in that structure when it compares the number of host parameters in the INTO clause with the number of values in the row.) The data types of parameters and variables must be compatible with the values of the corresponding column of the cursor row. 3 parameter Specifies the name of the cursor from which you want to retrieve a row. Use a parameter if the cursor referred to by the cursor name was declared at run time with a dynamic DECLARE CURSOR statement. Specify the parameter used for the cursor name in the dynamic DECLARE CURSOR statement. You can use a parameter to refer to the cursor name only when the FETCH statement is accessing a dynamic cursor. 3 simple-value-expression Specifies either a positive or negative integer, or a numeric module language or host language parameter. 3 USING_DESCRIPTOR Specifies the name of a descriptor that corresponds to an SQLDA. If you use the INCLUDE statement to insert the SQLDA into your program, the descriptor name is simply SQLDA. An SQLDA is a collection of host language variables used only in dynamic SQL. In a FETCH statement, the SQLDA points to a number of parameters SQL uses to store values from the row. The number of parameters must match the number of columns in the row. The data types of parameters must be compatible with the values of the corresponding column of the cursor row. 2 Examples Example 1: Using a FETCH statement embedded in a PL/I program This program fragment uses embedded DECLARE CURSOR, OPEN, and FETCH statements to retrieve and print the names and departments of managers. The FETCH statement fetches the rows of the result table and stores them in the parameters :FNAME, :LNAME, and :DNAME. /* Declare the parameters: */ BEGIN DECLARE SECTION DCL ID CHAR(3); DCL FNAME CHAR(10); DCL LNAME CHAR(14); END DECLARE SECTION /* Declare the cursor: */ EXEC SQL DECLARE MANAGER CURSOR FOR SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.EMPLOYEE_ID = D.MANAGER_ID ; /* Open the cursor: */ EXEC SQL OPEN MANAGER; /* Start a loop to process the rows of the cursor: */ DO WHILE (SQLCODE = 0); /* Retrieve the rows of the cursor and put the value in parameters: */ EXEC SQL FETCH MANAGER INTO :FNAME, :LNAME, :DNAME; /* Print the values in the parameters: */ . . . END; /* Close the cursor: */ EXEC SQL CLOSE MANAGER; Example 2: Using a FETCH statement to display segments in a column of data type LIST This interactive example uses a table cursor to retrieve a row that contains a list from the RESUMES table. The OPEN statement positions the cursor on the first segment of the list in the RESUME column, and subsequent FETCH statements retrieve successive segments of that list. SQL> DECLARE TBLCURSOR2 CURSOR FOR SELECT EMPLOYEE_ID, RESUME cont> FROM RESUMES; SQL> DECLARE LSTCURSOR2 LIST CURSOR FOR SELECT RESUME cont> WHERE CURRENT OF TBLCURSOR2; SQL> OPEN TBLCURSOR2; SQL> FETCH TBLCURSOR2; 00164 SQL> OPEN LSTCURSOR2; SQL> FETCH LSTCURSOR2; RESUME This is the resume for 00164 SQL> FETCH LSTCURSOR2; RESUME Boston, MA SQL> FETCH LSTCURSOR2; RESUME Oracle Corporation SQL> FETCH LSTCURSOR2; RESUME %RDB-E-STREAM_EOF, attempt to fetch past end of record stream SQL> CLOSE LSTCURSOR2; SQL> SELECT * FROM RESUMES; EMPLOYEE_ID RESUME 00164 72:2:3 1 row selected SQL> CLOSE TBLCURSOR2; SQL> COMMIT; Example 3: Using a scrollable list cursor to fetch list data This C program demonstrates the use of scrollable list cursors to read list data from the sample personnel database using the FETCH statement. The list data being read is from the RESUME column of the RESUMES table in personnel. Note that the RESUME is divided into three segments in this order: 1. A line including the employee's name: "This is the resume for Alvin Toliver" 2. A line stating where the employee lives: "Boston, MA" 3. A line stating where the employee works: "Oracle Corporation" #include stdio #include descrip /* Declare parameters for error handling by including the SQLCA. */ EXEC SQL INCLUDE SQLCA; /* Error-handling section. */ dump_error( ) { short errbuflen; char errbuf[ 1024 ]; struct dsc$descriptor_s errbufdsc; errbufdsc.dsc$b_class = DSC$K_CLASS_S; errbufdsc.dsc$b_dtype = DSC$K_DTYPE_T; errbufdsc.dsc$w_length = 1024; errbufdsc.dsc$a_pointer = &errbuf; if (SQLCA.SQLCODE != 0) { printf( "SQLCODE = %d\n", SQLCA.SQLCODE ); SQL$GET_ERROR_TEXT( &errbufdsc, &errbuflen ); errbuf[ errbuflen ] = 0; printf("%s\n", &errbuf ); } } main() { /* Attach to the personnel database. */ EXEC SQL DECLARE ALIAS FILENAME personnel; /* Declare variables. */ short two_s; long two_l; char blob[8]; char emp_id[6]; char seg2[ 81 ]; /* Declare a table cursor. */ exec sql declare resumes_cursor table cursor for select employee_id, resume from resumes where employee_id = '00164'; /* Declare a read-only scrollable list cursor to fetch the RESUME column. */ exec sql declare resume_list_cursor read only scrollable list cursor for select resume where current of resumes_cursor; /* Open the table cursor. */ exec sql open resumes_cursor; dump_error(); /* Place the first value in the table cursor (00164) into the emp_id parameter, and the resume data into the blob parameter. */ exec sql fetch resumes_cursor into :emp_id, :blob; dump_error(); /* Open the scrollable list cursor. */ exec sql open resume_list_cursor; dump_error(); /* Begin to use the FETCH statement to read desired lines from the resume. If an attempt is made to retrieve a segment that is out of range, the program prints an error message. */ exec sql fetch last from resume_list_cursor into :seg2; printf("FETCH LAST segment returned: %s\n", seg2 ); dump_error(); exec sql fetch next from resume_list_cursor into :seg2; printf("FETCH NEXT segment returned: %s\n", seg2 ); dump_error(); exec sql fetch first from resume_list_cursor into :seg2; printf("FETCH FIRST segment returned: %s\n", seg2 ); dump_error(); exec sql fetch next from resume_list_cursor into :seg2; printf("FETCH NEXT segment returned: %s\n", seg2 ); dump_error(); exec sql fetch next from resume_list_cursor into :seg2; printf("FETCH NEXT segment returned: %s\n", seg2 ); dump_error(); exec sql fetch relative -2 from resume_list_cursor into :seg2; printf("FETCH RELATIVE -2 segment returned: %s\n", seg2 ); dump_error(); exec sql fetch first from resume_list_cursor into :seg2; printf("FETCH FIRST segment returned: %s\n", seg2 ); dump_error(); exec sql fetch relative 2 from resume_list_cursor into :seg2; printf("FETCH RELATIVE 2 segment returned: %s\n", seg2 ); dump_error(); exec sql fetch last from resume_list_cursor into :seg2; printf("FETCH LAST segment returned: %s\n", seg2 ); dump_error(); exec sql fetch prior from resume_list_cursor into :seg2; printf("FETCH PRIOR segment returned: %s\n", seg2 ); dump_error(); exec sql fetch ABSOLUTE 1 from resume_list_cursor into :seg2; printf("FETCH ABSOLUTE 1 segment returned: %s\n", seg2 ); dump_error(); exec sql fetch relative 2 from resume_list_cursor into :seg2; printf("FETCH RELATIVE 2 segment returned: %s\n", seg2 ); dump_error(); two_s = 2; exec sql fetch ABSOLUTE :two_s from resume_list_cursor into :seg2; printf("FETCH ABSOLUTE :two_s segment returned: %s\n", seg2 ); dump_error(); two_l = 2; exec sql fetch ABSOLUTE :two_l from resume_list_cursor into :seg2; printf("FETCH ABSOLUTE :two_1 segment returned: %s\n", seg2 ); dump_error(); exec sql fetch RELATIVE :two_l from resume_list_cursor into :seg2; printf("FETCH RELATIVE :two_1 segment returned: %s\n", seg2 ); dump_error(); exec sql rollback; } The following example shows the output from the program: FETCH LAST segment returned: Oracle Corporation FETCH NEXT segment returned: Oracle Corporation SQLCODE = 100 %SQL-W-NOTFOUND, No rows were found for this statement FETCH FIRST segment returned: This is the resume for Alvin Toliver FETCH NEXT segment returned: Boston, MA FETCH NEXT segment returned: Oracle Corporation FETCH RELATIVE -2 segment returned: This is the resume for Alvin Toliver FETCH FIRST segment returned: This is the resume for Alvin Toliver FETCH RELATIVE 2 segment returned: Oracle Corporation FETCH LAST segment returned: Oracle Corporation FETCH PRIOR segment returned: Boston, MA FETCH ABSOLUTE 1 segment returned: This is the resume for Alvin Toliver FETCH RELATIVE 2 segment returned: Oracle Corporation FETCH ABSOLUTE :two_s segment returned: Boston, MA FETCH ABSOLUTE :two_1 segment returned: Boston, MA FETCH RELATIVE :two_1 segment returned: Boston, MA SQLCODE = -1 %RDB-F-SEGSTR_EOF, attempt to fetch past the end of a segmented string -RDMS-E-FETRELATIVE, fetch relative (2) causes reference out of range 1..3