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 fuction as CREATE OR REPLACE FUNCTION MD5_ENCODE it will run smoothly, but if it satys anonymously within the SQL-Plus block as PL/SQL --> "may not be a function" error. what is this Oracle "feature" again??? many thx. in advance...

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

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.

Answer 2

Your first problem is you have a typo.

FUNCTION MD5_ENCODE(CLEARTEXT IN VARCHAR2) RETURN VARCHAR2 IS

versus

UPDATE ADDRESSES_T SET STREET = MD5ENCODE(STREET) ;

You are missing the underscore in the function call in the update statement.

The next error you will encounter is :

PLS-00231: function 'MD5_ENCODE' may not be used in SQL

So you can simply assign the function results to a variable and use it in the Update statement.

Answer 3

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 ...