Insert sequence Oracle

Question

Folks good night I’m wanting to insert data into a table , which has a sequence created, but when trying to insert data with nextval it presents the following error

Error SQL Error: ORA-00911: invalid character

911. 00000 - invalid"Haracter"

*Cause: Identifiers may not start with any ASCII Character other than Letters and Numbers. $#_ are also allowed after the first Character. Identifiers Enclosed by doublequotes may contain any Character other than a doublequote. Alternative Quotes (q'#...#') cannot use Spaces, tabs, or Carriage Returns as delimiters. For all other contexts, Consult the SQL Language Reference Manual.

CREATE TABLE CursoSequencia (
    cod_curso NUMBER(3),
    nome_curso VARCHAR2(30) NOT NULL,
    carga_hor NUMBER(3),
    CONSTRAINT cursoSquencia_cod_pk PRIMARY KEY(cod_curso)
);

CREATE SEQUENCE cod_curso INCREMENT BY 1
START WITH 1001;

INSERT INTO CursoSequencia VALUES(cod_curso.nextval, ‘AdministracaoOracle’,3);

INSERT INTO CursoSequencia VALUES(cod_curso.nextval, ‘AdministracaoSQLServer’,3);

Answer 1

Hello Felipe!

Making a quick translation of the error presented, ORA-00911, we know that invalid characters have been found in your query that is preventing its execution. However, I found another small problem in the DDL of your code.

I’ll separate the problems I found in your code into two parts.

1. Your specific DML command in the column nome_curso VARCHAR2(30), from which you are using the single quotes ‘ instead ' , note that visibly, the problem is just this, you are using the simple quotes in american standard and this results in invalid character conflict.
   Change the quotation marks used in the standard values ‘AdministracaoOracle’ for 'AdministracaoOracle'.
2. Note that your SEQUENCE has been set to count its initial value from the value 1001, so we know that this value will apply to a total of 4 characters or more. By defining the total number of characters in your PK for 3 digits: cod_curso NUMBER(3), you will have the error (ORA-01438), this time caused by the total of characters returned from cod_curso.nextval, precisely because the SEQUENCE return a total of 4 digits and the allowed of your column cod_curso is being 3 digits.

Solving these two items above, you’ll have your SQL working normally.

Sources:Docs.oracle, dba-orable.

Fiddle with the corrections here.

Answer 2

If you have created a Quence that will do auto increment, at the time of doing Insert no need to inform id, the database will automatically add.

So just make your Insert that way:

INSERT INTO CursoSequencia (nome_curso, carga_hor) VALUES (‘AdministracaoSQLServer’,3);