Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2012
    Posts
    24

    Unanswered: Create function problem

    Hello again everyone. I am creating a function and getting an error when using it. None of function creation is covered in our books and I exhausted my online searching ability lol. Any advice/direction welcomed. Error is:
    Code:
    SQL Error: ORA-01722: invalid number
    ORA-06512: at "JPOWELL12.CHARTER_CHARGE", line 33
    01722. 00000 -  "invalid number"
    Error points to this line
    Code:
    WHERE AIRCRAFT.AC_NUMBER = charge_ac;
    Now for the whole code:
    Code:
    create or replace
    FUNCTION CHARTER_CHARGE 
    (
      ac_num hartmar.aircraft.ac_number%TYPE
    ) RETURN VARCHAR2 AS
      wait_hours NUMBER(3,1) :=0;
      distance NUMBER(5,0) :=0;
      charge_ac CHAR(25 byte) :='';
      charge_mod CHAR(10 byte) :='';
      charge NUMBER(6,2) :=0;
      destin CHAR(3) :='';
      landing NUMBER(7,2) :=0;
      tie NUMBER(6,2) :=0;
      charter_chg NUMBER(9,2) :=0;
    BEGIN
    SELECT CHAR_HOURS_WAIT 
      INTO wait_hours
      FROM hartmar.charter
      WHERE char_id = char_trip;
    SELECT CHAR_DISTANCE 
      INTO distance
      FROM hartmar.charter
      WHERE char_id = char_trip;
    SELECT AC_NUMBER 
      INTO charge_ac
      FROM hartmar.charter
      WHERE char_id = char_trip;
    SELECT MOD_CODE 
      INTO charge_mod
      FROM hartmar.aircraft
      WHERE AIRCRAFT.AC_NUMBER = charge_ac;
    SELECT MOD_CHG_MILE 
      INTO charge
      FROM hartmar.model
      WHERE charge = MOD_CODE;
    SELECT DESTINATION 
      INTO destin
      FROM hartmar.charter
      WHERE char_id = char_trip;
    SELECT LANDING_FEE 
      INTO landing
      FROM jpowell12.airport
      WHERE destin = DESTINATION;
    SELECT TIE_DOWN_FEE 
      INTO tie
      FROM jpowell12.airport
      WHERE destin = DESTINATION;
    IF (wait_hours > 0) THEN
      charter_chg := (((destin * charge)+ landing + tie)*1.35);
      RETURN charter_chg;
    ELSE 
      charter_chg := (((destin * charge)+ landing)*1.35);
      RETURN charter_chg;
    END IF;
    END CHARTER_CHARGE;
    And how i call:
    Code:
    SELECT CHARTER_CHARGE(charter.char_trip) Charge
    from hartmar.charter
    where char_trip = 10001;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ORA-06512: at "JPOWELL12.CHARTER_CHARGE", line 33

    which is line 33?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2012
    Posts
    24
    Line 33 is:

    WHERE AIRCRAFT.AC_NUMBER = charge_ac;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >charge_ac CHAR(25 byte) :='';
    CHARGE_AC contains non-numeric value
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2012
    Posts
    24
    charge_ac VARCHAR2(5) :='';

    I have also tried this. Still same error

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >charge_ac VARCHAR2(5) :='';

    what NUMBER does CHARGE_AC contain????????????????
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2012
    Posts
    24
    2289L
    they will always contain 4 number followed by a letter

  8. #8
    Join Date
    Oct 2012
    Posts
    24
    Is it possible that the error is from only having one casting parameter?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle throws error since the "L" is not numeric.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Oct 2012
    Posts
    24
    I found the problem. Oracle was actually stating the wrong line. The next line is where the problem was. Funny thing is, it said that the number value in the table was not a number when it was. I changed the variable type i declared for it and it works now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •