Solved

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

Posted on 2006-07-11
19
5,340 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
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 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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL/SQL Display based on value 4 39
Select and Insert Query running slow 4 45
best datatype for oracle table email creation 8 55
oracle differnce between two timestamps 5 31
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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