Oracle, calling PL/SQL issues from within SQL-Plus file x.sql says my_function "may not be a function"

Question

so simple, if I create my function as CREATE OR REPLACE FUNCTION MD5_ENCODE it will run smoothly, but if it stays anonymously within the SQL-Plus block as PL/SQL --> "may not be a function" error.

What is this Oracle "feature" again?

DECLARE

FUNCTION MD5_ENCODE(CLEARTEXT IN VARCHAR2) RETURN VARCHAR2 IS    
    CHK VARCHAR2(16);   
    HEX VARCHAR2(32);  
    I   INTEGER;  
    C   INTEGER;  
    H   INTEGER;  
BEGIN
    IF CLEARTEXT IS NULL THEN
        RETURN '';
    ELSE
        CHK := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING
=> CLEARTEXT);
        FOR I IN 1 .. 16 LOOP
            C := ASCII(SUBSTR(CHK, I, 1));
            H := TRUNC(C / 16);
            IF H >= 10 THEN
                HEX := HEX || CHR(H + 55);
            ELSE
                HEX := HEX || CHR(H + 48);
            END IF;
            H := MOD(C, 16);
            IF H >= 10 THEN
                HEX := HEX || CHR(H + 55);
            ELSE
                HEX := HEX || CHR(H + 48);
            END IF;
        END LOOP;  
        RETURN HEX;  
    END IF;  

END;  

BEGIN  
    UPDATE ADDRESSES_T SET STREET = MD5ENCODE(STREET) ;  

-- etc...  
END   

Answer 1

http://forums.oracle.com/forums/thread.jspa?threadID=245112

There are a number of things it could be.

My #1 candidate is, we can only use functions in SQL statements that are public i.e. declared in a package spec. This is the case even for SQL statements executed within the same Package Body. The reson is that SQL statements are executed by a different engine which can only see publicly declared functions.

---

Long story short, function must be defined in a package, like CREATE OR REPLACE FUNCTION MD5ENCODE(IN_TEXT IN VARCHAR2) RETURN VARCHAR2 IS ...

Answer 2

In order to call a function within a SQL statement, it needs to exist as a function object within the database. The SQL statement is parsed and executed separately from your PL/SQL code, so it doesn't have access to the locally defined function.

If you really don't want to create the function object, you could use a cursor to loop over the rows in the table, execute the function in a PL/SQL statement, and update each row as you go.