Oracle Stored Procedure INSERT example

Here’s an INSERT stored procedure example in Oracle database.

1. Table SQL Script

DBUSER table creation script.


CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

2. Stored Procedure

A stored procedure, accept 4 IN parameters and insert it into table “DBUSER”.


CREATE OR REPLACE PROCEDURE insertDBUSER(
	   p_userid IN DBUSER.USER_ID%TYPE,
	   p_username IN DBUSER.USERNAME%TYPE,
	   p_createdby IN DBUSER.CREATED_BY%TYPE,
	   p_date IN DBUSER.CREATED_DATE%TYPE)
IS
BEGIN

  INSERT INTO DBUSER ("USER_ID", "USERNAME", "CREATED_BY", "CREATED_DATE") 
  VALUES (p_userid, p_username,p_createdby, p_date);

  COMMIT;

END;
/

3. Calls from PL/SQL

Call from PL/SQL like this :


BEGIN
   insertDBUSER(1001,'mkyong','system',SYSDATE);
END;

Result
A record is inserted into DBUSER table via insertDBUSER store procedure.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
9 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
karthi vijayAdamharicactuscraigV'Jay Kumar Recent comment authors
newest oldest most voted
hari
Guest
hari

i tried to insert the values but i get an error tell me what to do

Adam
Guest
Adam

Depends on the error. If it was the same as mine, the call from pl/sql should use single quotations ‘ and not double quotations “. Hope this helps!

V'Jay Kumar
Guest
V'Jay Kumar

hello,
i created a table emp and emp_err without any constraints on it and i dumped the raw data into it.Also i have created another table emp1 with a primary and check constraint on it.
So , i want to write a procedure to load the data from emp into these tables emp1 and emp_err. Before inserting the record it should check the constraints. If the constraints are satisfied then the data should load into emp1 otherwise the data is stored into emp_err.
I am struggling with this problem can u pease help me out!!!!

karthi vijay
Guest
karthi vijay

how to run the code?

V'Jay Kumar
Guest
V'Jay Kumar

please send me the reply as early as possible coz my project got stuck in the middle due to this problem

NAVEEN KUMAR REDDY
Guest
NAVEEN KUMAR REDDY

HOW WE CAN INSERT MULTIPLE RECORDS INTO THE TABLE IN ORACLE DB.it’s very urgent

V'Jay Kumar
Guest
V'Jay Kumar

insert all
into table_name(column 1,column 2, column 3,………..column n) values(value1,value 2, value 3………… value n)
into table_name(column 1,column 2, column 3,………..column n) values(value1,value 2, value 3………… value n)
into table_name(column 1,column 2, column 3,………..column n) values(value1,value 2, value 3………… value n)
into table_name(column 1,column 2, column 3,………..column n) values(value1,value 2, value 3………… value n)
into table_name(column 1,column 2, column 3,………..column n) values(value1,value 2, value 3………… value n)
.
.
.
select 1 from dual;
you can do multiple inserts like this……

akshatha
Guest
akshatha

It helped me a lot. Thank you so much.

Unyime
Guest
Unyime

Thanks. love how you structure it out. Nice, simple and straight forward

VINAYAKA
Guest
VINAYAKA

this was very helpful.Thanks….

cactuscraig
Guest
cactuscraig

i tried executing simple SP from VB.NET and every time telling me “not all variables bound”