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;
/

You cannot execute DML inside a function without defining `PRAGMA AUTONOMOUS_TRANSACTION` in your declaration. You can add that to your function, or make this a procedure, which is what it should probably be anyway. — WoMo, Jun 04 '14 at 15:06
Is there definitely a column USER in the table and does a row exist where the value is the same as `P_USER`? P.S. your function isn't returning anything so this won't even compile... — Ben, Jun 04 '14 at 19:58
Dear all, thank you for your comments. Kindly note that this is a simplified example of my problem. It has nothing to do with USER. USER is only used as a replacement for subno. The problem is that when I output the variable in dbms_output.put_line it prints it in DBMS output, but when I update with the same variable in table. it updates nothing. — Sarwar, Jun 05 '14 at 06:14
@Wolf That's only true if the function is called from SQL, and even then it might perform DML on a different table if it's called from an UPDATE or DELETE, see http://psoug.org/snippet/FUNCTIONS-special-restrictions_868.htm — Frank Schmitt, Jun 05 '14 at 06:21
@Sarwar Then update your question. It doesn't make sense to paste code that does not reproduce your problem. Post a *minimal*, *reproducible* example with table definitions, content and expected outcome. — Frank Schmitt, Jun 05 '14 at 06:23
Update: It does not work in Toad but does in Oracle SQL Developer — Sarwar, Jun 05 '14 at 07:02
We don't know what doesn't work at the moment @Sarwar... if your question remains the same you've got the correct answer already. As that isn't your actual question I can't see how anyone can help you. — Ben, Jun 05 '14 at 07:27

score 1 · Answer 1 · answered Jun 04 '14 at 19:55

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

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

1 Answers1