Messages

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

PSOUG / Re: INSERT statement with a SELECT statement in a store procedure

« 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.

3

PSOUG / Re: INSERT statement with a SELECT statement in a store procedure

« 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.

4

PSOUG / Re: ORA 01779 : cannot modify a column that maps to a non key preserved table

« on: April 14, 2009, 01:36:02 pm »

This has been a very beneficial and pleasant interaction. All the code works just fine. I saw the code for my earlier question. I did it slightly differently yesterday and got to work. I have posted it there. Please critique it. Good to know the other way. Thanks David again for all the help.

5

PSOUG / Re: ORA 01779 : cannot modify a column that maps to a non key preserved table

« on: April 14, 2009, 11:37:50 am »

Thanks again David,
Since I am a newbie, looks like my questions evolve with answers. Last one I have is
1. how do we manage to add a new employee to either an existing or a totally new department at the same time.  All we need to do is to supply the emp_name and dept_name. The values should get inserted by either creating a new deptName(and deptID)or by using an existing dept name. I assume we need the earlier newdept trigger, but the combination may conflict if deptname already exists.
Thanks

6

PSOUG / Re: ORA 01779 : cannot modify a column that maps to a non key preserved table

« on: April 14, 2009, 10:43:39 am »

I finally got everything to execute successfully. Sorry about my 2 earlier posts where I posted regarding some errors. Theye were due to some spell check issues. I apologize.

I have a different issue though. The current code allows me to add a new employee and a new dept for that employee successfully. I want to be able to add a new employee to an existing dept. When I try doing that I get an error
ORA-01422: exact fetch returns more than requested number of rows
ORA -04088 : error during execution of trigger e_view_insert
ORA -06512: at lines 6 and 9 which insert the new dept name in the new dept table.
Thanks David

7

PSOUG / Re: ORA 01779 : cannot modify a column that maps to a non key preserved table

« on: April 14, 2009, 10:08:33 am »

I am  getting an error "ORA-01733: virtual column not allowed here " at the 14th line of the e-view-insert trigger. I am using the entire code. Everything else executes successfully. Only at this I see those errors.

8

PSOUG / Re: ORA 01779 : cannot modify a column that maps to a non key preserved table

« on: April 14, 2009, 09:38:02 am »

David,
Thanks very much. I now have a clear idea. 
One thing I wanted to clarify is that  do we STILL have to have a trigger for  dept table in case I want to conduct NEW INSERT operations on EMP in  an existing department? All I want to do is to be able to add new employees to an existing dept.

Thanks once again

9

PSOUG / ORA 01779 : cannot modify a column that maps to a non key preserved table

« on: April 13, 2009, 09:57:19 pm »

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??

10

PSOUG / Re: INSERT statement with a SELECT statement in a store procedure

« 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'

11

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