Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CREATE PACKAGE BODY

Re: CREATE PACKAGE BODY

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Tue, 04 May 1999 15:13:58 -0700
Message-ID: <372F7126.42A3E972@earthlink.net> 





Hi Muyi,



You just had couple of sintax errors,


here I fixed them and it works:





drop package lock_1_to_200 ;


create package lock_1_to_200 AS

   nl_mode  constant integer := 1;
   ss_mode  constant integer := 2;
   sx_mode  constant integer := 3;
   s_mode   constant integer := 4;
   ssx_mode constant integer := 5;




   x_mode   constant integer := 6;


   maxwait  constant integer := 32767;


   function request(id in integer,

     lockmode in integer default x_mode,
       timeout in integer default maxwait,
       release_on_commit in boolean default FALSE)
       return integer;
   function convert(id in integer,
         lockmode in integer,
             timeout in number default maxwait)
        return integer;




   function release(id in integer)


  return integer;


end lock_1_to_200;


/



drop package body lock_1_to_200;


CREATE OR REPLACE package body lock_1_to_200 AS
--   begin


   function  request(id in integer,

              lockmode in integer default x_mode,
              timeout in integer default maxwait,
              release_on_commit in boolean default FALSE)
       return integer
     AS
     begin
      if id < 1 or id > 200 then
        raise_application_error(-20000,'Lock id out of range');
--       endif;
         end if;
      return dbms_lock.request(id, lockmode, timeout, release_on_commit);
     end request;
  function convert(id in integer,
                      lockmode in integer,
                      timeout in number default maxwait)
       return integer
 AS
     begin
       if id < 1 or id > 200 then
          raise_application_error(-20000,'Lock id out of range');
--        endif;
          end if;
        return dbms_lock.convert(id, lockmode, timeout);
      end convert;




  function release(id in integer) return integer
 AS

      begin
        if id < 1 or id > 200 then
          raise_application_error(-20000,'Lock id out of range');
--        endif;
          end if;
        return dbms_lock.release(id);
      end release;




    end lock_1_to_200 ;


/



ALL ERRORS ARE REMED OUT (--) AND CORRECTION IS MADE

AFTER THEM


Good Luck!



+--------------------------------------------------------------------------+




| Vitaliy Mogilevskiy


| Senior Consultant


| CORE Technology Group, Inc.


| E-mail:    vit100gain_at_earthlink.net


| Fax :      (707) 516-2163


| Web Page:  http://home.earthlink.net/~vit100gain/index.html


|            *** Free DBA Script Library at my Web Page ***



|




| "Never wrestle a pig - you both get dirty and the pig likes it..."


| "If the only tool you have is a hammer, everything looks like a nail..."



+--------------------------------------------------------------------------+







-----Original Message-----


From: Muyi Cui [mailto:MCui_at_Dentrix.com]
Sent: Tuesday, May 04, 1999 2:33 PM


To: 'vit100gain_at_earthlink.net'


Subject: Help on package and build-in functions




Dear Vitaliy,



Thanks for the good advice you posted on the net.  I have two questions.
Hope you can help me.


1.  I tried to create a package called lock_1_to_200 in Oracle.  I run the
script as SYS.


The script is as follows:





drop package lock_1_to_200 ;


create package lock_1_to_200 AS

   nl_mode  constant integer := 1;
   ss_mode  constant integer := 2;
   sx_mode  constant integer := 3;
   s_mode   constant integer := 4;
   ssx_mode constant integer := 5;




   x_mode   constant integer := 6;


   maxwait  constant integer := 32767;


   function request(id in integer,

     lockmode in integer default x_mode,
       timeout in integer default maxwait,
       release_on_commit in boolean default FALSE)
       return integer;
   function convert(id in integer,
         lockmode in integer,
             timeout in number default maxwait)
        return integer;




   function release(id in integer)


  return integer;


end lock_1_to_200;



drop package body lock_1_to_200;


CREATE OR REPLACE package body lock_1_to_200 AS


   begin


   function  request(id in integer,

              lockmode in integer default x_mode,
              timeout in integer default maxwait,
              release_on_commit in boolean default FALSE)
       return integer
     AS
     begin
      if id < 1 or id > 200 then
        raise_application_error(-20000,'Lock id out of range');
       endif;
      return dbms_lock.request(id, lockmode, timeout, release_on_commit);
     end request;
  function convert(id in integer,
                      lockmode in integer,
                      timeout in number default maxwait)
       return integer
 AS
     begin
       if id < 1 or id > 200 then
          raise_application_error(-20000,'Lock id out of range');
        endif;
        return dbms_lock.convert(id, lockmode, timeout);
      end convert;




  function release(id in integer) return integer
 AS

      begin
        if id < 1 or id > 200 then
          raise_application_error(-20000,'Lock id out of range');
        endif;
        return dbms_lock.release(id);
      end release;




    end lock_1_to_200 ;




   I ran the script.  The create package body body has compilation error as
