Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Topics - oracle_guy

Pages: [1]
1
PSOUG / Error message PLS 00103
« on: April 16, 2009, 11:18:42 AM »
Hi I am trying to write a store procedure that contains 2 select statements and I want the union of that to be the output. My select statements work very well at the command line. But when I use it in a store procedure, I get errors such as the above or errors where the entire SQL statement is ignored after the SELECT statement. Can somebody point if there is a different way to code select statements in a SP.
Thanks

2
I have 2 db tables

emp(empID, empname, deptID)
dept(deptID, deptName)

I have the following view that is created successfully

Create view E_VIEW as
Select empName, deptName
From emp, dept
Where emp.deptID = dept.deptID;



I have the following store procedure created successfully that calls and inserts into the above view, the empname and deptName.

CREATE or REPLACE procedure INSERT_TO_VIEW(
p_ename in varchar2,
p_dname in varchar)
IS
BEGIN
INSERT into E_VIEW values(p_ename, p_dname);
commit;
end;
/

However my calling routine with the following code generates the below mentioned error

Begin
INSERT_TO_VIEW('Mark', 'Sales');
end;
/

ORA-1779 error cannot modify a column which maps to a non key

One thing I want to mention is that the empID in the emp table is a non null field and is generated by a sequence next val (EmpSeq.nextval).

Do I need to take care of this or is the error due to something else??

3
PSOUG / INSERT statement with a SELECT statement in a store procedure
« on: April 12, 2009, 10:19:11 AM »
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

Pages: [1]