Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-11
19
Medium Priority
?
6,323 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1600 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.

670 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