When i try to run this code:
DECLARE
STUDENT_ID NUMBER;
BEGIN
-- Generate the next value for the sequence
SELECT LMS_STUDENT_DETAILS_SEQ.nextval;
-- Insert data into LMS_STUDENT_DETAILS table
INSERT INTO LMS_STUDENT_DETAILS (STUDENT_ID, STUDENT_NAME, GENDER, DATE_OF_BIRTH, COURSE, CONTACT_NUMBER, DEPARTMENT)
VALUES (STUDENT_ID, :P6_STUDENT_NAME, :P6_GENDER, :P6_DOB, :P6_COURSE, :P6_CONTACT_NO, :P6_DEPARTMENT);
-- Insert data into LMS_BORROWER table
INSERT INTO LMS_BORROWER (BORROWER_ID, ENTITY_OWNER_FK, ENTITY_TYPE)
VALUES (LMS_BORROWER_SEQ.nextval, STUDENT_ID, 'STUDENT');
END;
I faced this error:
ORA-06550: line 1, column 106: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set
OK, first - here's how you should provide a test case to anyone, ie, they need to be able to reproduce the environment on their own machine
SQL> create sequence LMS_STUDENT_DETAILS_SEQ;
Sequence created.
SQL> create sequence LMS_BORROWER_SEQ;
Sequence created.
SQL> create table LMS_STUDENT_DETAILS
2 (STUDENT_ID int, STUDENT_NAME int, GENDER int, DATE_OF_BIRTH int, COURSE int, CONTACT_NUMBER int, DEPARTMENT int);
Table created.
SQL> create table LMS_BORROWER(BORROWER_ID int, ENTITY_OWNER_FK int, ENTITY_TYPE varchar2(100));
Table created.
SQL>
SQL> variable P6_STUDENT_NAME number
SQL> variable P6_GENDER number
SQL> variable P6_DOB number
SQL> variable P6_COURSE number
SQL> variable P6_CONTACT_NO number
SQL> variable P6_DEPARTMENT number
SQL>
Now that they/we have this, we can test your code:
SQL> DECLARE
2 STUDENT_ID NUMBER;
3 BEGIN
4 -- Generate the next value for the sequence
5 SELECT LMS_STUDENT_DETAILS_SEQ.nextval;
6
7 -- Insert data into LMS_STUDENT_DETAILS table
8 INSERT INTO LMS_STUDENT_DETAILS (STUDENT_ID, STUDENT_NAME, GENDER, DATE_OF_BIRTH, COURSE, CONTACT_NUMBER, DEPARTMENT)
9 VALUES (STUDENT_ID, :P6_STUDENT_NAME, :P6_GENDER, :P6_DOB, :P6_COURSE, :P6_CONTACT_NO, :P6_DEPARTMENT);
10
11 -- Insert data into LMS_BORROWER table
12 INSERT INTO LMS_BORROWER (BORROWER_ID, ENTITY_OWNER_FK, ENTITY_TYPE)
13 VALUES (LMS_BORROWER_SEQ.nextval, STUDENT_ID, 'STUDENT');
14
15 END;
16 /
SELECT LMS_STUDENT_DETAILS_SEQ.nextval;
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
Looks like we need an INTO
SQL> DECLARE
2 STUDENT_ID NUMBER;
3 BEGIN
4 -- Generate the next value for the sequence
5 SELECT LMS_STUDENT_DETAILS_SEQ.nextval into student_id; <<<====
6
7 -- Insert data into LMS_STUDENT_DETAILS table
8 INSERT INTO LMS_STUDENT_DETAILS (STUDENT_ID, STUDENT_NAME, GENDER, DATE_OF_BIRTH, COURSE, CONTACT_NUMBER, DEPARTMENT)
9 VALUES (STUDENT_ID, :P6_STUDENT_NAME, :P6_GENDER, :P6_DOB, :P6_COURSE, :P6_CONTACT_NO, :P6_DEPARTMENT);
10
11 -- Insert data into LMS_BORROWER table
12 INSERT INTO LMS_BORROWER (BORROWER_ID, ENTITY_OWNER_FK, ENTITY_TYPE)
13 VALUES (LMS_BORROWER_SEQ.nextval, STUDENT_ID, 'STUDENT');
14
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Some notes as well:
1) Try not to name variables the same as column names, because you'll end up with bugs for sure
2) The sequence value can be obtained as you need it (at INSERT) which avoids an extra call, eg
SQL> DECLARE
2 l_STUDENT_ID NUMBER;
3 BEGIN
4
5 -- Insert data into LMS_STUDENT_DETAILS table
6 INSERT INTO LMS_STUDENT_DETAILS (STUDENT_ID, STUDENT_NAME, GENDER, DATE_OF_BIRTH, COURSE, CONTACT_NUMBER, DEPARTMENT)
7 VALUES (LMS_STUDENT_DETAILS_SEQ.nextval, :P6_STUDENT_NAME, :P6_GENDER, :P6_DOB, :P6_COURSE, :P6_CONTACT_NO, :P6_DEPARTMENT)
8 returning student_id into l_student_id;
9
10 -- Insert data into LMS_BORROWER table
11 INSERT INTO LMS_BORROWER (BORROWER_ID, ENTITY_OWNER_FK, ENTITY_TYPE)
12 VALUES (LMS_BORROWER_SEQ.nextval, l_STUDENT_ID, 'STUDENT');
13
14 END;
15 /
PL/SQL procedure successfully completed.