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
14.	Fetch cursor data to number variable
15.	Fetch cursor till cursorVariable%notfound
16.	Fetch cursor to table collection of row type
17.	Fetch cursor value to three variables
18.	Fetch row by row
19.	Fetch single column cursor to varchar2 variable

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

Related examples in the same category