We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

ORA-06553: PLS-306: Error in function

jugluck
jugluck asked
on
4,776 Views
Last Modified: 2013-12-19
I am working on Oracle 10Gr2 with Windows XP machine.
I have created 2 functions in oracle and I am pasting the code as shown below
i)
create or replace function sp_match_slabs(MU_ID IN VARCHAR2,MU_SET IN INTEGER,CHR_ID IN VARCHAR2,PO_ID IN VARCHAR2,ROUTE_ID VARCHAR2) return number is

chrValue NUMBER;
avLen1 NUMBER := 204;
avLen2 NUMBER := 240;
avLen3 NUMBER := 316;
avLen4 NUMBER := 365;
avLen5 NUMBER := 382;
avLen6 NUMBER := 429;
avLen7 NUMBER := 435;
avLen8 NUMBER := 480;
yield Float:=0;
begin

    chrValue := nvl(to_number(sp_get_mu_char(mu_id,mu_set,chr_id)),-1);

    if (
      chrValue between avLen1 and avLen2 or
      chrValue between avLen3 and avLen4 or
      chrValue between avLen5 and avLen6 or
      chrValue between avLen7 and avLen8 or
      chrValue/2 between avLen1 and avLen2 or
      chrValue/2 between avLen3 and avLen4 or
      chrValue/2 between avLen5 and avLen6 or
      chrValue/2 between avLen7 and avLen8 or
      chrValue/3 between avLen1 and avLen2 or
      chrValue/3 between avLen3 and avLen4 or
      chrValue/3 between avLen5 and avLen6 or
      chrValue/3 between avLen7 and avLen8
    )
      then
    yield:= sp_check_yield(po_id,mu_id,1,ROUTE_ID);
   dbms_output.put_line('Yield');
   dbms_output.put_line(yield);
    if (yield<=0.25) Then
    return chrValue;
    end if;
    end if;
    return -1;
end sp_match_slabs;

Upon compiling the code I am getting the following error.
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SP_MATCH_SLABS'


What is wrong with the code?
Comment
Watch Question

Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please try with:

create or replace function sp_match_slabs(MU_ID IN VARCHAR2,MU_SET IN NUMBER,CHR_ID IN VARCHAR2,PO_ID IN VARCHAR2,ROUTE_ID VARCHAR2) return number is
Top Expert 2007

Commented:
angellll - I didn't think INTEGER was a key word in Oracle. However, I built a simple table with:
CREATE TABLE test
(
   testkey INTEGER,
   testname VARCHAR2(40)
);

and it (10g - R2) built the table! I was very surprised. Have you seen INTEGER used before?

Jim
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>Have you seen INTEGER used before?
no, but I haven't use ora-10x yet alot ...

so, actually, that would eventually not be the right track, but the calling procedure of this one...
we need more explanations from the author, then

Author

Commented:
Jim:
 There was an error in the calling function as you had explained in the first mail.
I have changed the SPs as shown
Main Function:
create or replace function sp_match_slabs(MU_ID IN VARCHAR2,MU_SET IN INTEGER,CHR_ID IN VARCHAR2,routeid IN VARCHAR2) return number is
chrValue NUMBER;

avLen1 NUMBER := 204;
avLen2 NUMBER := 240;
avLen3 NUMBER := 316;
avLen4 NUMBER := 365;
avLen5 NUMBER := 382;
avLen6 NUMBER := 429;
avLen7 NUMBER := 435;
avLen8 NUMBER := 480;
yield Number:=0;
begin
   
   
    chrValue := nvl(to_number(sp_get_mu_char(mu_id,mu_set,chr_id)),-1);

    if (
      chrValue between avLen1 and avLen2 or
      chrValue between avLen3 and avLen4 or
      chrValue between avLen5 and avLen6 or
      chrValue between avLen7 and avLen8 or
      chrValue/2 between avLen1 and avLen2 or
      chrValue/2 between avLen3 and avLen4 or
      chrValue/2 between avLen5 and avLen6 or
      chrValue/2 between avLen7 and avLen8 or
      chrValue/3 between avLen1 and avLen2 or
      chrValue/3 between avLen3 and avLen4 or
      chrValue/3 between avLen5 and avLen6 or
      chrValue/3 between avLen7 and avLen8
    )
      then
         
          yield:= sp_check_yield(mu_id,1,routeid);
          dbms_output.put_line('Yield');
          dbms_output.put_line(yield);
   
    if (yield <= 0.25) Then
    return chrValue;
    end if;
    else
    return -1;
   end if;
   
end sp_match_slabs;

This is the callled function

