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 - Слава Україні, Sep 08 '19 at 00:58

score 1 · Answer 1 · answered Sep 08 '19 at 03:00

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!!

Thankyu so much for help – Amit prajapati Sep 08 '19 at 08:44 — Amit prajapati, Sep 08 '19 at 08:44

EliteRaceElephant · Answer 2 · 2019-09-08T00:30:09.907

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 08 '19 at 00:30

answered Sep 08 '19 at 00:15

EliteRaceElephant

7,744
4
47
62

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

2 Answers2