PLS-00306 Error for Sub Function 1

[Petemush]

Hello,

I have an interesting twist on the ever loved PLS-00306 wrong number or types of arguments in call to ... error!

I have a stored procedure which amongst other things, has a function defined inside of it. Cut down version is like so:

CREATE OR REPLACE PROCEDURE hsp_ext_didos_corp (
pn_status       IN OUT     NUMBER,
pc_error_no     IN OUT     VARCHAR2,
pc_error_mess   IN OUT     VARCHAR2,
pc_directory    IN         VARCHAR2,
pc_filename     IN         VARCHAR2)
IS

-- Some variables

-- Some cursors

-- some procedures

--My function
FUNCTION hsf_supp_type(p_sup_sup_id VARCHAR2)
RETURN VARCHAR2 IS

lc_supp_type  VARCHAR2(1);

BEGIN

  ... code ...
  
  lc_supp_type := '7';

  RETURN lc_supp_type;
END hsf_supp_type;

BEGIN

.. main code ....

END hsp_ext_didos_corp

Now I call this function from a cursor in the main program, the cursor resembles this:

SELECT DECODE(whatever, '2', hsf_supp_type(varcharvariable), NULL)
FROM wherever
WHERE whatever

Trying to compile this procedure brings back the error:

119/38 PLS-00306: wrong number or types of arguments in call to 'HSF_SUPP_TYPE'

Can't for the life of me work out why, but the interesting thing is, if I comment the function out of the procedure and compile it as a stand alone stored procedure on the database, the procedure then compiles fine!

Has anyone encountered this before or possibly know why this should happen?

Cheers!

 

[sem]

SQL engine cann't see your "private" function, thus when you run your query another hsf_supp_type with probably different signature is called. This may be your own function or a synonym. In any case you may DESCribe it to see its signature. Then you may query ALL_OBJECTS to find out which object is referenced by this name.

Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

Cheers Dima,

I've already looked for that possibility and couldn't find any other functions called hsf_supp_type. Also tried doing a Describe and nothing happened.

I know that my procedure is looking at my "private" function because if I comment the function out then I get an "hsf_supp_type must be defined" error.

I've also tried calling the function using hsp_ext_didos_corp.hsf_supp_type but to no avail.

Thanks for your suggestion though!

 

[sem]

Could you provide more details about "resembles this"? I'm still sure that if no public object named hsf_supp_type exist then this query can not be compiled but not due to the wrong function signature, but rather due to PLS-00231 or similar error. In any case the function must be public (standalone or packaged) to be used within sql.
BTW what is your exact Oracle version?

Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

Right, Oracle is 8.1.7.4.0, just noticed that I posted this in the 9i forum so if anyone has the power, would be good if it could be moved, sorry about that!

I noticed I used the phrase "main program" in my original post, what I meant by that was the main procedure.

This function is defined inside a procedure. The same procedure then calls this function from one of its cursors. So as far as I'm aware, Public and Private don't come into it because this is all within the scope of one preocedure. Sorry if there was any confusion there!

I said resembles because the cursor is quite big and I just cut it down to what mattered, so if I expand it a bit:

CURSOR cur_sup IS
   SELECT  '01|0|'||
           ifs.supplier||'|'||
           SUBSTR(ifs.supplier_name,1,35)||
           '|N|N|01|'||
           [B]DECODE(sup.home_import, '2', hsp_ext_didos_corp.hsf_supp_type(ifs.supplier), NULL)||'|'||[/B]
           DECODE(sup.home_import, '2', DECODE(sup.pay_type, '2', 1, '3', 2, '4', 3, '5', 4), NULL)||'|' dan_rec,
           ifs.supplier,
           sup.name
   FROM
          suppliers      sup,
          if_suppliers   ifs
   WHERE  NVL(ifs.date_closed, SYSDATE) > '28-FEB-98'
   AND    ifs.account_type IN ('M', 'A', 'R', 'B', 'J', 'L')
   AND    sup.sup_id       = ifs.supplier;

Bear in mind I didn't write this cursor, I'm just tacking on to the end of it. This is still not the full cursor but hopefully it's given a better idea of its structure.

Cheers!

 

[sem]

I asked about exact code because the word "resembles" is quite ambiuous: hsp_ext_didos_corp.hsf_supp_type only resembles hsf_supp_type but in fact may refer either to some function hsf_supp_type in hsp_ext_didos_corp package or function hsf_supp_type owned by hsp_ext_didos_corp user.

Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

Cheers for all this Dima,

I did a search through hsp_ext_didos_corp and found no other hsp_supp_type functions or otherwise. Pretty sure there is no hsp_ext_didos_corp user but had a search through the users table and came up with nowt.

What suggests most to me that the line in the cursor is definitely looking at the right function is that if I comment the function out then I get the following errors depending on how I try to call the function:

119/57 PLS-00302: component 'HSF_SUPP_TYPE' must be declared

and

119/38 PLS-00201: identifier 'HSF_SUPP_TYPE' must be declared

The first occurs if I try to call the function using hsp_ext_didos_corp.hsf_supp_type and the second occurs using just hsf_supp_type as a call.

 

[sem]

Ok, are you sure that PLS-00306 was caused by call to hsf_supp_type, not home_import or any other procedure? When you comment hsf_supp_type, compiler finds unresolved name and raises an error; then if it resolves all names it finds some parameters mismatch and raises another error; and if everything else is ok it fails on the scope issue.

I just want to say that you're out of luck to call nested or private function from sql and this is not an assumption but a confidence. Error codes may differ, but the result remains the same.

Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

Cheers for that Dima,

I got an answer off MetaLink earlier on which went into detail on the whole scope thing with the SQL Engine for the cursor etc. etc. and have now managed to test it with a dummy procedure trying to reference a local variable from a cursor and it failing.

Also worked out the correct layout of declares begins etc. so that I get a proper error message telling me I can't use the procedure from the cursor!

Cheers for your help and have a star for helping me track it down!