Error when trying to create a procedure
I'm trying to write a procedure to do an insert.
The first one works fine.
1 create or replace procedure change_id_no(p_oldidno in varchar2, p_newidno in varchar2)
2 as
3 begin
4 insert into company_details (id_no, registration_no)
5 values
6 (p_newidno, (select registration_no from company_details where id_no = 'p_oldidno'));
7 end change_id_no;
SQL> /
Procedure created.
But when I try this (ie, when I have more than one value in the select stmt), it tends to fail.
CAn you tell me what's wrong with this statement and how should I be writing it?
1 create or replace procedure change_id_no(p_oldidno in varchar2, p_newidno in varchar2)
2 as
3 begin
4 insert into company_details (id_no, registration_no, company_name)
5 values
6 (p_newidno, (select registration_no, company_name from company_details where id_no = 'p_oldidno'));
7 end change_id_no;
SQL> /
Warning: Procedure created with compilation errors.
SQL> sho errors
Errors for PROCEDURE CHANGE_ID_NO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/66 PL/SQL: ORA-00947: not enough values
Thanks a lot