[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4582
  • Last Modified:

ORA-06553: PLS-306: Error in function

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?
0
jugluck
Asked:
jugluck
  • 5
  • 4
  • 3
  • +1
5 Solutions
 
JimBrandleyCommented:
Where is the code or script that invokes the SP? That seems to be where the problem is.

Jim
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
JimBrandleyCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
 
jugluckAuthor 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean, you call it like this:

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

or do you call it in a query?
also, 'av_length' and 'routeid', are those variables/fields from the source table?
if yes, remove the quotes:

av_length:=sp_match_slabs(mu_id,1, av_length , routeid );
0
 
jugluckAuthor 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>route_id is the actual field from the source table
so, you need to query like this:

select sp_match_slabs(mu_id,1, av_length, routeid ) into av_length
from yourtable
where  ...

or, first get the values from the table into variables, and use those in you call.

with sp_match_slabs(mu_id,1,'av_length','routeid'); you pass the strings 'av_length' and 'routeid' as arguments, which explains the problem.


0
 
Jinesh KamdarCommented:
@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??
0
 
jugluckAuthor 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
0
 
Jinesh KamdarCommented:
That is because you cannot directly pass a table-column in a simple function call. Either the function call has to be within a SELECT or you have to extract the route_id column value within a local variable and then pass that local variable to the function call.
0
 
jugluckAuthor Commented:
Jinesh:
 How can I do this?
 Thanks
0
 
Jinesh KamdarCommented:
Your implementation would depend on what u want to pass. If you want to call the function for just a single value of the route_id column, then SELECT that value INTO a local variable first and then pass that local variable to the function call. If you need to call this function for multiple values of route_id, then you can call this function within a SELECT (shown in Angel's last post) or loop through a cursor defined on a SELECT that has this column and then call this function with the cursor variable in that loop. What is it that you wish to do from the above options?
0
 
jugluckAuthor Commented:
It finally worked.
Thanks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now