Solved

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

Posted on 2006-07-11
19
4,994 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:m8rix
  • 7
  • 7
  • 5
19 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087217
Have you declared these 2 fields in your package declaration?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087241
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
 
LVL 10

Author Comment

by:m8rix
ID: 17087290
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
 
LVL 10

Author Comment

by:m8rix
ID: 17087308
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087313
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17087323
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087324
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17087328
>>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
 
LVL 19

Expert Comment

by:actonwang
ID: 17087331
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Accepted Solution

by:
sathyagiri earned 400 total points
ID: 17087333
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087373
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
 
LVL 10

Author Comment

by:m8rix
ID: 17087382
@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
 
LVL 10

Author Comment

by:m8rix
ID: 17087391
Yes the "dbms_output.put_line(GL.MonthRunDate);" works so how come not the above
0
 
LVL 10

Author Comment

by:m8rix
ID: 17087396
Should I create a GetMonth and GetWeek function?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087444
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
 
LVL 10

Author Comment

by:m8rix
ID: 17087457
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17087499
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17087501
well, ...
0
 
LVL 10

Author Comment

by:m8rix
ID: 17087534
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now