Oracle Packages and using a select statement in a public variable

Question

Have been searching for ages to no avail. Part of an assessment it specifies that we must declare a public variable WITHIN a package (so don't try telling me to make it a stand alone function...) that is the number of rows from another table ("SELECT COUNT(*) FROM A2_GUESTS" is the hint)

I can set a public variable easily enough as a static number, however if i try to add the select statement it throws an error.

If I try to assign it in the package body then it also throws an error, if I wrap it within "begin" and "end" it terminates the package body too early.

CREATE OR REPLACE PACKAGE Beachcomber
AS
    v_Guest_Count NUMBER := 0; 
END Beachcomber;
/
CREATE OR REPLACE PACKAGE BODY Beachcomber IS
    SELECT COUNT(*) INTO v_Guest_Count FROM A2_GUESTS; -- doesn't work
    v_Guest_Count NUMBER := SELECT COUNT(*) FROM A2_GUESTS; -- doesn't work
    BEGIN
        v_Guest_Count NUMBER := SELECT COUNT(*) FROM A2_GUESTS; 
    END; -- doesn't work - ends the package prematurely
END Beachcomber;

the above example are the ways i've been trying (amongst others), not at the same time but individually.

We get given the code to test it: (must not change this testing code)

PROMPT 
PROMPT TESTING: Initialisation of the v_Guest_Count variable.  Expect 81.
BEGIN
   DBMS_OUTPUT.PUT_LINE('v_Guest_Count has been initialised to: '||TO_CHAR(BEACHCOMBER.v_Guest_Count));
END;

any help is greatly appreciated, i found someone asked this here once back in 2015 but the only answer was given as make it a function and they adjusted the testing code so thats less than helpful.

there is more code within the package with procedures and functions:

CREATE OR REPLACE PACKAGE Beachcomber
IS
    v_Guest_Count NUMBER := 0; 
    PROCEDURE ADD_GUEST 
        (p_guest_name A2_GUESTS.guest_name%TYPE,
        p_guest_address A2_GUESTS.guest_address%TYPE);
END Beachcomber;
/
    CREATE OR REPLACE PACKAGE BODY Beachcomber IS
    BEGIN 
    SELECT COUNT(*) INTO v_Guest_Count FROM A2_GUESTS;

    PROCEDURE ADD_GUEST
        (p_guest_name A2_GUESTS.guest_name%TYPE,
        p_guest_address A2_GUESTS.guest_address%TYPE)
        IS BEGIN
        INSERT INTO A2_GUESTS (Guest_ID, Guest_Name, Guest_Address) 
            VALUES (guest_id_seq.NEXTVAL, p_guest_name, p_guest_address);
        v_Guest_Count := v_Guest_Count +1;
    END ADD_GUEST;
END Beachcomber;

this will throw:

5/5 PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "declare" was substituted for "PROCEDURE" to continue.

im normally alright with working it out from the error messages but oracle error messages may as well be written in dutch to me :/

Answer 1

We can include initialising code in a package by putting it at the end of the body. It takes the form of a BEGIN block which is terminated by the final END of the package body.

create or replace package BEACHCOMBER is
    v_Guest_Count pls_integer;
    function get_cnt return number;
end BEACHCOMBER;
/
create or replace package body BEACHCOMBER is
    function get_cnt return number
    is
    begin
        return v_Guest_Count;
    end get_cnt;

<< init_block >>
begin
    select count(*) 
    into v_Guest_Count
    from A2_GUESTS;
end BEACHCOMBER;
/

The code under the label << init_block >> is run the first time the package is invoked. This includes referencing the public variable. This code not run again in the session, unless the package is recompiled, which discards state.

Here is my test script. I have published this as a working demo on Oracle LiveSQL (because DBMS_OUTPUT) but you need a free Oracle account to run it. Check it out

Test set up

drop table A2_GUESTS
/

create table A2_GUESTS (id number);

insert into A2_GUESTS select level from dual connect by level <=23;

create or replace package BEACHCOMBER is
    v_Guest_Count pls_integer;
    function get_cnt return number;
end BEACHCOMBER;
/
create or replace package body BEACHCOMBER is
    function get_cnt return number
    is
    begin
        return v_Guest_Count;
    end get_cnt;
begin
    select count(*) 
    into v_Guest_Count
    from A2_GUESTS;
end BEACHCOMBER;
/

Here are the tests;

begin
    dbms_output.put_line('count = ' || BEACHCOMBER.v_Guest_Count);
end;
/

insert into A2_GUESTS values (42)
/

select BEACHCOMBER.get_cnt 
from dual
/

alter package BEACHCOMBER compile body
/

select BEACHCOMBER.get_cnt 
from dual
/

Answer 2

You have to set the Package variable in the package body. You need not declare another variable locally in the body.

CREATE OR REPLACE PACKAGE BODY Beachcomber IS
    BEGIN
        SELECT COUNT(*) INTO v_Guest_Count FROM A2_GUESTS; 
END Beachcomber;
/

You may then access that variable in any other PL/SQL block.

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE(Beachcomber.v_Guest_Count); 
end;
/

0

PL/SQL procedure successfully completed.

EDIT

You should put the queries inside the procedure as you would be calling the procedure externally.

CREATE OR REPLACE PACKAGE BODY beachcomber IS
     PROCEDURE add_guest (
          p_guest_name      a2_guests.guest_name%TYPE,
          p_guest_address   a2_guests.guest_address%TYPE
     )
          IS
     BEGIN
          SELECT COUNT(*)
           INTO v_guest_count
          FROM a2_guests;

          INSERT INTO a2_guests (
               guest_id,
               guest_name,
               guest_address
          ) VALUES (
               guest_id_seq.NEXTVAL,
               p_guest_name,
               p_guest_address
          );
          v_guest_count := v_guest_count + 1;
     END add_guest;
END beachcomber;
/

EDIT2 : using a main procedure for initialisation.

CREATE OR REPLACE PACKAGE beachcomber IS
     v_guest_count NUMBER := 0;
     PROCEDURE main;
     PROCEDURE add_guest (
          p_guest_name      a2_guests.guest_name%TYPE,
          p_guest_address   a2_guests.guest_address%TYPE
     );
END beachcomber;
/ 
CREATE OR REPLACE PACKAGE BODY beachcomber IS
     PROCEDURE add_guest (
          p_guest_name      a2_guests.guest_name%TYPE,
          p_guest_address   a2_guests.guest_address%TYPE
     )
          IS
     BEGIN
          INSERT INTO a2_guests (
               guest_id,
               guest_name,
               guest_address
          ) VALUES (
               guest_id_seq.NEXTVAL,
               p_guest_name,
               p_guest_address
          );
          v_guest_count := v_guest_count + 1;
     END add_guest;
     PROCEDURE main
          IS
     BEGIN
          SELECT COUNT(*)
          INTO v_guest_count
          FROM a2_guests;
     END main;
END beachcomber;
/

Execution of procedure.

BEGIN
  beachcomber.main;
  beachcomber.add_guest('Sherlock','221b baker street');
END;
/