Home » SQL & PL/SQL » SQL & PL/SQL » insert value into table is not working
insert value into table is not working [message #38196] Sun, 31 March 2002 06:38 Go to next message
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 Go to previous messageGo to next message
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
Re: insert value into table is not working [message #38203 is a reply to message #38196] Mon, 01 April 2002 08:17 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You can use named parameter references:

exec new_emp('HARRIS', v_deptno => 30);
Previous Topic: ABOUT PROCEDURE
Next Topic: Locally Managed TableSpaces
Goto Forum:
  


Current Time: Thu Mar 28 09:25:12 CDT 2024