legal and illegal FETCH statements. : Cursor Fetch « Cursor « Oracle PL / SQL

Oracle PL / SQL 1.  Aggregate Functions 2.  Analytical Functions 3.  Char Functions 4.  Constraints 5.  Conversion Functions 6.  Cursor 7.  Data Type 8.  Date Timezone 9.  Hierarchical Query 10.  Index 11.  Insert Delete Update 12.  Large Objects 13.  Numeric Math Functions 14.  Object Oriented Database 15.  PL SQL 16.  Regular Expressions 17.  Report Column Page 18.  Result Set 19.  Select Query 20.  Sequence 21.  SQL Plus 22.  Stored Procedure Function 23.  Subquery 24.  System Packages 25.  System Tables Views 26.  Table 27.  Table Joins 28.  Trigger 29.  User Previliege 30.  View 31.  XML Java Java Tutorial Java Source Code / Java Documentation Java Open Source Jar File Download Java Articles Java Products Java by API C# / C Sharp C# / CSharp Tutorial ASP.Net JavaScript DHTML JavaScript Tutorial JavaScript Reference HTML / CSS HTML CSS Reference C / ANSI-C C Tutorial C++ C++ Tutorial PHP Python SQL Server / T-SQL Oracle PL/SQL Tutorial PostgreSQL SQL / MySQL MySQL Tutorial VB.Net VB.Net Tutorial Flash / Flex / ActionScript VBA / Excel / Access / Word Microsoft Office PowerPoint 2007 Tutorial Microsoft Office Excel 2007 Tutorial Microsoft Office Word 2007 Tutorial

Oracle PL / SQL » Cursor » Cursor Fetch   legal and illegal FETCH statements.   SQL> SQL> SQL> CREATE TABLE session (   2    department       CHAR(3),   3    course           NUMBER(3),   4    description      VARCHAR2(2000),   5    max_lecturer     NUMBER(3),   6    current_lecturer NUMBER(3),   7    num_credits      NUMBER(1),   8    room_id          NUMBER(5)   9    ); Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('HIS', 101, 'History 101', 30, 11, 4, 20000); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('HIS', 301, 'History 301', 30, 0, 4, 20004); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('CS', 101, 'Computer Science 101', 50, 0, 4, 20001); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('ECN', 203, 'Economics 203', 15, 0, 3, 20002); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('CS', 102, 'Computer Science 102', 35, 3, 4, 20003); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('MUS', 410, 'Music 410', 5, 4, 3, 20005); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('ECN', 101, 'Economics 101', 50, 0, 4, 20007); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('NUT', 307, 'Nutrition 307', 20, 2, 4, 20008); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)   2              VALUES ('MUS', 100, 'Music 100', 100, 0, 3, NULL); 1 row created. SQL> SQL> select * from session; DEP   COURSE --- -------- DESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID ------------ ---------------- ----------- -------- HIS   101.00 History 101        30.00            11.00        4.00 ######## HIS   301.00 History 301        30.00              .00        4.00 ######## DEP   COURSE --- -------- DESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID ------------ ---------------- ----------- -------- CS    101.00 Computer Science 101        50.00              .00        4.00 ######## ECN   203.00 Economics 203 DEP   COURSE --- -------- DESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID ------------ ---------------- ----------- --------        15.00              .00        3.00 ######## CS    102.00 Computer Science 102        35.00             3.00        4.00 ######## MUS   410.00 DEP   COURSE --- -------- DESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID ------------ ---------------- ----------- -------- Music 410         5.00             4.00        3.00 ######## ECN   101.00 Economics 101        50.00              .00        4.00 ######## DEP   COURSE --- -------- DESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID ------------ ---------------- ----------- -------- NUT   307.00 Nutrition 307        20.00             2.00        4.00 ######## MUS   100.00 Music 100       100.00              .00        3.00 DEP   COURSE --- -------- DESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID ------------ ---------------- ----------- -------- 9 rows selected. SQL> SQL> DECLARE   2    v_Department  session.department%TYPE;   3    v_Course      session.course%TYPE;   4    CURSOR myAllSession IS   5      SELECT *   6        FROM session;   7    v_sessionRecord  myAllSession%ROWTYPE;   8  BEGIN   9    OPEN myAllSession;  10  11    FETCH myAllSession INTO v_sessionRecord;  12  13    FETCH myAllSession INTO v_Department, v_Course;  14  END;  15  /   FETCH myAllSession INTO v_Department, v_Course;   * ERROR at line 13: ORA-06550: line 13, column 3: PLS-00394: wrong number of values in the INTO list of a FETCH statement ORA-06550: line 13, column 3: PL/SQL: SQL Statement ignored SQL> SQL> drop table session; Table dropped. SQL>   Related examples in the same category 1.  Below is a function that demonstrates how to use the FETCH statement 2.  Fetch value from cursor 3.  Fetches the records from a cursor variable that returns employee information 4.  Fetch cursor to two variables 5.  "ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE loop. 6.  A WHILE cursor fetch loop. 7.  Different BULK_COLLECT statements used for bulk binds 8.  Check Cursor%FOUND after fetching 9.  Fetch value in cursor until NOTFOUND 10.  ORA-01002: fetch out of sequence 11.  Fetch out cursor value and insert to another table 12.  PLS-00394: wrong number of values in the INTO list of a FETCH statement 13.  FETCH cursor BULK COLLECT

w___w___w__.__j_a___v__a2__s___._co__m__ | Contact Us

Copyright 2003 - 08 Demo Source and Support. All rights reserved.

All other trademarks are property of their respective owners.