how to create a stored procedure in oracle

See more:

Oracle

Hi,

i just need to create an SP which returns the count fo multiple tables.so i just defined the procedure like this,

--created my package like this

CREATE OR REPLACE PACKAGE COUNTTABLE AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE SAMPLECOUNT
(
    cur_COUNT OUT T_CURSOR
);
END COUNTTABLE;

--defined the procedure like this

CREATE OR REPLACE PACKAGE BODY COUNTTABLE AS
PROCEDURE SAMPLECOUNT
(
    cur_COUNT OUT T_CURSOR
)
IS
BEGIN
    OPEN cur_COUNT FOR
    SELECT  (
        SELECT COUNT(*)
        FROM   PROJECTS
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   PROJECTS1
        ) AS count2
FROM    dual
END SAMPLECOUNT;
END COUNTTABLE;

while i am executing, its throwing me an error saying PL/SQL cannot find the program unit being called "MyDBA.Counttable. MyDBA is my database and counttable is the package. but in the SP if i specify to take the count of a single table instead of multiple tables, its working fine. i am new to this Oracle.so can anyone please help me in doing this.........?