package to execute sql statements

Here is my code:

create or replace package mail_data_extract is
  PROCEDURE DELETE_TABLE(i_table_name   IN   VARCHAR2);
  PROCEDURE EXECUTE_SQL(i_table_name   IN   varchar2);
  PROCEDURE populate_mail_data;
end mail_data_extract;
/
create or replace package body mail_data_extract as

  PROCEDURE EXECUTE_SQL(i_table_name   IN   varchar2)
     is
          t_cursor_name       INTEGER;
          t_execution_status  INTEGER;
         
     begin


          t_cursor_name := dbms_sql.open_cursor;

          DBMS_SQL.PARSE(t_cursor_name, i_table_name,dbms_sql.native);
          t_execution_status := dbms_sql.execute(t_cursor_name);

          DBMS_SQL.close_cursor(t_cursor_name);


     end EXECUTE_SQL;

  PROCEDURE DELETE_TABLE(i_table_name   IN   VARCHAR2)
     is
          t_cursor_name       INTEGER;
          t_execution_status  INTEGER;
          t_count             INTEGER;
         
     begin

          t_cursor_name := dbms_sql.open_cursor;

          -- check Table status
          t_count := 0;

          SELECT count(1)
            INTO t_count
            FROM user_tables
           WHERE TABLE_NAME = i_table_name;

          IF t_count > 0
          THEN
               DBMS_SQL.PARSE(t_cursor_name, 'DROP TABLE ' || i_table_name,dbms_sql.native);
               t_execution_status := dbms_sql.execute(t_cursor_name);
          END IF;

          DBMS_SQL.close_cursor(t_cursor_name);


     end DELETE_TABLE;
     



procedure populate_mail_data
is

begin
delete_table('EMAIL_LIST');
delete_table('EMAIL_LIST_1');
delete_table('EMAIL_LIST_2');



