Inconsistent behaviour when executing a function from within Oracle SQL*Plus?

Question

I am new to Oracle and SQL and am trying to execute a simple test function from within SQL*Plus. My function is called tf (for Test Function) and it is defined in a file called tf.sql as follows ;

create or replace
function
tf
(
 arg1 in varchar2
)
return number

as

return_value number;

begin

return_value := 0;
dbms_output.put_line('Argument 1 = ' || arg1);
return return_value;

end;
/

I am able to successfully load this function into Oracle using the following command ;

SQL> start ./tf.sql

As a result of executing this command, SQL*Plus simply states ;

Function created.

When I then execute the following command from the SQL*Plus command prompt (after I have invoked set serveroutput on) ;

SQL> exec dbms_output.put_line(SYSTEM.TF('Hello'));

I get the following output ;

Argument = Hello
0

PL/SQL procedure successfully completed.

Now, if I attempt to execute my function directly from the SQL*Plus command prompt, using the following command ;

SQL> exec SYSTEM.TF('Hello');

then I get presented with the following error message from SQL*Plus ;

BEGIN SYSTEM.TF('Hello'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TF' is not a procedure or is undefined
ORA-06550: ;ine 1, column 7
PL/SQL: Statement ignored

Is anyone able to shed any light on this for me? I can't work out why my function appears to execute successfully in the first case, but not in the second case.

If I execute the following command from the SQL*Plus command prompt ;

SQL> select * from user_objects where object_name = 'TF';

then I get the following results returned ;

OBJECT_NAME
-----------
TF
SUBOBJECT_NAME
--------------
OBJECT_ID
---------
74475
DATA_OBJECT_ID
--------------
OBJECT_TYPE
-----------
FUNCTION
CREATED
-------
05-FEB-12
LAST_DDL_
---------
05-FEB-12
TIMESTAMP
---------
2012-02-05:02:11:15
STATUS
------
VALID
T
-
N
G
-
N
S
-
N
EDITION_NAME
------------
1

Any help on this would be immensely appreciated.

Thanks in advance.

Craig

Answer 1

Following on from @jonearles answer,which highlights the difference between a function and a procedure from SQL*Plus' perspective, and @MS Stp's comment, one way to run it is:

variable rc number;
exec :rc := tf('Hello');

Argument = Hello

PL/SQL procedure successfully completed.

To see the return code you can then do:

print rc
0

exec is really just shorthand for an anonymous PL/SQL block, as you can see from the error message you got. variable lets you declare a bind variable at SQL*Plus level, rather than in the block. You can also declare the argument as bind variable, and set it with a separate exec call:

variable rc number;
variable arg varchar2(5);
exec :arg := 'Hello';
exec :rc := tf(:arg);

I often use this construct for testing an existing procedure call, e.g. something copied from Pro*C code, without having to replace the variables in that call with fixed values. It can make it easier to call repeatedly with different arguments, and you can reuse variables in several calls - so you could pass :rc to another function later.

Answer 2

exec does not work with functions because it does not know what to do with the return value. This is similar to a regular PL/SQL statement; if you call a function you must assign the return value to something.

If you want to use a function in SQL*Plus, you should use SQL instead:

select tf('asdf') from dual;

Also, you should never create objects in SYSTEM. This can cause some really weird problems.