dbms_output.put_line shows variable value but does not save to database

Question

I am trying to save a value in a table after removing an & in the input variable. When I print the value of the variable in dbms_ouput.put_line I get the output. but when I save the value of the variable to database, nothing is updated. Code below is a simplified version. Using toad 10.6 on oracle 11g

FUNCTION UPDATE_ADDRESS(ADDRESS VARCHAR2,
                    P_USER  VARCHAR2)
RETURN VARCHAR2 IS
    RetVal                VARCHAR2(32767);
    V_ADDRESS   VARCHAR2(32767);
BEGIN
    DBMS_OUTPUT.put_line('ADDRESS ::=' || ADDRESS); --this prints
    V_ADDRESS := REPLACE(ADDRESS, '&', '');
    DBMS_OUTPUT.put_line('V_ADDRESS ::=' || V_ADDRESS); --so does this

    UPDATE ADDRESS_TABLE
    SET ADDRESS = SUBSTR(V_ADDRESS, 1, 255) --this column does not get updated
    WHERE USER = P_USER;
    COMMIT;
END;
/

Answer 1

your column name USER conflicts with internal function with the same name, make it ADDRESS_TABLE.USER

@Wolf

tmp_func has DML operation and no PRAGMA. Just working....

create table tmp1 as select 1 n from dual;

create or replace function tmp_func return number is
v_n number;
begin
  update tmp1 set n = n + 1
  returning n into v_n;
  return v_n;
end;
/

declare
v_n number;
begin
v_n := tmp_func();
dbms_output.put_line(v_n);
end;
/

Output:

table TMP1 created.
FUNCTION TMP_FUNC compiled
anonymous block completed

2