The story is this. We have to make a change to one of our data migration scripts. It's already doing some really nasty string manipulation, and we are about to make it far far worse. Frankly, I don't have a hope of making the change unless we can simplify things a bit first, so I was hoping to break some of our string manipulation out into functions.
In order to do that, we wanted to develop these functions out of the context of the migration script, so we could play with, uh, I mean test them. ;-)
Problem is, the finest minds in Herbal Hill haven't been able to write a function that Oracle is happy with. We have removed all the complex stuff, and are trying to write and run the simplest function that we can think of, but we get no joy. Running the broken_function.sql, we get this:
ORA-06550: line 11, column 13:
PLS-00231: function 'FOO' may not be used in SQL
ORA-06550: line 11, column 13:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 11, column 6:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Any clues?
Posted to Software development by Simon Brunning at November 22, 2007 11:38 AMThe error message is right --- you cannot use a simple PL/SQL function in a SQL statement. You need to create a function for use in SQL with the CREATE FUNCTION statement. Alternatively, create a local PL/SQL variable, assign the function result to that, and use the variable in the SQL statement (NB, untested code follow):
WHENEVER SQLERROR EXIT 1
DECLARE
FUNCTION foo(bar IN VARCHAR2, baz IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
return bar || baz;
END foo;
temp VARCHAR2;
BEGIN
temp := foo('egg', 'chips');
select temp from dual;
END;
/
I don't think that's entirely right - way back when I did Oracle stuff, you *could* use a custom function in SQL. The catch is that the optimiser wants to be sure that calling a function doesn't have any side effects, so you have to declare that using a pragma.
Not sure it still applies, though: see http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#21958
And look at the discussion of purity on that page.
Posted by: xtian on November 22, 2007 05:51 PMBrilliant, thanks.
Posted by: Simon on November 22, 2007 06:04 PMWouldnt it be wonderful if we could write database scripts in Python ;)
Posted by: Cenk on November 22, 2007 08:35 PMThe simple way to do this in SQL*Plus is;
"""
CREATE OR REPLACE FUNCTION foo(bar IN VARCHAR2, baz IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN bar || baz;
END foo;
/
SELECT foo('egg', 'chips')
FROM dual
/
"""
"Pragma RESTRICT_REFERENCES must be declared in a package specification"
Sigh.
Andy, I was hoping to avoid littering the schema with temporary functions. Still perhaps that's the way to go.
Or better still, write the bleedin' thing in Java. (Java rather than Python 'cos we have all the Hibernate machinery set up so we'll get a lot of functionality for free.)
Posted by: Simon on November 23, 2007 09:40 AMYou could wrap your functions in a package. That way you only pollute the schema with one name (the package name). You can then also use RESTRICT_REFERENCES as appropriate.
Posted by: Anthony Williams on November 27, 2007 11:44 AM