Thanks for the question, Ken.

Asked: June 21, 2000 - 12:29 pm UTC

Last updated: June 18, 2004 - 11:05 am UTC

Version: Oracle 8

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I encounted a package that has a procedure and function that shares the same name and parameter. How would Oracle differentiate the two? These procedure/function are called within sqlplus like this:

exec status.update_staus (-
p_action=>status.step_started, -
p_step_id=>status.load_fnd, -
p_load_id=>to_date ('$FILE', 'YYYYMMDD_HH24MISS'), -
.
.
);

I just don't understand what is happening here..

many thanks
GoJo.

and Tom said...

the way in which the packagd procedure/function is called tells us whether the call the procedure or the function. In your example above, I can tell you the PROCEDURE is being called since there is nothing to recieve the value of the function. Here is a small example of your issue:

ops$tkyte@8i> create or replace package demo_pkg
2 as
3 procedure foo( x in number );
4 function foo( x in number ) return varchar2;
5 end;
6 /

Package created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body demo_pkg
2 as
3 procedure foo( x in number )
4 is
5 begin
6 dbms_output.put_line( 'PROCEDURE called with ' || x );
7 end;
8
9 function foo( x in number ) return varchar2
10 is
11 begin
12 dbms_output.put_line( 'FUNCTION called with ' || x );
13 return 'function called';
14 end;
15 end;
16 /

Package body created.

Here I call the procedure version. Since I did not ASSIGN demo_pkg.foo to anything or attempt to pass it as a parameter -- it must call the procedure

ops$tkyte@8i>
ops$tkyte@8i> exec demo_pkg.foo(5)
PROCEDURE called with 5

PL/SQL procedure successfully completed.

Here I call the FUNCTION version. I am using the return value from the function as the input into another procedure. Since I need a return value -- it has to call the function

ops$tkyte@8i> exec dbms_output.put_line( demo_pkg.foo(10) )
FUNCTION called with 10
function called

PL/SQL procedure successfully completed.

Rating

(11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 16, 2003 - 3:29 pm UTC

Have some related questions, hope you don't mind if I post them here rather than wait for your banner to change!

1. When performing a task in PL/SQL, when is it appropriate to use a procedure and when is it appropriate to use function. I am used to create procedures for most of
the purposes (eg: I have procedure which will rebuild all the indexes after a load into our datawarehouse). I have used functions only for using within a select statement.
But recently I came across a programming style of using functions for carriying out tasks that we generally do using procedures.

Eg: A procedure for rebuilding indexes can be rewritten as a function and called like this:

begin
if bnadmin.fn_rebuild_index('BNADMIN') then
dbms_output.put_line('All indexes of BANADMIN rebuilt');
else
dbms_output.put_line('Error...!! Please check error log for details');
end if;
end;
/

I wanted to know your openion on what you prefer.

2. Secondly, I have seen that while most functions can be executed by selecting from dual, not all. Some of them have to be executed as above. Can you please discuss this with some examples.

Thank you very much for hosting this wonderfully helpful forum.

January 16, 2003 - 8:01 pm UTC

1) it is more a matter of "common sense" then anything else. If it makes more sense to be a function, by all means - go for it.

The general "rot" on this is (compute science-ese here):

a function does not modify its inputs and returns a single output. it takes only in parameters and always returns a value.

a procedure can modify and/all of its inputs and takes in, in/out, out parameter.

I don't follow that tho, I use functions where they make sense, procedures where they make sense.

2) you cannot select a function that uses IN OUT variables.
you cannot select a function that modifies the database (unless it is an autonomous transaction)

Is/As any difference

reader, October 10, 2003 - 7:34 am UTC

Hi Tom,
is there any difference between is and as clause of procedure/function creation I dont' think so and where is the compiled code kept in the database and if you can please explain how Oracle works on procedure/function to be in sga as it works on query by calculating hash value.

Thanks for your help as always.

October 10, 2003 - 8:16 am UTC

It is purely semantics.

create or replace procedure p AS begin....

reads nicer in english then

create or replace procedure p IS begin....

but in a package:

create or replace package body pkg
as

procedure sounds_nice is begin ....

procedure doesnt_sound_so_nice as begin .....

so, i use them that way.

the code is kept in the idl$ tables in the sys schema. The code is even easier then a generic query for code has a fully qualified, unique name associated with it. makes it really easy to organize and store.

IS/AS syntax

Ashiq Shamsudeen, October 10, 2003 - 9:29 am UTC

