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

«
»

3 Responses to 'Not quite an invalid function'

Subscribe to comments with RSS or TrackBack to 'Not quite an invalid function'.

  1. it is a bit puzzling why you have
    SYS_GUID(), SYSDATE, USER
    but not
    SYS_GUID, SYSDATE(), USER()

    …

    Laurent Schneider

    28 May 09 at 1:37 am

  2. My understanding of those is:

    • SYSDATE is a SQL*Plus session level alias for the result of an OS current time call.
    • USER is a SQL*Plus environment variable that maps to the CURRENT_USER in the V$SESSION view.
    • SYS_GUID() calls a function to return a Globally Unique Identifier. This makes it a constructor function that requires open and closed parentheses in a SQL context and none in a PL/SQL context.

    In SQL, you call it like this with the parentheses. This type of call mechanic is required for user-definted object types that are instantiable.

    SQL> SELECT sys_guid() FROM dual;

    The PL/SQL syntax returns a different value because each call returns a unique value.

    BEGIN
  DBMS_OUTPUT.put_line('['|sys_guid||']');
END;
/

    Laurent, while you know all this other don’t and I figured it might help somebody. If you’ve any followup comments, they’re always appreciated.

    Here’s a quick little example of the object type, and it’s call semantics. The first step is to create an object type that returns an instance of itself.

    1
2
3
4
5
6
7
8
9

    CREATE OR REPLACE TYPE hello_there IS OBJECT
( who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( who VARCHAR2 )
  RETURN SELF AS RESULT )
INSTANTIABLE NOT FINAL;
/

    After defining the object type, you create the body, like this:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    CREATE OR REPLACE TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    self := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (who VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    RETURN;
  END hello_there;
 
END;
/

    If you attempt to call it without parentheses in a SQL statement, it’ll fail like so …

    SELECT hello_there FROM dual
       *
ERROR at line 1:
ORA-00904: "HELLO_THERE": invalid identifier

    You should note that the error message is quite different than that received with the CALL triggered ORA-06576 error. If you provide paraentheses, like this

    SELECT hello_there() FROM dual;

    it’ll succeed with the following return value:

    HELLO_THERE()(WHO)
------------------------------
HELLO_THERE('Generic Object.')

    There’s no equivalent CALL syntax into a bind variable because you can’t define a bind variable of a user-defined type (e.g., object type). More on the right way to manage Oracle object types is found in Chapter 14 of Oracle Database 11g PL/SQL Programming.

    maclochlainn

    28 May 09 at 6:11 pm

  3. thanks for everything

    TonyBoy

    10 Sep 09 at 5:03 pm

Leave a Reply