Re: PL/SQL Delete Error -- Urgent!

From: Micha³ Kumela <mkumela_at_aptus.pl>
Date: 2000/07/21
Message-ID: <39782FC1.2B310FDC@aptus.pl>#1/1

John,
I found few syntax errors in your progam and I corrected them. Nevertheless you you will not be able to compile your package, because you declared pragma WNDS for function get_id, although this function modifies the database!!! It causes compilation errors. Try to remove these lines.
I enclosed correct version of the package below:

create or replace package my_package as

    function get_id return number;
-- pragma restrict_references(get_id, WNDS); -- pragma restrict_references(my_package, WNDS, RNPS); end my_package;
/

create or replace package body my_package as

    function get_id return number is

        min_id number;
    begin

        select min(id) into min_id from my_table;
        delete from my_table where id = min_id;
        return min_id;

    end get_id;

end my_package;
/

Regards,
Michal

John Chen wrote:
>
> Hi All,
>
> I have a table called "my_table" with one field "ID". I try to write a
> PL/SQL function to get the minimal value of "ID" and then delete this
> record. However I
> got an error message: Warning:
> Package Body created with compilation errors.
>
> If I do not include the "delete", everything is OK and it returns the
> minimal value.
> I am using Oracle 8iR2 on Red Hat Linux 6.1
> Please help!!
>
> The PL/SQL script is as follows:
> ================================
> create or replace package my_package as
> function get_id return number;
> pragma restrict_references(get_id, WNDS);
> pragma restrict_references(my_package, WNDS, RNPS);
> end opensrs_package;
> /
>
> create or replace package body my_package as
>
> function get_id return number is
> min_id number;
> begin
> select min(id) into min_id from my_table;
> delete from min_id where id = min_id;
> return min_id;
> end get_id;
>
> end my_package;
> /
>
> John Chen (jchen_at_tucows.com)
> ----------------------------
Received on Fri Jul 21 2000 - 00:00:00 CDT