Tom,

I believe even these syntax "IS/AS" are come up from ADA programming language.Isn't it?

October 10, 2003 - 10:43 am UTC

mmm, i cannot remember back that far -- it was 1990/1992 since I did Ada -- but probably, yes.

Overloading?

Padders, October 10, 2003 - 11:09 am UTC

Tom,

Just a pedants' clarification regarding the original question / answer. When a function exists with the same name and parameters as a procedure in the same package would this constitute (be referred to as) an 'overloading'? Looking in ALL / USER_ARGUMENTS Oracle records this situation as overloading 1 and 2 so perhaps I answer my own question. Obviously overloadings are typically determined by the parameters and their type - is there a specific term for this form of overloading?

October 10, 2003 - 11:17 am UTC

this is overloading -- overloading is when an "object" with the same name but a different "signature" (signature being a function of the inputs/outputs) differ.

Just pretend the function is a procedure with an extra OUT parameter at the end of the parameter list.

why would someone want to call same name to proc and func inside packages?

A reader, November 17, 2003 - 7:11 am UTC

Hi

I wonder what´s the use or the point naming the procedures/function to same name inside a package? (Overloading)

November 17, 2003 - 7:37 am UTC

well, it would have been cool if the HTP package (from mod_plsql) would have a function "escape_sc" as well as a procedure escape_sc

then we could code:

htp.escape_sc( 'hello & world' );

htp.p( 'hello ' || htp.escape_sc( 'hello & world' ) || ' world' );

instead of having to have an HTP and HTF (hyper text PROCEDURES, hyper text FUNCTIONS) package as we do. we really only needed one package -- not two as we have.

Code maintenance

reader, November 18, 2003 - 4:02 pm UTC

To make the maintenance of the code easier one should call the other i.e. the function calls the procedure and returns the value (or vice versa). The idea is to maintain a single code base and not maintain it in both the function and procedure. One of them is just a wrapper to the other making them easier to use in the apps and easy to maintain as well.

A reader, June 17, 2004 - 12:13 pm UTC

what if the case is this

CREATE OR REPLACE PACKAGE salespkg

IS
   PROCEDURE calc_total (zone_in IN VARCHAR2);

   PROCEDURE calc_total (zone_in IN CHAR);
END salespkg; 
/



SQL> DECLARE
  2     l_zone CHAR(6) := 'ZONE15';
  3  BEGIN
  4     salespkg.calc_total (l_zone);
  5  END;
  6  /
   salespkg.calc_total (l_zone);
   *

ERROR at line 4:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call

June 17, 2004 - 2:40 pm UTC

the case would be a wrong example. char, varchar2 -- they share the same signature. you cannot overload on that.

A reader, June 17, 2004 - 4:39 pm UTC

but if two procedure which have parameters name
but different type like one is SYS_REFCURSOR
and other is varchar2
as given below then how am i suppose to call
the 2nd procedure
as it giving me

create or replace package test as

create or replace package test as

PROCEDURE a (
in_Query IN SYS_REFCURSOR,
out_XML_doc OUT CLOB,
out_Status_CD OUT VARCHAR2,
out_Err_Msg OUT VARCHAR2,
Rowset_Tag IN VARCHAR2 DEFAULT NULL,
Row_Tag IN VARCHAR2 DEFAULT NULL,
Root_Node IN VARCHAR2 DEFAULT NULL,
Ind IN BOOLEAN DEFAULT TRUE,
Header_Ind IN BOOLEAN DEFAULT FALSE );

PROCEDURE a (
in_Query IN VARCHAR2,
out_XML_Doc OUT CLOB,
out_Status_CD OUT VARCHAR2,
out_Err_Msg OUT VARCHAR2,
Rowset_Tag IN VARCHAR2 DEFAULT NULL,
Row_Tag IN VARCHAR2 DEFAULT NULL,
Root_Node IN VARCHAR2 DEFAULT NULL,
Ind IN BOOLEAN DEFAULT TRUE,
Header_Ind IN BOOLEAN DEFAULT FALSE );

END test;
/
i am calling it like this
declare
insql_stmnt IN VARCHAR2 := 'select * from emp';
inrowsetTag IN VARCHAR2 := 'xx',
out_XML OUT CLOB,
outRETURN_CD OUT CHAR,
outRETURN_MSG OUT VARCHAR2
v_dcmnt_xml CLOB;
v_OutCd CHAR(2) := '';
v_OutMsg VARCHAR2(500) := '';