CREATE OR REPLACE FUNCTION Sp_Check_Yield(muId VARCHAR2, muSet NUMBER, routeId VARCHAR2)
RETURN NUMBER IS
projYield NUMBER:= 1.0; -- the projected yield, by default 1

reqWt     FLOAT := 0.0;
reqLen    FLOAT := 0.0;
reqThck   FLOAT := 0.0;
reqWidth  FLOAT := 0.0;

avlWt     FLOAT := 0.0;
avlLen    FLOAT := 0.0;
avlThck   FLOAT := 0.0;
avlWidth  FLOAT := 0.0;
reducelength Float:=0.0;
reduceweight Float:=0.0;
BEGIN

select muc.CHR_VALUE into avlWidth
       from material_unit_characteristic muc
       where muc.mu_id = muId and muc.chr_id = 'av_width';

       dbms_output.put_line('avlWidth');
        dbms_output.put_line(avlWidth);

select muc.CHR_VALUE into avlLen
       from material_unit_characteristic muc
       where muc.mu_id = muId and muc.chr_id = 'av_length';

        dbms_output.put_line('avlLen');
        dbms_output.put_line(avlLen);

select muc.CHR_VALUE into avlThck
       from material_unit_characteristic muc
       where muc.mu_id = muId and muc.chr_id = 'thickness';

       If(avlThck = 0) then
               avlThck := 9.11; -- default thickness
       END IF;

       dbms_output.put_line('avlThck');
        dbms_output.put_line(avlThck);

select muc.CHR_VALUE into avlWt
       from material_unit_characteristic muc
       where muc.mu_id = muId and muc.chr_id = 'av_weight';

        dbms_output.put_line('avlWt');
        dbms_output.put_line(avlWt);

select roc.MAX_VALUE into reqWidth
       from route_operation_characteristic roc
       where roc.route_id = routeId and roc.chr_id = 'av_width';

       dbms_output.put_line('reqWidth');
        dbms_output.put_line(reqWidth);

select roc.MAX_VALUE into reqLen
       from route_operation_characteristic roc
       where roc.route_id = routeId and roc.chr_id = 'tot_length';

        dbms_output.put_line('reqLen');
        dbms_output.put_line(reqLen);

       reqThck := 9.11; -- default thickness
     
       dbms_output.put_line('reqThck');
       dbms_output.put_line(reqThck);

select roc.MAX_VALUE into reqWt
       from route_operation_characteristic roc
       where roc.route_id = routeId and roc.chr_id = 'av_weight';

       dbms_output.put_line('reqWt');
        dbms_output.put_line(reqWt);

   If (avlWidth>reqWidth)
   Then
   if (AvlLen > ReqLen)Then
   reducelength:= Avllen-Reqlen;
   Else
   reducelength:=avlLen;
   End if;
   reduceweight:= 0.2855*avlwidth*reducelength*avlThck;
   projyield:=(0.2855*avlwidth*reducelength*avlThck)/avlWt;

   dbms_output.put_line('ReduceLength');
   dbms_output.put_line(reducelength);
   
   dbms_output.put_line('projYield');
   dbms_output.put_line(projYield);
   
   end if;

   RETURN projYield;



END;

And this is how I call the main function

av_length=sp_match_slabs(mu_id,1,'av_length','routeid')

The problem that I face is that when the 2 stored procedures are tested independently they work fine,but the values are not passed back to the call i.e. av_length doesnt have the value which should be returned to it after invoking this function

Please help
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
angelIIII:
route_id is the actual field from the source table
When i remove the quotes I get the error
ORA-0904: ROUTEID :Invalid identifier
:I feel that the value of the route ID is not being passed correctly thats why I am not getting the result but when I test the function where I manually pass the value of the route Id then I get the expected results
 I call it like
av_length:=sp_match_slabs(mu_id,1,'av_length','routeid');
Hope this helps
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
@jugluck
>> av_length:=sp_match_slabs(mu_id,1,'av_length','routeid');

In the above call, the use of 'av_length' seems fine to me after looking at SELECT ... INTO statements of the Sp_Check_Yield() function. However, i am not convinced about the use of 'routeid'. Is 'routeid' really a value stored in the route_id column of the route_operation_characteristic table??

Author

Commented:
Jinesh:
No 'routeid is not the value of the field route_id.The route operation characteristic table has the field route_id which can take string values like R5000,R5001,R5002 etc.I do suspect that 'routeid' is being passed rather than the actual values of route_id.When I remove the quotes as per angel's response I am getting the error.
ORA00904:Route_ID: Invalid identifier
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Jinesh:
 How can I do this?
 Thanks
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
It finally worked.
Thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.