execute_sql('CREATE TABLE EMAIL_LIST_1
AS
(select k.pp_insured_code,
       q.enty_email_id,
       q.enty_first_name,
       q.enty_last_name,
       max(decode(k.rank, 1, k.pp_petname, null)) || " " ||
       max(decode(k.rank, 2, k.pp_petname, null)) || " " ||
       max(decode(k.rank, 3, k.pp_petname, null)) || " " ||
       max(decode(k.rank, 4, k.pp_petname, null)) || " " ||
       max(decode(k.rank, 5, k.pp_petname, null)) || " " ||
       max(decode(k.rank, 6, k.pp_petname, null)) petname,
       q.enty_address1,
       q.enty_address2,
       q.enty_city,
       q.enty_state,
       q.enty_zip
  from (select pp_insured_code,
               dense_rank() over(partition by pp_insured_code order by pp_petname) as rank,
               pp_petname
          from renvpi.policy_pets
         group by pp_insured_code, pp_petname) k,
       renvpi.policy_register a,
       renvpi.entity_address_master q
 where a.preg_insured = k.pp_insured_code and a.preg_insured = q.enty_code and
       q.enty_group_code = "INSURED"
  --    AND a.preg_insured="100030"
       and a.preg_status = "I" and a.preg_end_date > sysdate and
       rtrim(q.enty_email_id, " ") is not null
 group by q.enty_email_id,
          q.enty_first_name,
          q.enty_last_name,
          k.pp_insured_code,
          q.enty_address1,
          q.enty_address2,
          q.enty_city,
          q.enty_state,
          q.enty_zip)');

/*
execute_sql('CREATE TABLE EMAIL_LIST_2 AS
(select k.pp_insured_code,
q.enty_email_id,
q.enty_first_name,
q.enty_last_name,
max(decode(k.rank, 1, k.pp_petname, null)) || " " ||
max(decode(k.rank, 2, k.pp_petname, null)) || " " ||
max(decode(k.rank, 3, k.pp_petname, null)) || " " ||
max(decode(k.rank, 4, k.pp_petname, null)) || " " ||
max(decode(k.rank, 5, k.pp_petname, null)) || " " ||
max(decode(k.rank, 6, k.pp_petname, null)) petname,
q.enty_address1,
q.enty_address2,
q.enty_city,
q.enty_state,
q.enty_zip
from (select pp_insured_code,
dense_rank() over(partition by pp_insured_code order by pp_petname) as rank,
pp_petname
from renvpi.policy_pets
group by pp_insured_code, pp_petname) k,
renvpi.policy_register a,
renvpi.entity_address_master q
where a.preg_insured = k.pp_insured_code and
a.preg_insured = q.enty_code and q.enty_group_code = "INSURED"
--AND a.preg_insured="100030"
and a.preg_status = "I" and a.preg_end_date > sysdate and
rtrim(q.enty_email_id, " ") is null
group by q.enty_email_id,
q.enty_first_name,
q.enty_last_name,
k.pp_insured_code,
q.enty_address1,
q.enty_address2,
q.enty_city,
q.enty_state,
q.enty_zip)');

execute_sql('CREATE TABLE EMAIL_LIST AS (SELECT * FROM EMAIL_LIST_1 UNION SELECT * FROM EMAIL_LIST_2)');


--SELECT * FROM EMAIL_LIST


execute_sql('ALTER TABLE EMAIL_LIST add (VRCCC         VARCHAR2(10))');
execute_sql('ALTER TABLE EMAIL_LIST add (CANCER        VARCHAR2(10))');
execute_sql('ALTER TABLE EMAIL_LIST add (SUPPLEMENTAL  VARCHAR2(10))');
execute_sql('ALTER TABLE EMAIL_LIST add (PLAN_TYPE     VARCHAR2(10))');
execute_sql('ALTER TABLE EMAIL_LIST add (YEAR_ACTIVE   DATE)');
execute_sql('ALTER TABLE EMAIL_LIST add (PETS_COUNT    NUMBER)');

-- Update VRCC



UPDATE EMAIL_LIST x
   SET x.vrccc = 'YES'
where exists(SELECT '' from renvpi.policy_register a, renvpi.ar_pre_iss_endr_rein b
                where a.preg_policy_no =  b.apier_policy_no
                  and a.preg_policy_renew_no =  b.apier_policy_renew_no
                  and a.preg_insured = x.pp_insured_code  and b.apier_product_type = 'VRCC'
                  and a.preg_end_date > sysdate
                  and rownum < 2);
               


UPDATE EMAIL_LIST x
   SET x.vrccc = 'NO' WHERE x.vrccc IS NULL;

COMMIT;


-- Update CANCER

UPDATE EMAIL_LIST x
   SET x.cancer = 'YES'
where exists(SELECT '' from renvpi.policy_register a, renvpi.ar_pre_iss_endr_rein b
                where a.preg_policy_no =  b.apier_policy_no
                  and a.preg_policy_renew_no =  b.apier_policy_renew_no
                  and a.preg_insured = x.pp_insured_code  and b.apier_product_type like 'CANCC%'
                  and a.preg_end_date > sysdate
                  and rownum < 2);
               


UPDATE EMAIL_LIST x
   SET x.cancer = 'NO' WHERE x.cancer IS NULL;



COMMIT;

-- Update supplemental_routine

UPDATE EMAIL_LIST x
   SET x.supplemental = 'YES'
where exists(SELECT '' from renvpi.policy_register a, renvpi.ar_pre_iss_endr_rein b
                where a.preg_policy_no =  b.apier_policy_no
                  and a.preg_policy_renew_no =  b.apier_policy_renew_no
                  and a.preg_insured = x.pp_insured_code  and b.apier_product_type = 'AVIANVRC'
                  and a.preg_end_date > sysdate
                  and rownum < 2);


UPDATE EMAIL_LIST x
   SET x.supplemental = 'NO' WHERE x.supplemental IS NULL;


COMMIT;

-- Update plan_type

UPDATE EMAIL_LIST x
   SET x.Plan_Type = 'SUPERIOR'
where exists(SELECT '' from renvpi.policy_register a, renvpi.ar_pre_iss_endr_rein b
                where a.preg_policy_no =  b.apier_policy_no
                  and a.preg_policy_renew_no =  b.apier_policy_renew_no
                  and a.preg_insured = x.pp_insured_code  and b.apier_product_type = 'E50'
                  and a.preg_end_date > sysdate
                  and rownum < 2);


UPDATE EMAIL_LIST x
   SET x.Plan_Type = 'STANDARD'
where x.plan_type IS NULL  and exists(SELECT '' from renvpi.policy_register a, renvpi.ar_pre_iss_endr_rein b
                where a.preg_policy_no =  b.apier_policy_no
                  and a.preg_policy_renew_no =  b.apier_policy_renew_no
                  and a.preg_insured = x.pp_insured_code  and b.apier_product_type = 'C50'
                  and a.preg_end_date > sysdate
                  and rownum < 2);


COMMIT;



-- Update years_active
UPDATE EMAIL_LIST x
   SET x.year_active = (SELECT min(a.preg_begin_date)
                          from renvpi.policy_register a where a.preg_insured =  x.pp_insured_code
                           and a.preg_end_date > sysdate);


COMMIT;

-- update pets count

delete_table('EMAIL_PET_COUNT');

execute_sql('CREATE TABLE EMAIL_PET_COUNT AS (select g.pp_insured_code,count(1) pet_count from renvpi.policy_pets g where g.pp_end_date > sysdate group by g.pp_insured_code)');

execute_sql('CREATE INDEX EMAIL_PET_COUNT_1 ON EMAIL_PET_COUNT(pp_insured_code)');

UPDATE EMAIL_LIST x
   SET x.pets_count = (SELECT pet_count
                          from EMAIL_PET_COUNT u
                          where u.pp_insured_code =  x.pp_insured_code);
                           


COMMIT;


-- remove the purge list

execute_sql('CREATE TABLE EMAIL_PURGE_LIST(EMAIL_ID  VARCHAR2(255))');

--SELECT * FROM EMAIL_PURGE_LIST

-- Note : need to load the data into EMAIL_PURGE_LIST using sql loader
-- Note 01/17/2005: NO need to load the data into EMAIL_PURGE_LIST using sql loader

execute_sql('DELETE FROM EMAIL_LIST x where upper(x.enty_email_id) in ( select upper(s.email_id)  from EMAIL_PURGE_LIST s)');


COMMIT;

-- remove "www" prefix


UPDATE  EMAIL_LIST x
   SET  x.enty_email_id = replace(x.enty_email_id,'www.','')
  where enty_email_id like 'www.%';


UPDATE  EMAIL_LIST x
   SET  x.enty_email_id = replace(x.enty_email_id,'www','')
  where enty_email_id like 'www%';


COMMIT;

end;

END mail_data_extract;

Using dynamic SQL, roles are disabled. Do you have the approp. privileges granted directly to you?

Alex

My role priviledges are connect and resource.

My system priviledges are create any table, create any trigger, create any view, create database link, create session, create synonym, are unlimited tablespace.

Check the SQL you are passing. You code seems OK to me.

Thanks Alex, for your help.  What a small oversight that I missed.  I commented out part of the code to debug what was going wrong with my code.  When I posted the question, I forgot to delete the beginning comment symbol.  Gladys =)