PLS-00371: at most one declaration for 'VARIABLE_NAME' is permitted in the declaration section

Dear Experts,

I am trying to set up a package that will store some session global variables. I have hit a wall and I hope that someone here can help.

here is my PACKAGE BODY

CREATE OR REPLACE
PACKAGE BODY GL IS
  MonthRunDate DATE DEFAULT CURRENT_DATE;
  WeekRunDate DATE DEFAULT CURRENT_DATE;

  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRunDate := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRunDate := P_Date;
  END SetWeek;
END GL;

and gere are the ERROR MESSAGES
Error(7,5): PLS-00371: at most one declaration for 'MONTHRUNDATE' is permitted in the declaration section
Error(12,5): PLS-00371: at most one declaration for 'WEEKRUNDATE' is permitted in the declaration section

Reg's,
m8rix
LVL 10
m8rixAsked:
Who is Participating?
 
sathyagiriConnect With a Mentor Commented:
Oops made some typos again
CREATE OR REPLACE PACKAGE GL IS
  MonthRunDate DATE DEFAULT CURRENT_DATE;
  WeekRunDate DATE DEFAULT CURRENT_DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
END GL;
/
CREATE OR REPLACE
PACKAGE BODY GL IS
  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRunDate := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRunDate := P_Date;
  END SetWeek;
END GL;
0
 
sathyagiriCommented:
Have you declared these 2 fields in your package declaration?
0
 
sathyagiriCommented:
I guess you have probably declared these two fields have been declared in your package specification probably.

Check your package specification which would be something like
create or replace package GL as
//Var declarations
end;


If that's the case change the field names in your package body to something else for ex


CREATE OR REPLACE
PACKAGE BODY GL IS
  MonthRun_Date DATE DEFAULT CURRENT_DATE;
  WeekRun_Date DATE DEFAULT CURRENT_DATE;

  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRun_Date := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRun_Date := P_Date;
  END SetWeek;
END GL;
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
m8rixAuthor Commented:
this is how I declared the package:

CREATE OR REPLACE PACKAGE GL IS
  MonthRunDate DATE;
  WeekRunDate DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
END GL;
0
 
m8rixAuthor Commented:
Ok changing the variable names in the body worked. That is, it stopped the errors

but what I hoped to acheive is that the variables would be directly accessable (ie: SELECT GL.MonthRunDate FROM DUAL;)
0
 
sathyagiriCommented:
That's why u're getting the error.

Remove the declararion of MonthRunDate  and WeekRunDate from your package body.
Change your package body as follows

CREATE OR REPLACE
PACKAGE BODY GL IS
  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRun_Date := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRun_Date := P_Date;
  END SetWeek;
END GL;
0
 
actonwangCommented:
create or replace package simplepackage as
 a varchar2(10) := 'public';
end;
/

create or replace package body simplepackage as
 a varchar2(10) := 'private';
 
 procedure atest
 as
 begin
   a := 'hello';
 end;
end;
/


7/4      PLS-00371: at most one declaration for 'A' is permitted in the
         declaration section

you would do:
(1) you need to remove one of declarations for MonthRunDate or WeekRunDate
(2)  >> MonthRun_Date := P_Date;
      change to :
        GL.MonthRun_Date := P_Date;

       in this case, you would only access to public MonthRun_Date.
 
0
 
sathyagiriCommented:
Oops I guess this what you want
CREATE OR REPLACE PACKAGE GL IS
  MonthRunDate DATE DEFAULT CURRENT_DATE;
  WeekRunDate DATE DEFAUL CURREN_DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
END GL;
/
CREATE OR REPLACE
PACKAGE BODY GL IS
  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRunDate := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRunDate := P_Date;
  END SetWeek;
END GL;


Now you should be able to display these two variables at the session level
0
 
actonwangCommented:
>>CREATE OR REPLACE PACKAGE GL IS
  MonthRunDate DATE;
  WeekRunDate DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
END GL;

you only need to do:

CREATE OR REPLACE PACKAGE GL IS
  MonthRunDate DATE DEFAULT CURRENT_DATE;
  WeekRunDate DATE DEFAULT CURRENT_DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
END GL;
/

