Error when trying to create a procedure

[thomasp]

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

[thomasp]

basically what I trying to do is give new id values for existing ones.

Is there anything wrong if I try to update the primary key?
id_no is the primary key in this table.

can I give

update company_details set id_no='newid' where id_no='oldid';

is it right to do so or does this have any impact?

thanks a lot

[jmodic]

Originally posted by thomasp
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

You can't list more than one column/expression in a select statement of the VALUES clause - it's not PL/SQL's restriction, it is SQL's restriction.

However I don't understand why you are using such a complicated form of an insert statement at all. You should change your second procedure to something like this:

create or replace procedure change_id_no(p_oldidno in varchar2, p_newidno in varchar2)
as
begin
insert into company_details (id_no, registration_no, company_name)
select p_newidno, registration_no, company_name
from company_details where id_no = p_oldidno;
end change_id_no;

[thomasp]

thanks jmodic, but I'm still getting an error saying missing left parenthesis.

My procedure would really look like this:

CREATE OR REPLACE PROCEDURE Change_id_No(p_oldidno in varchar2,p_newidno in varchar2)
as
BEGIN
INSERT INTO company_details (ID_NO, REGISTRATION_NO, SORT_CODE, COUNTRY_CODE, COMPANY_NAME, LIFE_STATUS, COMPANY_ADDRESS_STREET,
COMPANY_ADDRESS_CITY, COMPANY_ADDRESS_STATE, COMPANY_ADDRESS_ZIPCODE, COMPANY_ADMIN_STREET, COMPANY_ADMIN_CITY,
COMPANY_ADMIN_STATE, COMPANY_ADMIN_ZIPCODE, TELEPHONE_NO, FAX_NO, EMAIL, WEB_ADDRESS, ACTIVE_STATUS, COMPANY_NOTES,
COMPANY_NAME_CHANGES, id_RATING, LOCKED, LOCKED_BY, COMPANY_ADMIN_COUNTRY_CODE, CONTACT_NAME, ALTERNATIVE_COMPANY_NAME,
PRIORITY, COMPANY_ADDRESS_STREET_2, LOCKED_TIME, FSA_AC_NO)
VALUES
SELECT p_newidno, REGISTRATION_NO, SORT_CODE, COUNTRY_CODE, COMPANY_NAME, LIFE_STATUS, COMPANY_ADDRESS_STREET,
COMPANY_ADDRESS_CITY, COMPANY_ADDRESS_STATE, COMPANY_ADDRESS_ZIPCODE, COMPANY_ADMIN_STREET, COMPANY_ADMIN_CITY,
COMPANY_ADMIN_STATE, COMPANY_ADMIN_ZIPCODE, TELEPHONE_NO, FAX_NO, EMAIL, WEB_ADDRESS, ACTIVE_STATUS, COMPANY_NOTES,
COMPANY_NAME_CHANGES, id_RATING, LOCKED, LOCKED_BY, COMPANY_ADMIN_COUNTRY_CODE, CONTACT_NAME, ALTERNATIVE_COMPANY_NAME,
PRIORITY, COMPANY_ADDRESS_STREET_2, LOCKED_TIME, FSA_AC_NO FROM COMPANY_DETAILS WHERE id_NO = 'p_oldidno';

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
9/8 PL/SQL: ORA-00906: missing left parenthesis

what do you think about the update stmt? will that work???

thanks a lot

[jmodic]

Originally posted by thomasp
...
PRIORITY, COMPANY_ADDRESS_STREET_2, LOCKED_TIME, FSA_AC_NO)
VALUES
SELECT p_newidno, REGISTRATION_NO, SORT_CODE, COUNTRY_CODE,
...

Remove the line "VALUES", ie change the above to:

...
PRIORITY, COMPANY_ADDRESS_STREET_2, LOCKED_TIME, FSA_AC_NO)
SELECT p_newidno, REGISTRATION_NO, SORT_CODE, COUNTRY_CODE,
...

[thomasp]

thanks a lot for the help, that worked.