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.
3>WRITE A PL/SQL STORED PROCEDURE PROGRAM TO INSERT A RECORD INTO DEPARTMENTS TABLE BY USING ‘IN’ pARAMETERS.? Answer please
how to create exception store procedure
how to insert mutliple values in stored procedure at a time
Use loop to insert data
i want insert and save Picture in oracle database ?please help me
Please I don’t understand what u mean by %type. Mine failed to insert.
Create table buster(
Buster_id int, buster_name varchar(10)
);
Table created.
Please how do i insert into the above table using procedure
how to run the code?
i tried to insert the values but i get an error tell me what to do
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!
i tried executing simple SP from VB.NET and every time telling me “not all variables bound”
its all fraud.. Waste of time. Yakk
yessss
please send me the reply as early as possible coz my project got stuck in the middle due to this problem
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!!!!
HOW WE CAN INSERT MULTIPLE RECORDS INTO THE TABLE IN ORACLE DB.it’s very urgent
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……
It helped me a lot. Thank you so much.
Thanks. love how you structure it out. Nice, simple and straight forward
this was very helpful.Thanks….