CREATE OR REPLACE
PACKAGE BODY "EDM_PACK" AS
PROCEDURE get_inc(
i_user_id IN NUMBER,
o_inc OUT ginc)
IS
CURSOR id_name IS SELECT id,name FROM groups WHERE user_id = i_user_id;
i_n id_name%ROWTYPE;
temp_count NUMBER;
temp_id NUMBER;
temp_name VARCHAR2(2000);
temp_sql VARCHAR2(2000);
temp_exist NUMBER;
BEGIN
OPEN id_name;
LOOP
FETCH id_name INTO i_n;
EXIT WHEN id_name%NOTFOUND;
SELECT COUNT(*) INTO temp_exist FROM USER_TABLES WHERE ;
IF temp_exist=1 THEN
tmp_sql := 'SELECT COUNT(*) FROM '||'EMAILS_'||i_n.id;
EXECUTE IMMEDIATE tmp_sql INTO temp_count;
ELSE
temp_count := -1;
END If;
INSERT INTO tmp NOLOGGING VALUES('||i_n.id||','||i_n.name||','||temp_count||');
END LOOP;
CLOSE id_name;
OPEN o_inc FOR SELECT group_id,group_name,email_count FROM tmp;
EXECUTE Immediate 'DROP TABLE tmp';
END get_inc;
END;