insert value into table is not working [message #38196] |
Sun, 31 March 2002 06:38 |
Sid
Messages: 38 Registered: May 1999
|
Member |
|
|
I want to create a procedure that inserts a new row into the employee table. It takes some default values that is if the user has decided to leave out some of the values.
The new_emp procedure calls a function called valid_deptno which returns a boolean value if the deptno exists in the dept table. This function has been tested and works. At end of message I have provided the function code for convenience. Note problem occurs in new_emp procedure.
Here is a synopsis of the main columns in the employer table.
EMPNO ENAME DEPTNO
--------- ---------- ---------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
CREATE OR REPLACE procedure new_emp
( v_name IN emp.ename%TYPE , v_job IN emp.job%TYPE default 'SALESMAN',
v_mgr IN emp.mgr%TYPE default 7839, v_hiredate IN emp.hiredate%TYPE default SYSDATE,
v_sal IN emp.sal%TYPE default '1000', v_comm IN emp.comm%TYPE default 0,
v_deptno IN emp.deptno%TYPE default 30
)
IS
BEGIN
IF ( valid_deptno( v_deptno) ) THEN
insert into emp
values(emp.empno.NEXTVAL, v_name, v_job, v_mgr, v_hiredate, sys.date, v_sal, v_comm, v_deptno);
ELSE
DBMS_OUTPUT.PUT_LINE('You have entered an incorrect department number please try again');
END IF;
END;
/
The error generated by oracle is:
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5 PL/SQL: SQL Statement ignored
11/22 PLS-00487: Invalid reference to variable 'EMPNO'
The error refers to emp.empno.NEXTVAL in the insert clause. I can't recall how to generate a sequence number.
I think there is a command similar to NEXTVAL?
I think there could be a sequence command similar to SEQ_EMPNO?
Any way how would I generate an unique number that is not in the employer table?
Below is valid_deptno
CREATE OR REPLACE FUNCTION valid_deptno
(v_deptno IN dept.deptno%TYPE)
RETURN BOOLEAN
IS
v_result dept.deptno%type;
BEGIN
select deptno
into v_result
from dept
where deptno = v_deptno;
return (true);
EXCEPTION
when no_data_found then
return (false);
end valid_deptno;
/
|
|
|
Re: insert value into table is not working [message #38198 is a reply to message #38196] |
Sun, 31 March 2002 12:54 |
Sid
Messages: 38 Registered: May 1999
|
Member |
|
|
The solution to my woes has been solved with sequence. But how can I test default values in procedure?
--create sequence number
create sequence empno increment by 1 start with 8100;
--create procedure
CREATE OR REPLACE procedure new_emp
( v_name IN empy.ename%TYPE , v_job IN empy.job%TYPE default 'SALESMAN',
v_mgr IN empy.mgr%TYPE default 7839, v_hiredate IN empy.hiredate%TYPE default SYSDATE,
v_sal IN empy.sal%TYPE default '1000', v_comm IN empy.comm%TYPE default 0,
v_deptno IN empy.deptno%TYPE default 30
)
IS
BEGIN
IF ( valid_deptno( v_deptno) ) THEN
insert into empy
values(empno.NEXTVAL, v_name, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
ELSE
DBMS_OUTPUT.PUT_LINE('You have entered an incorrect department number please try again');
END IF;
END;
/
How I can test the default values with above code
EXECUTE new_emp('HARRIS', , , , , , 30);
ORACLE error:
*
ERROR at line 1:
ORA-06550: line 1, column 25:
PLS-00103: Encountered the symbol "," when expecting one of the following:
( - + mod not null others
avg count exists max
min prior sql stddev sum variance
|
|
|
|