Re: create function problems

From: David Roberts <big.dave.roberts_at_googlemail.com>
Date: Fri, 23 Sep 2011 10:39:18 +0100
Message-ID: <CAOCOAVL-NBgV0A6pgPovJEUuzE4AVLyYVfC4njStfBt_H7S_sQ_at_mail.gmail.com>

Missing is:
SQL> create table iva(
  2 DATA_INIZIO DATE NOT NULL,
  3 DATA_FINE DATE,
  4 ALIQUOTA NUMBER(3,1) NOT NULL); Table created.

SQL> CREATE OR REPLACE Function aliquota_iva ( data IN date)

  2     RETURN number
  3     cnumber number(3,1);
  4     BEGIN SELECT aliquota INTO cnumber FROM iva WHERE data BETWEEN
  5  data_inizio AND data_fine OR data >= data_inizio AND data_fine IS NULL;
  6     RETURN (cnumber);

  7 END;
  8 /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION ALIQUOTA_IVA:

LINE/COL ERROR

-------- -----------------------------------------------------------------
3/4      PLS-00103: Encountered the symbol "CNUMBER" when expecting one of
         the following:
         . _at_ % ; is authid as cluster order using external character
         deterministic parallel_enable pipelined aggregate
         The symbol "is" was substituted for "CNUMBER" to continue.

SQL> edit
Wrote file afiedt.buf

  1 CREATE OR REPLACE Function aliquota_iva ( data IN date)   2 RETURN number
  3 IS

  4     cnumber number(3,1);
  5     BEGIN SELECT aliquota INTO cnumber FROM iva WHERE data BETWEEN
  6  data_inizio AND data_fine OR data >= data_inizio AND data_fine IS NULL;
  7     RETURN (cnumber);

  8* END;
SQL> / Function created.

SQL> On Fri, Sep 23, 2011 at 10:28 AM, jo <jose.soares_at_sferacarta.com> wrote:

> Hi all,
>
> I think I need some help to create an Oracle function.
> I created the table IVA like this one:
>
> SQL> desc iva;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> DATA_INIZIO NOT NULL DATE
> DATA_FINE DATE
> ALIQUOTA NOT NULL NUMBER(3,1)
>
> SQL> select * from iva;
>
> DATA_INIZI DATA_FINE ALIQUOTA
> ---------- ---------- ----------
> 1900-01-01 2011-09-16 20
> 2011-09-16 21
>
> and I want to create a function to returns the value of "aliquota" valid
> at a given date.
>
>
> SQL> CREATE OR REPLACE Function aliquota_iva ( data IN date)
> 2 RETURN number
> 3 cnumber number(3,1);
> 4 BEGIN SELECT aliquota INTO cnumber FROM iva WHERE data BETWEEN
> data_inizio AND data_fine OR data >= data_inizio AND data_fine IS NULL;
> 5 RETURN (cnumber);
> 6 END;
> 7 /
>
> Warning: Function created with compilation errors.
>
>
> What's wrong with this code?
>
> j
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 23 2011 - 04:39:18 CDT