I am trying to create a trigger which will insert the record with some more data but my trigger creation is throwing compilation error
[Warning] ORA-24344: success with compilation error
103/9 PL/SQL: ORA-00913: too many values
16/9 PL/SQL: SQL Statement ignored
(1: 0): Warning: compiled but with compilation errors
Below is my trigger code
CREATE OR REPLACE trigger insert_into_out_customer
before insert
on out_customer
for each row
DECLARE
v_username out_customer.created_by%TYPE;
newRow out_customer%ROWTYPE;
cId out_customer.CUSTOMER_ID%TYPE;
begin
SELECT user INTO v_username
FROM dual;
:new.created_by := v_username;
:new.updated_by := v_username;
cId := :new.CUSTOMER_ID;
SELECT
SAS_FATCA_CUSTOMER.CUSTOMER_ID ,
CUSTOMER_TYPE ,
CUSTOMER_SUB_TYPE ,
FIRST_NAME ,
MIDDLE_NAME ,
LAST_NAME ,
DOB ,
PASSPORT_NUMBER ,
GREEN_CARD_HOLDER ,
GREEN_CARD_NUMBER ,
TIN ,
PRIMARY_ADDR_LINE_1 ,
PRIMARY_ADDR_LINE_2 ,
PRIMARY_ADDR_CITY ,
PRIMARY_ADDR_STATE ,
PRIMARY_ADDR_POSTAL_CODE ,
PRIMARY_ADDR_COUNTRY ,
PRIMARY_ADDR_PHONE_COUNTRY_CD ,
PRIMARY_ADDR_PHONE_NUMBER ,
PRIMARY_EMAIL ,
SECONDARY_ADDR_LINE_1 ,
SECONDARY_ADDR_LINE_2 ,
SECONDARY_ADDR_CITY ,
SECONDARY_ADDR_STATE ,
SECONDARY_ADDR_POSTAL_CODE ,
SECONDARY_ADDR_COUNTRY ,
SECONDARY_ADDR_PHONE_CNTRY_CD ,
SECONDARY_ADDR_PHONE_NUMBER ,
SECONDARY_EMAIL ,
CITIZENSHIP ,
COUNTRY_OF_ORIGIN ,
NATIONALITY_1 ,
NATIONALITY_2 ,
BANK_STAFF_ID ,
SELF_CERTIFICATION_RESPONSE ,
SELF_CERTIFICATION_REASON ,
SELF_CERTIFICATION_TIMESTAMP ,
'EDIT_FLAG' ,
POA_EXISTS ,
POA_FIRST_NAME ,
POA_LAST_NAME ,
POA_DOB ,
POA_ADDR_LINE_1 ,
POA_ADDR_LINE_2 ,
POA_CITY ,
POA_STATE ,
POA_POSTAL_CODE ,
POA_COUNTRY ,
POA_COUNTRY_OF_ORIGIN ,
POA_PHONE_COUNTRY_CODE ,
POA_PHONE_NUMBER ,
BENEFICIARY_BANK_ADDRESS_LINE1 ,
BENEFICIARY_BANK_ADDRESS_LINE2 ,
BENEFICIARY_BANK_CITY ,
BENEFICIARY_BANK_POSTAL_CODE ,
BENEFICIARY_BANK_COUNTRY ,
BENEFICIARY_BANK_IFSC_CODE ,
'ACCOUNT_NUMBER' ,
'ACCOUNT_NAME' ,
'ACCOUNT_OPEN_DATE' ,
'BASE_CURRENCY ' ,
' ACCOUNT_BALANCE_BASE_CURRENCY' ,
' ACCOUNT_BALANCE_US_CURRENCY' ,
'GROSS_WITHDRAWALS' ,
' GROSS_PROCEEDS' ,
'GROSS_DIVIDENDS' ,
'GROSS_INTERESTS' ,
'OTHER_INCOME' ,
sysdate
POA_ID ,
STANDING_INSTRUCTION_ID ,
BENEFICIARY_ACCOUNT_ID ,
BENEFICIARY_ACCOUNT_NUMBER ,
BENEFICIARY_ACCOUNT_NAME ,
BENEFICIARY_BANK_NAME ,
STANDING_INSTRUCTION_START_DT ,
STANDING_INSTRUCTION_END_DATE ,
STANDING_INS_AMOUNT ,
STANDING_INSTRC_FREQUENCY ,
:new.FATCA_REASON ,
:new.fatca_flag ,
SAS_FATCA_CUSTOMER.CREATED_BY ,
SAS_FATCA_CUSTOMER.CREATED_DT ,
SAS_FATCA_CUSTOMER.UPDATED_BY ,
SAS_FATCA_CUSTOMER.UPDATED_DT
into newRow
FROM SAS_FATCA_CUSTOMER
LEFT JOIN SAS_FATCA_ATTORNEY ON SAS_FATCA_CUSTOMER.CUSTOMER_ID = SAS_FATCA_ATTORNEY.CUSTOMER_ID
LEFT JOIN SAS_FATCA_STANDING_INSTRUCTION ON SAS_FATCA_CUSTOMER.CUSTOMER_ID = SAS_FATCA_STANDING_INSTRUCTION.CUSTOMER_ID
where SAS_FATCA_CUSTOMER.CUSTOMER_ID= cId and rownum<2;
:new.CUSTOMER_TYPE := newRow.CUSTOMER_TYPE ;
:new.CUSTOMER_SUB_TYPE := newRow.CUSTOMER_SUB_TYPE ;
:new.FIRST_NAME := newRow.FIRST_NAME ;
:new.MIDDLE_NAME := newRow.MIDDLE_NAME ;
:new.LAST_NAME := newRow.LAST_NAME ;
:new.DOB := newRow.DOB ;
:new.PASSPORT_NUMBER := newRow.PASSPORT_NUMBER ;
:new.GREEN_CARD_HOLDER := newRow.GREEN_CARD_HOLDER ;
:new.GREEN_CARD_NUMBER := newRow.GREEN_CARD_NUMBER ;
:new.TIN := newRow.TIN ;
:new.PRIMARY_ADDR_LINE_1 := newRow.PRIMARY_ADDR_LINE_1 ;
:new.PRIMARY_ADDR_LINE_2 := newRow.PRIMARY_ADDR_LINE_2 ;
:new.PRIMARY_ADDR_CITY := newRow.PRIMARY_ADDR_CITY ;
:new.PRIMARY_ADDR_STATE := newRow.PRIMARY_ADDR_STATE ;
:new.PRIMARY_ADDR_POSTAL_CODE := newRow.PRIMARY_ADDR_POSTAL_CODE ;
:new.PRIMARY_ADDR_COUNTRY := newRow.PRIMARY_ADDR_COUNTRY ;
:new.PRIMARY_ADDR_PHONE_COUNTRY_CD := newRow.PRIMARY_ADDR_PHONE_COUNTRY_CD ;
:new.PRIMARY_ADDR_PHONE_NUMBER := newRow.PRIMARY_ADDR_PHONE_NUMBER ;
:new.PRIMARY_EMAIL := newRow.PRIMARY_EMAIL ;
:new.SECONDARY_ADDR_LINE_1 := newRow.SECONDARY_ADDR_LINE_1 ;
:new.SECONDARY_ADDR_LINE_2 := newRow.SECONDARY_ADDR_LINE_2 ;
:new.SECONDARY_ADDR_CITY := newRow.SECONDARY_ADDR_CITY ;
:new.SECONDARY_ADDR_STATE := newRow.SECONDARY_ADDR_STATE ;
:new.SECONDARY_ADDR_POSTAL_CODE := newRow.SECONDARY_ADDR_POSTAL_CODE ;
:new.SECONDARY_ADDR_COUNTRY := newRow.SECONDARY_ADDR_COUNTRY ;
:new.SECONDARY_ADDR_PHONE_CNTRY_CD := newRow.SECONDARY_ADDR_PHONE_CNTRY_CD ;
:new.SECONDARY_ADDR_PHONE_NUMBER := newRow.SECONDARY_ADDR_PHONE_NUMBER ;
:new.SECONDARY_EMAIL := newRow.SECONDARY_EMAIL ;
:new.CITIZENSHIP := newRow.CITIZENSHIP ;
:new.COUNTRY_OF_ORIGIN := newRow.COUNTRY_OF_ORIGIN ;
:new.NATIONALITY_1 := newRow.NATIONALITY_1 ;
:new.NATIONALITY_2 := newRow.NATIONALITY_2 ;
:new.BANK_STAFF_ID := newRow.BANK_STAFF_ID ;
:new.SELF_CERTIFICATION_RESPONSE := newRow.SELF_CERTIFICATION_RESPONSE ;
:new.SELF_CERTIFICATION_REASON := newRow.SELF_CERTIFICATION_REASON ;
:new.SELF_CERTIFICATION_TIMESTAMP := newRow.SELF_CERTIFICATION_TIMESTAMP ;
:new.POA_EXISTS := newRow.POA_EXISTS ;
:new.POA_FIRST_NAME := newRow.POA_FIRST_NAME ;
:new.POA_LAST_NAME := newRow.POA_LAST_NAME ;
:new.POA_DOB := newRow.POA_DOB ;
:new.POA_ADDR_LINE_1 := newRow.POA_ADDR_LINE_1 ;
:new.POA_ADDR_LINE_2 := newRow.POA_ADDR_LINE_2 ;
:new.POA_CITY := newRow.POA_CITY ;
:new.POA_STATE := newRow.POA_STATE ;
:new.POA_POSTAL_CODE := newRow.POA_POSTAL_CODE ;
:new.POA_COUNTRY := newRow.POA_COUNTRY ;
:new.POA_COUNTRY_OF_ORIGIN := newRow.POA_COUNTRY_OF_ORIGIN ;
:new.POA_PHONE_COUNTRY_CODE := newRow.POA_PHONE_COUNTRY_CODE ;
:new.POA_PHONE_NUMBER := newRow.POA_PHONE_NUMBER ;
:new.BENEFICIARY_BANK_ADDRESS_LINE1 := newRow.BENEFICIARY_BANK_ADDRESS_LINE1 ;
:new.BENEFICIARY_BANK_ADDRESS_LINE2 := newRow.BENEFICIARY_BANK_ADDRESS_LINE2 ;
:new.BENEFICIARY_BANK_CITY := newRow.BENEFICIARY_BANK_CITY ;
:new.BENEFICIARY_BANK_POSTAL_CODE := newRow.BENEFICIARY_BANK_POSTAL_CODE ;
:new.BENEFICIARY_BANK_COUNTRY := newRow.BENEFICIARY_BANK_COUNTRY ;
:new.BENEFICIARY_BANK_IFSC_CODE := newRow.BENEFICIARY_BANK_IFSC_CODE;
commit;
end;
EDIT 1: below Query is working fine which means out_customer has all the columns
SELECT CUSTOMER_ID ,
CUSTOMER_TYPE ,
CUSTOMER_SUB_TYPE ,
FIRST_NAME ,
MIDDLE_NAME ,
LAST_NAME ,
DOB ,
PASSPORT_NUMBER ,
GREEN_CARD_HOLDER ,
GREEN_CARD_NUMBER ,
TIN ,
PRIMARY_ADDR_LINE_1 ,
PRIMARY_ADDR_LINE_2 ,
PRIMARY_ADDR_CITY ,
PRIMARY_ADDR_STATE ,
PRIMARY_ADDR_POSTAL_CODE ,
PRIMARY_ADDR_COUNTRY ,
PRIMARY_ADDR_PHONE_COUNTRY_CD ,
PRIMARY_ADDR_PHONE_NUMBER ,
PRIMARY_EMAIL ,
SECONDARY_ADDR_LINE_1 ,
SECONDARY_ADDR_LINE_2 ,
SECONDARY_ADDR_CITY ,
SECONDARY_ADDR_STATE ,
SECONDARY_ADDR_POSTAL_CODE ,
SECONDARY_ADDR_COUNTRY ,
SECONDARY_ADDR_PHONE_CNTRY_CD ,
SECONDARY_ADDR_PHONE_NUMBER ,
SECONDARY_EMAIL ,
CITIZENSHIP ,
COUNTRY_OF_ORIGIN ,
NATIONALITY_1 ,
NATIONALITY_2 ,
BANK_STAFF_ID ,
SELF_CERTIFICATION_RESPONSE ,
SELF_CERTIFICATION_REASON ,
SELF_CERTIFICATION_TIMESTAMP ,
EDIT_FLAG ,
POA_EXISTS ,
POA_FIRST_NAME ,
POA_LAST_NAME ,
POA_DOB ,
POA_ADDR_LINE_1 ,
POA_ADDR_LINE_2 ,
POA_CITY ,
POA_STATE ,
POA_POSTAL_CODE ,
POA_COUNTRY ,
POA_COUNTRY_OF_ORIGIN ,
POA_PHONE_COUNTRY_CODE ,
POA_PHONE_NUMBER ,
BENEFICIARY_BANK_ADDRESS_LINE1 ,
BENEFICIARY_BANK_ADDRESS_LINE2 ,
BENEFICIARY_BANK_CITY ,
BENEFICIARY_BANK_POSTAL_CODE ,
BENEFICIARY_BANK_COUNTRY ,
BENEFICIARY_BANK_IFSC_CODE ,
ACCOUNT_NUMBER ,
ACCOUNT_NAME ,
ACCOUNT_OPEN_DATE ,
BASE_CURRENCY ,
ACCOUNT_BALANCE_BASE_CURRENCY ,
ACCOUNT_BALANCE_US_CURRENCY ,
GROSS_WITHDRAWALS ,
GROSS_PROCEEDS ,
GROSS_DIVIDENDS ,
GROSS_INTERESTS ,
OTHER_INCOME ,
BALANCE_DATE ,
POA_ID ,
STANDING_INSTRUCTION_ID ,
BENEFICIARY_ACCOUNT_ID ,
BENEFICIARY_ACCOUNT_NUMBER ,
BENEFICIARY_ACCOUNT_NAME ,
BENEFICIARY_BANK_NAME ,
STANDING_INSTRUCTION_START_DT ,
STANDING_INSTRUCTION_END_DATE ,
STANDING_INS_AMOUNT ,
STANDING_INSTRC_FREQUENCY ,
FATCA_REASON ,
FATCA_FLAG ,
CREATED_BY ,
CREATED_DT ,
UPDATED_BY ,
UPDATED_DT
from out_customer
I am not sure if this error is because of I am using %ROWTYPE variable to store query result. Please help me. And also if this is because of %ROWTYPE then what else I can use in place of %ROWTYPE
Confusingly in this case the error is because your select's projection has too few columns, not too many:
create table t42 (col1 number, col2 number);
declare
x t42%rowtype;
begin
select 1 into x from dual;
end;
/
Error report -
ORA-06550: line 4, column 19:
PL/SQL: ORA-00913: too many values
If you had too many columns in your select then you'd get ORA-00947: not enough values. Presumably this is based on the rowtype variable having too many columns compared to the select, rather than the other way round as you might expect.
In your case you are missing a comma. At the moment POA_ID is an alias for the sysdate, not a separate column. So this:
'OTHER_INCOME' ,
sysdate
POA_ID ,
should be:
'OTHER_INCOME' ,
sysdate,
POA_ID ,
Incidentally, this two-step process of selecting everything including values you already know into a rowtype variable, and then individually copy some of those values into the :new pseudorow columns, seems unnecessarily complicated, and may break if the table structure changes or the columns are not in exactly the order you expect. (Which can happen moving between environments; though it may be a sign you're doing some wrong).
Why not directly set the fixed values:
:new.created_by := user;
:new.updated_by := user;
:new.some_column := sysdate;
:new.some_column := 'EDIT FLAG';
...
and then only select the values you really have to get from other tables directly into a list of :new variables?
You also cannot commit inside a trigger.