BEGIN

test.a(
in_Query => insql_stmnt,
out_XML_Doc => v_dcmnt_xml,
out_Status_CD => v_OutCd,
out_Err_Msg => v_OutMsg,
Rowset_Tag => inrowsetTag,
Row_Tag => 'ROW',
Root_Node => null
Ind => TRUE,
Header_Ind => FALSE);
END;

i am getting PLS-00307 too many declarations to match this call kind of error

how to call the 2nd procedure in the package

June 17, 2004 - 6:38 pm UTC

that is not the code you are calling that with, it is not even PLSQL code.  However, this block that looks like yours a little bit:

ops$tkyte@ORA9IR2> declare
  2  insql_stmnt        VARCHAR2(200) := 'select * from emp';
  3  inrowsetTag        VARCHAR2(200) := 'xx';
  4  out_XML     CLOB;
  5  outRETURN_CD       CHAR(200);
  6  outRETURN_MSG       VARCHAR2(200);
  7  v_dcmnt_xml        CLOB;
  8  v_OutCd           CHAR(2)  := '';
  9  v_OutMsg          VARCHAR2(500)  := '';
 10
 11  BEGIN
 12
 13  test.a(
 14  in_Query          => insql_stmnt,
 15  out_XML_Doc          => v_dcmnt_xml,
 16  out_Status_CD        => v_OutCd,
 17  out_Err_Msg          => v_OutMsg,
 18  Rowset_Tag   => inrowsetTag,
 19  Row_Tag           => 'ROW',
 20  Root_Node         => null,
 21  Ind         => TRUE,
 22  Header_Ind  => FALSE);
 23  END;
 24  /
 
PL/SQL procedure successfully completed.

works -- soooo, give us an entire example -- cut and pasted.  remove ALL non-essential pieces -- all of them (eg: remove all parameters except for the problem one)

YA IT WAS A GOOD ONE...

ashish, June 18, 2004 - 12:39 am UTC

ya it was a good page , with things to try on..

A reader, June 18, 2004 - 10:43 am UTC

Hi,
i created a PACKAGE
CREATE OR REPLACE PACKAGE test_salespkg

IS
PROCEDURE calc_total (zone_in IN VARCHAR2);

PROCEDURE calc_total (zone_in IN sys_refcursor);
END test_salespkg;
/
then i called
DECLARE
l_zone CHAR(6) := 'ZONE15';
BEGIN
apps.test_salespkg.calc_total (l_zone);
END;
/
i got this error
ERROR at line 4:
ORA-06550: line 4, column 8:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call
ORA-06550: line 4, column 8:
PL/SQL: Statement ignored

second time i ran i got this error
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "APPS.TEST_SALESPKG" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 4

am i doing something wrong

June 18, 2004 - 11:05 am UTC

who is "apps"

You should get the second error (for I don't see a package body).

perhaps you are not "apps"
perhaps there is an "apps" user
perhaps the apps user has this package and it is overloaded "wrong"
perhaps the second time you ran it, it was using your code and got the right error


but, you don't give us the full example, don't say who you are logged in as, so it is all about "perhaps"

ops$tkyte@ORA9IR2> CREATE OR REPLACE PACKAGE test_salespkg
  2
  3  IS
  4     PROCEDURE calc_total (zone_in IN VARCHAR2);
  5
  6     PROCEDURE calc_total (zone_in IN sys_refcursor);
  7  END test_salespkg;
  8  /
 
Package created.
 
ops$tkyte@ORA9IR2> DECLARE
  2     l_zone CHAR(6) := 'ZONE15';
  3  BEGIN
  4     test_salespkg.calc_total (l_zone);
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "OPS$TKYTE.TEST_SALESPKG" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 4
 

Is what I would expect to see, if you cut and pasted from sqlplus directly.

A reader, June 18, 2004 - 10:59 am UTC

Hi,
may be this is not the right example
i will give the correct one
Thanks
rakesh

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Is/As any difference

IS/AS syntax

Overloading?

why would someone want to call same name to proc and func inside packages?

Code maintenance

YA IT WAS A GOOD ONE...

More to Explore

PL/SQL demos

PL/SQL docs

Procedure and Function sharing the same name and parameter.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Is/As any difference

IS/AS syntax

Overloading?

why would someone want to call same name to proc and func inside packages?

Code maintenance

YA IT WAS A GOOD ONE...

More to Explore

PL/SQL demos

PL/SQL docs