TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

Oracle

Oracle SQL INSERT Statement

Overview

We can use Oracle SQL INSERT Statement to add new rows into an Oracle database table. INSERT statement can be used to add data to a single table or multiple tables.

INSERT is a DML statement( Data Manipulation Language ). DML statements are SQL statements that modify the data in the Oracle database.

In previous post, we have created dept and emp tables. We will insert sample data into these tables.

https://www.testingdocs.com/oracle-sql-create-table-statement/

Example – INSERT single row

We will insert a single row to dept table. The dept table has three columns(deptno,dname,loc)

SQL query to insert data into all the three columns.

INSERT INTO dept VALUES
(1000,’ACCOUNTING’,’NEW YORK’);

Oracle SQL Insert Statement

We will get the SQL success message : 1 row created.

Common Errors

Example – Unique constraint violated

SQL> INSERT INTO dept VALUES (2000,’RESEARCH’,’DALLAS’);
INSERT INTO DEPT VALUES (2000,’RESEARCH’,’DALLAS’)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

We know that deptno column is the primary key for the dept table. The values for the deptno column should be unique in the table. If we try to insert duplicate values for the deptno column values, we would get an error. This is because the unique constraint on the primary key column for the table is violated. There is already a row with deptno with 2000 present in the dept table.

The following error would be displayed:

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

SCOTT is the Oracle schema, PK_DEPT is the primary key constraint name for the table. In Oracle, to uniquely identify the database object names we use the dot notation.

<database_schema>.<database object>

Example – Error Not enough values

This is common error with the Insert statement. We have to supply values for all the columns of the table.

SQL> INSERT INTO dept VALUES (‘RESEARCH’,’DALLAS’);
INSERT INTO DEPT VALUES (‘RESEARCH’,’DALLAS’)
*
ERROR at line 1:
ORA-00947: not enough values

—

Oracle Database Tutorials on this website:

https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

More information about Oracle Database:

https://www.oracle.com/database/

Related Posts

Oracle Databse 19c Download

Oracle /

Install Oracle 19c Database On Windows 10

ORA-01109 Database Not Open

Oracle /

Fix ORA-01109: database not open Error

Oracle SQL Update Statement

Oracle /

Oracle SQL Update Statement

SELECT all rows and columns Oracle

Oracle /

Oracle SQL SELECT Statement

CREATE TABLE Statement Oracle SQL

Oracle /

Oracle SQL CREATE TABLE Statement

‹ Oracle SQL SELECT Statement› Oracle SQL Update Statement

Recent Posts

  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Print Triangle Pattern Flowchart
  • RAPTOR Editions
  • Flowgorithm Conditional Breakpoint Statement
  • Flowgorithm Read Numbers from File Example
  • Search Text File Flowchart Example
  • Flowgorithm Turtle Graphics Symbols
  • Draw Circle using Flowgorithm Turtle

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com

Go to mobile version