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

Jon Heller · Accepted Answer · 2012-02-05 05:02:30Z

6

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.

answered Feb 5, 2012 at 5:02

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adnan Bhatti Over a year ago

True or he can also use host/bind or local variable in annon PL/SQL block to get the return value

Alex Poole · Accepted Answer · 2012-02-05 09:54:05Z

3

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.

answered Feb 5, 2012 at 9:54

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

2 Comments

Adnan Bhatti Over a year ago

+1 Yup and there is another way which can also be used like this DECLARE rc number(specify length or use %TYPE); arg varchar2(5):='Hello'; BEGIN rc := tf(arg);DBMS_OUTPUT.PUT_LINE(rc);END; or you can also use DBMS_OUTPUT.PUT_LINE(tf(arg)); in between begin and end tags.

Alex Poole Over a year ago

@MS Stp - that what I assumed you meant from your earlier comment; I think it's worth breaking that out into a separate answer as it's useful in itself, and arguably more generic?

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related