asked on

Insert Data using PL/SQL

Why can't I insert data that I am prompted for into a table? I'm trying to understand. Based on research, I think this would be better coded using JAVA, but I'm just a beginner and don't know JAVA. Is this possible in PL/SQL? What am I doing wrong?

SET SERVEROUTPUT ON FEEDBACK OFF
DECLARE
First_Name VARCHAR2(25);
Last_Name VARCHAR2 (25);
Birth_Date DATE;
HR_Rate NUMBER(6,2);
 
BEGIN
First_Name := &Employee_First_Name;
Last_Name := &Employee_Last_Name;
Birth_Date := &Employee_DOB;
HR_Rate := &Employee_Rate;
 
EXCEPTION
     WHEN OTHERS THEN
     	  NULL;
 
BEGIN
	Insert into Emp
	(First_Name, Last_Name, Employee_DOB, HR_Rate)
	Values  
	(First_Name, Last_Name, TO_DATE(Employee_DOB, 'MM/DD/YYYY'), HR_Rate);
 
END;
    	  
END;

Open in new window

Guy Hengel [angelIII / a3]

SET SERVEROUTPUT ON FEEDBACK OFF
DECLARE
First_Name VARCHAR2(25);
Last_Name VARCHAR2 (25);
Birth_Date DATE;
HR_Rate NUMBER(6,2);

BEGIN
First_Name := &Employee_First_Name;
Last_Name := &Employee_Last_Name;
Birth_Date := &Employee_DOB;
HR_Rate := &Employee_Rate;

EXCEPTION
WHEN OTHERS THEN
NULL;

BEGIN
Insert into Emp
(First_Name, Last_Name, Employee_DOB, HR_Rate)
Values
(First_Name, Last_Name, Employee_DOB, HR_Rate);

END;

END;

Naveen Kumar

i suggest to use different variable names ( not the same names as the column names to avoid any confusion and better readability )

The attached works code perfectly for me.

thanks

SET SERVEROUTPUT ON FEEDBACK on --- first have feedback on so that you get to know what is happening
             -- once your procedure starts working, if you can put OFF instead of ON.
DECLARE
First_Name VARCHAR2(25);
Last_Name VARCHAR2 (25);
Birth_Date DATE;
HR_Rate NUMBER(6,2);
 
BEGIN
v_First_Name := '&Employee_First_Name';
v_Last_Name := '&Employee_Last_Name';
v_Birth_Date := '&Employee_DOB';
v_HR_Rate := &Employee_Rate;   -- for only varchar/char/date data types we must use quotes otherwise u 
         -- will get errors
 
      Insert into Emp
      (First_Name, Last_Name, Employee_DOB, HR_Rate)
      Values  
      (v_First_Name, v_Last_Name, v_Employee_DOB, v_HR_Rate);
 
EXCEPTION
     WHEN OTHERS THEN
    dbms_output.put_line('exception raised'||sqlcode||sqlerrm);            
END;
/

Open in new window

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

I think once your code starts working, then your code should also have a commit after the insert happens :

-- put this after your insert statement in your code
commit;

Thanks

johnsone

Why are you trying to use & in PL/SQL code? I believe you would run into issues if you ran this outside of SQL*Plus. If you are doing this entirely in SQL*Plus, I would do put the code below in a .sql script and run it.

set verify off
accept v_first_name prompt "First name "
accept v_last_name prompt "Last name "
accept v_birth_date prompt "Birth date (format MM/DD/YYYY) "
accept v_hr_rate prompt "Rate "
 
insert into emp
            (first_name, last_name, employee_dob, hr_rate)
     values ('&v_first_name', '&v_last_name', to_date('&v_birth_date', 'MM/DD/YYYY'), &v_hr_rate);

Open in new window

khall16

ASKER

Thank you for recommending to use different variable names than the column names, for clarification. Oh, and yes, the commit!!!
What if I wanted the user to enter the date as mm/dd/yyyy instead of dd-mon-yyyy?

Naveen Kumar

may be try something like this to enter date in a different format :

v_Birth_Date := to_date('&Employee_DOB','dd/mm/yyyy');

Thanks