Home » SQL & PL/SQL » SQL & PL/SQL » please help,Error(15,22): PLS-00382: expression is of wrong type
please help,Error(15,22): PLS-00382: expression is of wrong type [message #585111] Wed, 22 May 2013 04:42 Go to next message
fared240
Messages: 2
Registered: May 2013
Junior Member
create or replace
FUNCTION xx_TEST(
      p_dj_id       NUMBER ,
      p_dj_amount   NUMBER ,
      p_item_amount NUMBER )
    RETURN NUMBER
  IS
    v_item_amount NUMBER ;
    PP_Diff       NUMBER ;
  BEGIN
    v_item_amount  := p_item_amount ;
    IF p_dj_amount >= v_item_amount THEN
      RETURN v_item_amount ;
    ELSE
      RETURN PP_Diff = NVL(v_item_amount,0)-NVL(p_dj_amount,0) ;
      WHILE PP_Diff  > 0
      LOOP
        PP_Diff := NVL(v_item_amount,0)-NVL(PP_Diff,0);
        EXIT
      WHEN pp_diff <=0;
      END LOOP;
    END IF;
  END ;


please help me why is this error coming.



[Edit MC: added code tags]

[Updated on: Wed, 22 May 2013 04:47] by Moderator

Report message to a moderator

Re: please help,Error(15,22): PLS-00382: expression is of wrong type [message #585112 is a reply to message #585111] Wed, 22 May 2013 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session like:
SQL> create or replace
  2  FUNCTION xx_TEST(
  3        p_dj_id       NUMBER ,
  4        p_dj_amount   NUMBER ,
  5        p_item_amount NUMBER )
  6      RETURN NUMBER
  7    IS
  8      v_item_amount NUMBER ;
  9      PP_Diff       NUMBER ;
 10    BEGIN
 11      v_item_amount  := p_item_amount ;
 12      IF p_dj_amount >= v_item_amount THEN
 13        RETURN v_item_amount ;
 14      ELSE
 15        RETURN PP_Diff = NVL(v_item_amount,0)-NVL(p_dj_amount,0) ;
 16        WHILE PP_Diff  > 0
 17        LOOP
 18          PP_Diff := NVL(v_item_amount,0)-NVL(PP_Diff,0);
 19          EXIT
 20        WHEN pp_diff <=0;
 21        END LOOP;
 22      END IF;
 23    END ;
 24  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION XX_TEST:
LINE/COL
-------------------------------------------------------------------------
ERROR
-------------------------------------------------------------------------
14/7
PL/SQL: Statement ignored
14/22
PLS-00382: expression is of wrong type


RETURN PP_Diff = NVL(v_item_amount,0)-NVL(p_dj_amount,0) ;

Either return "PP_Diff" or "NVL(v_item_amount,0)-NVL(p_dj_amount,0)" but you cannot return the test between the two (which is of BOOLEAN datatype and not NUMBER as in the defintion, then the error).

Regards
Michel

Re: please help,Error(15,22): PLS-00382: expression is of wrong type [message #585114 is a reply to message #585111] Wed, 22 May 2013 04:54 Go to previous messageGo to next message
Littlefoot
Messages: 18847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This:
RETURN PP_Diff = NVL(v_item_amount,0)-NVL(p_dj_amount,0) ;
returns Boolean (while you specified that your function returns a NUMBER).

Besides, WHILE loop will never execute (even if you fix the above error). RETURN should be behind the loop.
Re: please help,Error(15,22): PLS-00382: expression is of wrong type [message #585141 is a reply to message #585114] Wed, 22 May 2013 07:59 Go to previous message
fared240
Messages: 2
Registered: May 2013
Junior Member
thx all
Previous Topic: How the SQL hash is computed ?
Next Topic: Retrieve view definiton
Goto Forum:
  


Current Time: Thu Apr 24 20:35:29 CDT 2014

Total time taken to generate the page: 0.16650 seconds