title: Oracle Packages and using a select statement in a public variable
body: <p>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) </p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>the above example are the ways i've been trying (amongst others), not at the same time but individually. </p>
  
  <p>We get given the code to test it: (must not change this testing code) </p>
  
  <pre><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;
  </code></pre>
  
  <p>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. </p>
  
  <p>there is more code within the package with procedures and functions:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>this will throw:</p>
  
  <p><code>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    &lt;an identifier&gt; &lt;a double-quoted delimited-identifier&gt;    &lt;a bind variable&gt; &lt;&lt; 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.</code></p>
  
  <p>im normally alright with working it out from the error messages but oracle error messages may as well be written in dutch to me :/ </p>