I try to insert query alphanumeric sequence in table but it is not working

Question

I am trying to insert an alphanumeric sequence in Oracle but it is not working.

create sequence LIB  start with 1 increment by 1;

select 'LIBR'||to_char(seq_no.nextval,'FM0000099') from dual;

create table addLib(
   USER_ID VARCHAR2(20) PRIMARY KEY,
   NAME VARCHAR2(20),
   PASSWORD VARCHAR2(20),
   FATHER_NAME VARCHAR2(20),
   DOB DATE,
   QUALIFICATION VARCHAR2(20),
   DOJ DATE,
   STATE VARCHAR2(20),
   ADDRESS VARCHAR2(20),
   PINCODE NUMBER(6));

INSERT INTO addLibrarian
values(
  LIB.nextval(LIBR),
  'abc',
  '1234',
  'xyz',
  to_date('19970503','YYYYMMDD'),
  'b.tech',
  to_date('19970308','YYYYMMDD'),
  'tanakpur',
  262309);

I expect it to insert all values into the table but an error shows not enough values.

You've created a table named ADDLIB but you're inserting into a table named ADDLIBRARIAN. Also, what is NEXTVAL(LIBR) supposed to do? NEXTVAL doesn't take an argument. ????? — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Sep 8, 2019 at 0:58

Popeye · Accepted Answer · 2019-09-08 03:00:57Z

1

There are multiple issues:

LIB.nextval(LIBR) is not a valid syntax.
table name is not valid.
value for address is missing in VALUES clause.

Try this:

create table addLib( -- changed table name
   USER_ID VARCHAR2(20) PRIMARY KEY,
   NAME VARCHAR2(20),
   PASSWORD VARCHAR2(20),
   FATHER_NAME VARCHAR2(20),
   DOB DATE,
   QUALIFICATION VARCHAR2(20),
   DOJ DATE,
   STATE VARCHAR2(20),
   ADDRESS VARCHAR2(20),
   PINCODE NUMBER(6));

INSERT INTO addLib -- changed table name
values(
  'LIBR' || LAPD(LIB.nextval, 7, 0), -- use something like this
  'abc',
  '1234',
  'xyz',
  to_date('19970503','YYYYMMDD'),
  'b.tech',
  to_date('19970308','YYYYMMDD'),
  'tanakpur',
  '<address>', -- add this value
  262309);

Cheers!!

answered Sep 8, 2019 at 3:00

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit prajapati Over a year ago

Thankyu so much for help

EliteRaceElephant · Accepted Answer · 2019-09-08 00:30:09Z

0

You have 10 columns and insert 9 values: but error show not enough values. Add the last correct value to your insertion.

This type of error can be prevented by formatting your code or use a prettifier to do it for you automatically.

edited Sep 8, 2019 at 0:30

answered Sep 8, 2019 at 0:15

EliteRaceElephant

8,1924 gold badges57 silver badges66 bronze badges

Collectives™ on Stack Overflow

I try to insert query alphanumeric sequence in table but it is not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related