?
Solved

Insert Data using PL/SQL

Posted on 2007-12-06
7
Medium Priority
?
1,460 Views
Last Modified: 2013-12-18
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
Comment
Question by:khall16
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20424526
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20424796
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
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1000 total points
ID: 20424803
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20424816
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
 
LVL 35

Expert Comment

by:johnsone
ID: 20428095
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
 

Author Closing Comment

by:khall16
ID: 31413586
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20433086
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question