follows:



SQLWKS> CREATE OR REPLACE package body lock_1_to_200 AS
     2>    begin
     3>    function  request(id in integer,
     4>               lockmode in integer default x_mode,
     5>               timeout in integer default maxwait,
     6>               release_on_commit in boolean default FALSE)
     7>        return integer
     8>      AS
     9>      begin
    10>       if id < 1 or id > 200 then
    11>         raise_application_error(-20000,'Lock id out of range');
    12>        endif;
    13>       return dbms_lock.request(id, lockmode, timeout,
release_on_commit);
    14>      end request;
    15>   function convert(id in integer,
    16>                       lockmode in integer,
    17>                       timeout in number default maxwait)
    18>        return integer
    19>  AS
    20>      begin
    21>        if id < 1 or id > 200 then
    22>           raise_application_error(-20000,'Lock id out of range');
    23>         endif;
    24>         return dbms_lock.convert(id, lockmode, timeout);
    25>       end convert;
    26>   function release(id in integer) return integer
    27>  AS
    28>       begin
    29>         if id < 1 or id > 200 then
    30>           raise_application_error(-20000,'Lock id out of range');
    31>         endif;
    32>         return dbms_lock.release(id);
    33>       end release;
    34>     end lock_1_to_200 ;




    35>


MGR-00072: Warning: PACKAGE BODY LOCK_1_TO_200 created with compilation
errors.





I then used schema manager to recompile it and I still got these errors:




PLS-00103: Encounted the symbol"REQUEST" when expecting one of the
following: := . [ @ %;


 The symbol ":=" was substituted for "REQUEST" to continue.



PLS-00103: Encounted the symbol"DEFAULT" when expecting one of the
following: := . [ @ % - + mod rem.


 an exponent[**] and or ||;


 The symbol "." was inserted before "DEFAULT" to continue.





Do you know what is happening?




2. I tried to call a build-in function (request) in DBMS_LOCK package. I ran
the script as user sys.


 select dbms_lock.request(2,2,2,FALSE) from dual;



It gave me this error:




SQLWKS> select dbms_lock.request(2,2,2,FALSE) from dual


     2>


select dbms_lock.request(2,2,2,FALSE) from dual

                               *




ORA-00904: invalid column name





I would appreciate any advice.



Thanks.



Muyi Cui


Software Engineer and DBA


Dentrix Inc




mcui_at_cc.usu.edu wrote:


> I copied a package definition from Oracle server shown below.  But
>  the package body always has compilation error.  Can somebody tell
> me what is wrong with my script?  If no error, what can be the cause
> of compilation error.
>
> SQLWKS> CREATE OR REPLACE package body lock_1_to_200 AS
>      2>    begin
>      3>    function  request(id in integer,
>      4>                 lockmode in integer default x_mode,
>      5>                 timeout in integer default maxwait,
>      6>                 release_on_commit in boolean default FALSE)
>      7>         return integer
>      8>      AS
>      9>      begin
>     10>       if id < 1 or id > 200 then
>     11>         raise_application_error(-20000,'Lock id out of range');
>     12>        endif;
>     13>       return dbms_lock.request(id, lockmode, timeout,
> release_on_commit);
>     14>      end request;
>     15>   function convert(id in integer,
>     16>                       lockmode in integer,
>     17>                       timeout in number default maxwait)
>     18>        return integer
>     19>         AS
>     20>      begin
>     21>        if id < 1 or id > 200 then
>     22>           raise_application_error(-20000,'Lock id out of range');
>     23>         endif;
>     24>         return dbms_lock.convert(id, lockmode, timeout);
>     25>       end convert;
>     26>   function release(id in integer) return integer
>     27>         AS
>     28>       begin
>     29>         if id < 1 or id > 200 then
>     30>           raise_application_error(-20000,'Lock id out of range');
>     31>         endif;
>     32>         return dbms_lock.release(id);
>     33>       end release;
>     34>     end lock_1_to_200 ;
>     35>
> MGR-00072: Warning: PACKAGE BODY LOCK_1_TO_200 created with compilation errors.
>
> error from Oracle schema manager:
>
> PLS-00103: Encounted the symbol"REQUEST" when expecting one of the following:
> := . [ @ %;
>         The symbol ":=" was substituted for "REQUEST" to continue.
>
> PLS-00103: Encounted the symbol"DEFAULT" when expecting one of the following:
> := . [ @ % - + mod rem.
>  an exponent[**] and or ||;
>         The symbol "." was inserted before "DEFAULT" to continue.




Received on Tue May 04 1999 - 17:13:58 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US