Solved

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

Posted on 2006-07-11
19
5,933 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

624 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