CREATE OR REPLACE
PACKAGE BODY GL IS

  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRunDate := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRunDate := P_Date;
  END SetWeek;
/
END GL;
0
 
actonwangCommented:
CREATE OR REPLACE PACKAGE GL IS
  MonthRunDate DATE DEFAULT CURRENT_DATE;
  WeekRunDate DATE DEFAULT CURRENT_DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
END GL;
/

CREATE OR REPLACE
PACKAGE BODY GL IS

  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRunDate := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRunDate := P_Date;
  END SetWeek;
END GL;
/
0
 
sathyagiriCommented:
After executing the above, call your procedure

exec GL.SetMonth('08-JUL-2006');
exec GL.SetWeek('10-JUL-2006');

Then run this
begin
dbms_output.put_line(GL.MonthRunDate);
dbms_output.put_line(GL.WeekRunDate);
end;
0
 
m8rixAuthor Commented:
@actonwang
>>     change to :
        GL.MonthRun_Date := P_Date;
I tried that and it diodn't work.


@sathyagiri
Ok I did that sathyagiri and no errors on compile, but I still can't retreive the value:

SQL> SELECT GL.MonthRunDate FROM DUAL;
SELECT GL.MonthRunDate FROM DUAL
       *
ERROR at line 1:
ORA-06553: PLS-221: 'MONTHRUNDATE' is not a procedure or is undefined
0
 
m8rixAuthor Commented:
Yes the "dbms_output.put_line(GL.MonthRunDate);" works so how come not the above
0
 
m8rixAuthor Commented:
Should I create a GetMonth and GetWeek function?
0
 
sathyagiriCommented:
You could create a GetMonth and GetWeek if you want to.


>>Yes the "dbms_output.put_line(GL.MonthRunDate);" works so how come not the above
Coz the global variables are accessible only with in the pl/sql engine
0
 
m8rixAuthor Commented:
Ok this works:

CREATE OR REPLACE
PACKAGE GL IS
  MonthRunDate DATE DEFAULT CURRENT_DATE;
  WeekRunDate DATE DEFAULT CURRENT_DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
  FUNCTION GetWeek RETURN DATE;
  FUNCTION GetMonth RETURN DATE;
END GL;
CREATE OR REPLACE
PACKAGE BODY GL IS
  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRunDate := P_Date;
  END SetMonth;
  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRunDate := P_Date;
  END SetWeek;
  FUNCTION GetWeek RETURN DATE IS
  BEGIN
    RETURN WeekRunDate;
  END GetWeek;
  FUNCTION GetMonth RETURN DATE IS
  BEGIN
    RETURN MonthRunDate;
  END GetMonth;
END GL;


---------------------------------------------------------------------------
SQL> exec GL.SetWeek('10-JUL-2006');

PL/SQL procedure successfully completed.

SQL> exec GL.SetMonth('08-JUL-2006');

PL/SQL procedure successfully completed.

SQL> SELECT GL.GetMonth FROM DUAL;

GETMONTH
---------
08/JUL/06

SQL> SELECT GL.GetWeek FROM DUAL;

GETWEEK
---------
10/JUL/06

---------------------------------------------------------------------------

Thanks for your help

Reg's,
m8
0
 
actonwangCommented:
try my other post:

 CREATE OR REPLACE PACKAGE GL IS
  MonthRunDate DATE DEFAULT CURRENT_DATE;
  WeekRunDate DATE DEFAULT CURRENT_DATE;
  PROCEDURE SetMonth(P_Date DATE);
  PROCEDURE SetWeek(P_Date DATE);
END GL;
/

CREATE OR REPLACE
PACKAGE BODY GL IS

  PROCEDURE SetMonth(P_Date DATE) IS
  BEGIN
    MonthRunDate := P_Date;
  END SetMonth;

  PROCEDURE SetWeek(P_Date DATE) IS
  BEGIN
    WeekRunDate := P_Date;
  END SetWeek;
END GL;
/
0
 
actonwangCommented:
well, ...
0
 
m8rixAuthor Commented:
Looks ok, but it is practically the same as what sathyagiri suggested: (http:Q_21916043.html#17087324) looks like he/she beat you to it by about 2 min :(

I appreciate your help though.
Cheers,
m8rix
0
All Courses

From novice to tech pro — start learning today.