COUNT in PLSQL ORACLE

Question

I have asked this question before but I did not get any help. I want to get the count of rows in two different table given an attribute. This is my code . Instead of fetching the total count where the condition holds, I am getting the whole count of the table

    create or replace PROCEDURE p1( suburb IN varchar2 ) 
    as
        person_count NUMBER;
        property_count NUMBER;
    BEGIN
        SELECT count(*) INTO person_count
        FROM person p WHERE p.suburb = suburb ;

        SELECT count(*) INTO property_count
        FROM property pp WHERE pp.suburb = suburb ;


        dbms_output.put_line('Number of People :'|| person_count);
        dbms_output.put_line('Number of property :'|| property_count);

    END;
    /

Is there any other way to do this so that i can retrieve the real total count of people in that SUBURB

Some datas from PERSON TABLE

       PEID FIRSTNAME       LASTNAME
    ---------- -------------------- --------------------
    STREET                   SUBURB           POST TELEPHONE
    ---------------------------------------- -------------------- ---- ------------
        30 Robert       Williams
    1/326 Coogee Bay Rd.             Coogee           2034 9665-0211

        32 Lily         Roy
   66 Alison Rd.                 Randwick         2031 9398-0605

        34 Jack         Hilfgott
    17 Flood St.                 Bondi            2026 9387-0573

SOME DATA from PROPERTY TABLE

          PNO STREET                    SUBURB       POST
    ---------- ---------------------------------------- -------------------- ----
    FIRST_LIS TYPE               PEID
    --------- -------------------- ----------
        48 66 Alison Rd.                Randwick         2031
    12-MAR-11 Commercial            8

        49 1420 Arden St.               Clovelly         2031
    27-JUN-10 Commercial               82

        50 340 Beach St.                Clovelly         2031
    05-MAY-11 Commercial               38

Sorry for the way the table is looking .

This is the value I get when I run the above script.

     SQL> exec p1('Randwick')
     Number of People :50
     Number of property :33

I changed the PROCEDURE ,this is what I get .

    SQL> create or replace PROCEDURE p1( location varchar2 ) 
    IS
        person_count NUMBER;
        property_count NUMBER;
    BEGIN
        SELECT count(p.peid) INTO person_count
        FROM person p WHERE p.suburb = location ;

        SELECT count(pp.pno) INTO property_count
        FROM property pp WHERE pp.suburb = location ;


        dbms_output.put_line('Number of People :'|| person_count);
        dbms_output.put_line('Number of property :'|| property_count);

    END;
    /
      2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  
    Procedure created.

    SQL> exec p1('KINGSFORD')
    Number of People :0
    Number of property :0

    PL/SQL procedure successfully completed.

    SQL> 


    SQL> 
    SQL> exec p1('Randwick')
    Number of People :0
    Number of property :0

    PL/SQL procedure successfully completed.

    SQL> 

The solution suppose to be this

    SQL> exec p1('randwick');
    Number of People: 7
    Number of Property: 2

Answer 1

First, create indices for case-insensitive search:

CREATE INDEX idx_person_suburb_u ON person(upper(suburb))
/
CREATE INDEX idx_property_suburb_u ON property(upper(suburb))
/

Second, use prefixes for procedure parameters and local variables:

CREATE OR REPLACE PROCEDURE p1(p_location VARCHAR2) 
IS
    v_person_count    NUMBER;
    v_property_count  NUMBER; 
    v_location        VARCHAR2(32767);
BEGIN
    IF p_location IS NOT NULL THEN
      v_location := upper(p_location);
      SELECT count(*) INTO v_person_count
      FROM person WHERE upper(suburb) = v_location ;

      SELECT count(*) INTO v_property_count
      FROM property WHERE upper(suburb) = v_location ;
    ELSE
      SELECT count(*) INTO v_person_count
      FROM person WHERE upper(suburb) IS NULL;

      SELECT count(*) INTO v_property_count
      FROM property WHERE upper(suburb) IS NULL;        
    END IF;
    dbms_output.put_line('Number of People :' || v_person_count);
    dbms_output.put_line('Number of Property :' || v_property_count);
END;
/

Answer 2

Although I agree that the cause of the problem is a namespace issue between SQL and PL/SQL, in that the SQL engine has "captured" the name of the PL/SQL variable, I don't believe that changing the name of the parameter is the best approach. If you do this then you doom every developer to start prefixing every parameter name with "p_" or some other useless appendage, and to make sure that they never create a column with a P_ prefix.

If you look through the PL/SQL Supplied Packages documentation you see very few, if any, cases where Oracle themselves do this, although they have in the past done irritatingly inconsistent things like refer to table_name as "tabname".

A more robust approach is to prefix the variable name with the pl/sql procedure name when referencing it in SQL statements:

SELECT count(*)
INTO   person_count
FROM   person p WHERE p.suburb = p1.suburb ;

In your case you clearly wouldn't name your procedure "P1" so in fact you'd have something like:

SELECT count(*)
INTO   person_count
FROM   person p WHERE p.suburb = count_suburb_objects.suburb ;

Your code is now immune to variable name capture -- as a bonus your text editor might highlight all the instances where you've used a variable name in a SQL statement when you double-click on the procedure name.

Answer 3

You named the variable the same as the field. In the query, suburb is first sought in the scope of the query, and it matches the field suburb even though it doesn't use the pp table alias.

So you're actually comparing the field with itself, therefore getting all records (where suburb is NOT NULL, that is). The procedure parameter isn't used in the query at all.

The solution: change the name of the procedure parameter.

To prevent errors like this, I always use P_ as a prefix for procedure/function parameters and V_ as a prefix for local variables. This way, they never mingle with field names.