Author Topic: INSERT statement with a SELECT statement in a store procedure  (Read 14637 times)

oracle_guy

  • Newbie
  • *
  • Posts: 11
    • View Profile
I am writing a  stored procedure which inserts values into emp table for employee name and department ID. The dept ID however is a part of dept table. The input values to the procedure are emp name and dept name. A select statement from the dept table maps the dept name to dept ID and this is passed as value to the original insert statement. I have written the following  but I keep getting errros. (sql statement ignored after line 4 and not enough values) Any help is appreciated

Code: [Select]
CREATE or REPLACE procedure INPUT_RECORDS(p_ename in varchar2,
p_deptID in number, p_dname in varchar) as
begin
INSERT into emp(ename, deptID)
(SELECT
deptID
FROM
dept where dname = p_dname);
commit;
end;
/

Errors
SQL statement ignored after line 4
ORA 00947 : not enough values
« Last Edit: April 12, 2009, 11:39:42 AM by Mike »


Mike

  • Administrator
  • Hero Member
  • *****
  • Posts: 2052
    • View Profile
Re: INSERT statement with a SELECT statement in a store procedure
« Reply #1 on: April 12, 2009, 11:39:29 AM »
Code: [Select]
CREATE or REPLACE procedure INPUT_RECORDS(p_ename in varchar2,
p_deptID in number, p_dname in varchar) as
begin
INSERT into emp(ename, deptID)
(SELECT
deptID
FROM
dept where dname = p_dname);
commit;
end;
/

Errors
SQL statement ignored after line 4
ORA 00947 : not enough values

Hmmm, my guess is that although you're telling it to insert both the ename and deptID, you're only supplying the deptID as a value.

oracle_guy

  • Newbie
  • *
  • Posts: 11
    • View Profile
Re: INSERT statement with a SELECT statement in a store procedure
« Reply #2 on: April 12, 2009, 06:09:30 PM »
Thank you very much. I finally got the procedure to be executed without errors.  However I have a different problem which I will describe after I explain what I am trying to do.

The database has the  following tables

1.  EMP(emp_ID, Ename, deptID), 
2. DEPT(deptID, deptName).

The calling envirnoment  takes as the argument, emp-ID, ename and
deptName but to insert into the table EMP, we need emp_ID, ename and dept_ID. A select statement written within the INSERT statement  fetches the dept_ID after the input deptName from the calling routine is matched.
My confusion is how to write the calling routine such that it takes deptName as the argument and not the dept_ID. The conflict is that I get an error as the arguments of the calling envirnoment and that of the INSERT statement in the store procedure are different.  The INSERT statement by itself works very well with hard coded data taking ename and deptName as the argument.  This is what I have till now.

CREATE or REPLACE procedure INPUT_RECORDS(
p_ename in varchar2,
p_deptID in number,
p_dname in varchar)
IS
BEGIN
INSERT into emp(emp_id, ename, deptID)
values(EmpSeq.nextval, 'v_ename',
'(SELECT dept_ID
FROM
dept where dname = v_dname)');
commit;
end;
/

Calling routine


Begin
INPUT_RECORDS('EmpSeq.nextval', 'Robert', Mktg);
end;
/

Error:
PLS-00306: Wrong number or types of arguments in call to 'INPUT_RECORDS'

Mike

  • Administrator
  • Hero Member
  • *****
  • Posts: 2052
    • View Profile
Re: INSERT statement with a SELECT statement in a store procedure
« Reply #3 on: April 13, 2009, 06:18:39 AM »
Thank you very much. I finally got the procedure to be executed without errors.  However I have a different problem which I will describe after I explain what I am trying to do.
Maybe someone else can chime in on this and give you some ideas; I'm not sure exactly how this might be done.

oratune

  • Newbie
  • *
  • Posts: 7
    • View Profile
Re: INSERT statement with a SELECT statement in a store procedure
« Reply #4 on: April 14, 2009, 07:49:37 AM »
You cannot put a SELECT in a VALUES clause.  Your procedure should look like this instead:

CREATE or REPLACE procedure INPUT_RECORDS(
p_ename in varchar2,
p_dname in varchar)
IS
BEGIN
INSERT into emp(emp_id, ename, deptID)
 select EmpSeq.nextval, p_ename, deptID
 FROM
 dept where deptname = p_dname;
commit;
end;
/



David Fitzjarrell


oracle_guy

  • Newbie
  • *
  • Posts: 11
    • View Profile
Re: INSERT statement with a SELECT statement in a store procedure
« Reply #5 on: April 14, 2009, 01:41:14 PM »
Hi David
My code like below surprisingly worked. I am able to insert values into the tables. Please critique my code for conventions or any other errors.

CREATE or REPLACE procedure INPUT_RECORDS(
p_ename in varchar2,
p_dname in varchar)
IS
BEGIN
INSERT into emp(emp_id, ename, deptID)
values(EmpSeq.nextval, p_ename,
(SELECT deptID
FROM
dept where deptname = p_dname));
commit;
end;
/

Thanks.

oratune

  • Newbie
  • *
  • Posts: 7
    • View Profile
Re: INSERT statement with a SELECT statement in a store procedure
« Reply #6 on: April 14, 2009, 01:46:36 PM »
It works for you, and that's the important aspect.  It is, though, going through a bit more work than it should (I think) since you can select a sequence value from any table and p_ename is a string literal which can also be selected from any table.  Since your embedded select returns only one row the solution I posted seems, to me, to be a better possibility.  I prefer to use either an INSERT ... SELECT ... or an INSERT ... VALUES ... and not combine the two.

Of course we're all different and your choices may not be mine.  Again, it works, so why change it?



David Fitzjarrell

oracle_guy

  • Newbie
  • *
  • Posts: 11
    • View Profile
Re: INSERT statement with a SELECT statement in a store procedure
« Reply #7 on: April 14, 2009, 04:41:13 PM »
I understand that your solution is more efficient than mine. This is the critique that was needed. Thanks again David and this helps the learning process.