Forum Stats

  • 3,838,627 Users
  • 2,262,385 Discussions
  • 7,900,712 Comments

Discussions

i am new to pl/sql help me get to solve this error

Aravind Kumar Sekar
Aravind Kumar Sekar Member Posts: 27
edited Sep 16, 2015 10:10AM in SQL & PL/SQL

the syntax for creating the HOME table and home details

create table home (F_NAME VARCHAR2(25),M_NAME VARCHAR2(25),C_NAME VARCHAR2(25));


create table home (F_NAME VARCHAR2(25),M_NAME VARCHAR2(25),C_NAME VARCHAR2(25));

the program for trigger

CREATE OR REPLACE TRIGGER HOME_BEFORE_DELETE

BEFORE INSERT

ON HOME

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(25);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO HOME_DETAILS

(F_NAME, M_NAME, C_NAME  )

values

(:old.F_NAME,

:old.M_NAME,

:old.C_NAME ,V_USERNAME);

END



when executing it shows

ORA-24344: success with compilation error

Tagged:
William Robertson

Best Answer

  • Manjusha Muraleedas
    Manjusha Muraleedas Member Posts: 230
    edited Sep 16, 2015 12:56AM Answer ✓

    Hi
    Insertion to table home details is incorrect.

    You specified only 3 columns F_NAME, M_NAME, C_NAME in column list and gave 4 values in value list.Please correct it.

    INSERT INTO HOME_DETAILS

    (F_NAME, M_NAME, C_NAME  )

    values

    (:old.F_NAME,

    :old.M_NAME,

    :old.C_NAME ,V_USERNAME);


    Regards.

    Manjusha

Answers

  • Manjusha Muraleedas
    Manjusha Muraleedas Member Posts: 230
    edited Sep 16, 2015 12:56AM Answer ✓

    Hi
    Insertion to table home details is incorrect.

    You specified only 3 columns F_NAME, M_NAME, C_NAME in column list and gave 4 values in value list.Please correct it.

    INSERT INTO HOME_DETAILS

    (F_NAME, M_NAME, C_NAME  )

    values

    (:old.F_NAME,

    :old.M_NAME,

    :old.C_NAME ,V_USERNAME);


    Regards.

    Manjusha

  • Unknown
    edited Sep 16, 2015 12:59AM

    [[email protected] ~]$ oerr ora 24344

    24344, 00000, "success with compilation error"

    // *Cause:  A sql/plsql compilation error occurred.

    // *Action: Return OCI_SUCCESS_WITH_INFO along with the error code

    //

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Sep 16, 2015 9:18AM

    There are other issues with your trigger in addition to the column list discrepancy.  You name the trigger HOME_BEFORE_DELETE, but define it as before insert.  The old values that you reference in the trigger body are not available in a before insert trigger.

    John

    William Robertson
  • UserMr
    UserMr Member Posts: 41 Blue Ribbon
    edited Sep 16, 2015 10:10AM

    Your code should be like following:

    Create tables:

    create table home (F_NAME VARCHAR2(25),M_NAME VARCHAR2(25),C_NAME VARCHAR2(25));

    create table home_details (F_NAME VARCHAR2(25),M_NAME VARCHAR2(25),C_NAME VARCHAR2(25),V_USERNAME varchar2(25));

    Create Triggers:

    CREATE OR REPLACE TRIGGER HOME_BEFORE_DELETE

    BEFORE INSERT

    ON HOME

    FOR EACH ROW

    DECLARE

    V_USERNAME VARCHAR2(25);

    BEGIN

    SELECT USER INTO V_USERNAME FROM DUAL;

    INSERT INTO HOME_DETAILS

    (F_NAME, M_NAME, C_NAME,V_USERNAME  )

    values

    (:new.F_NAME,

    :new.M_NAME,

    :new.C_NAME ,V_USERNAME);

    END;

    Test:

    insert into home values('a','b','c');
    commit;

    select * from home_details

    Output:

    a b c user1

    Aravind Kumar Sekar
This discussion has been closed.