MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Not quite an invalid function

with 3 comments

An interesting thing happened today, as I was explaining how you call functions with embedded DML statements. The students were stunned at seeing an ORA-06576 error for a function that they knew existed. It’s one of those imperfect error messages …

Basically, they wrote a wrapper function to a parallel enabled function, and then they tried to call it into a session level bind variable, like this:

SQL> VARIABLE verified NUMBER
SQL> CALL update_contact INTO :verified;

It failed with the following message:

CALL update_contact INTO :verified
     *
ERROR at line 1:
ORA-06576: NOT a valid FUNCTION OR PROCEDURE name

They were stunned but I was mum. I suggested that they test the call in an anonymous block program. Here’s the PL/SQL call example:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> BEGIN
  2    IF update_contact = 1 THEN
  3      DBMS_OUTPUT.put_line('Success!');
  4    ELSE
  5      DBMS_OUTPUT.put_line('Failure!');
  6    END IF;
  7  END;
  8  /

It returns

Success!

I removed the puzzled look by explaining that while you don’t need to provide the open and close parentheses inside PL/SQL, you do generally require them in the context of a CALL statement. They’re not required for stored functions in SQL statements, but they’re required for stored instantiable object types.

Here’s the correct way call the program:

SQL> CALL update_contact() INTO :verified;
SQL> SELECT DECODE(:verified,1,'Success!','Failure!') AS answer FROM dual;

It prints this to console:

ANSWER
--------
Success!

The natural question is why don’t you just run the function as part of a query. It’s a great question because it lets me demonstrate another principle. The principle that you can’t can’t perform a DML in a query, which is abstract until you see it up front and personal.

SQL> SELECT update_contact() FROM dual;

this query raises the following exception:

SELECT update_contact() FROM dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "PLSQL.UPDATE_CONTACT", line 4

Setup Code Supplement

You can find the base code for this example here. It comes from the Oracle Database 11g PL/SQL Programming book. After you run the create_store.sql script, you’ll need to run the following:

-- Add a null allowed column for derived data.
ALTER TABLE contact ADD (full_name VARCHAR2(44));
 
-- Define a function concatenate strings.
CREATE OR REPLACE FUNCTION MERGE
( last_name     VARCHAR2
, first_name    VARCHAR2
, middle_initial VARCHAR2 )
RETURN VARCHAR2 PARALLEL_ENABLE IS
BEGIN
  RETURN last_name ||', '||first_name||' '||middle_initial;
END;
/
 
-- Define a wrapper function around the merge function.
CREATE OR REPLACE FUNCTION update_contact RETURN NUMBER IS
  ret_val NUMBER := 0; -- The default return value to false or zero.
BEGIN
  UPDATE contact c1
  SET    c1.full_name = (SELECT MERGE(c2.last_name
                                     ,c2.first_name
                                     ,c2.middle_initial)
                         FROM   contact c2
                         WHERE  c1.rowid = c2.rowid);
  IF SQL%ROWCOUNT > 0 THEN
    ret_val := 1; -- This is only reached when 1 or more rows are updated.
  END IF;
  RETURN ret_val;
END update_contact;
/

Written by maclochlainn

May 27th, 2009 at 1:52 pm

Posted in Oracle,pl/sql,sql

«
»