ORACLE PL/SQL: Calling stored procedure function with multiple parameters (DML query)

Question

New PL/SQL person here. I have a (successfully compiled) PL/SQL function block that manipulates a table in my database by adding a new term to it:

create or replace FUNCTION add_new_term
    (TERM_ID_IN IN NUMBER, TERM_IN IN VARCHAR2, IS_METATERM_IN IN NUMBER)
    RETURN VARCHAR2
IS
    add_term CV_TERMS.TERM_NAME%TYPE; --TERM_NAME is VARCHAR2 type
BEGIN
    INSERT INTO CV_TERMS (TERM_ID, TERM_NAME, IS_METATERM)
    VALUES (TERM_ID_IN, TERM_IN, IS_METATERM_IN);
    dbms_output.put_line('New term successfully added to CV_TERMS table: ' || TERM_IN);
    RETURN add_term;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        raise_application_error (-20001, 'You have tried to insert a duplicate term.');
    WHEN OTHERS THEN    
        raise_application_error (-20002, 'An error has occurred inserting a term - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
END add_new_term;

I call this function like calling a stored procedure:

DECLARE
  add_term_success cv_terms.term_name%type;
BEGIN
  add_term_success := add_new_term(cv_terms_pk.NEXTVAL, 'TESTTT', 0);
END;

SQLDeveloper tells me the procedure was successfully completed, however, the term has not been added to the table. I created the sequence cv_terms_pk independently (it's not in the table CV_TERMS' SQL). Does it need to be there? Am I passing it improperly? Or is something wrong with my add_term declaration? Ideas?

Answer 1

After the DML INSERT you have to commit the transaction.

create or replace FUNCTION add_new_term
(TERM_ID_IN IN NUMBER, TERM_IN IN VARCHAR2, IS_METATERM_IN IN NUMBER)
RETURN VARCHAR2 IS
add_term CV_TERMS.TERM_NAME%TYPE; --TERM_NAME is VARCHAR2 type

BEGIN INSERT INTO CV_TERMS(TERM_ID, TERM_NAME, IS_METATERM VALUES (TERM_ID_IN, TERM_IN, IS_METATERM_IN); COMMIT; ---LINE ADDED ...