Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1459
  • Last Modified:

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

0
khall16
Asked:
khall16
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this latest one...i forgot to change your variable names in my previous code snippet.

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
v_First_Name VARCHAR2(25);
v_Last_Name VARCHAR2 (25);
v_Birth_Date DATE;
v_HR_Rate NUMBER(6,2);
 
BEGIN
v_First_Name := '&Employee_First_Name';
v_Last_Name := '&Employee_Last_Name';
v_Birth_Date := '&Employee_DOB';   -- I presume you are giving in dd-mon-yyyy format
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

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
johnsoneSenior Oracle DBACommented:
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

0
 
khall16Author Commented:
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?  
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
may be try something like this to enter date in a different format :

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

Thanks
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now