[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

package to execute sql statements

Hi,

I've created a package from a script a user made to execute sql statements.  I ran the scripts in my schema so that I have enough priviledges.  When I compile the package spec all is good.  When I compile the body I get a Hint comment stating value assigned to 't_execution_status' never used in 'EXECUTE_SQL' and 'DELETE_TABLE'.  Then I execute the package at the sql prompt with exec mail_data_extract.populate_mail_data.  I get the following errors:

ORA-00904: invalid column name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 824
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "GSAN.MAIL_DATA_EXTRACT", line 13
ORA-06512: at "GSAN.MAIL_DATA_EXTRACT", line 63
ORA-06512: at line 1

With the first error there shouldn't be an invalid column name because I ran the script the user made at the sql command prompt and I didn't get that error.

What am I doing wrong.  Please help.

Regards,
Gladys
0
Gladys522
Asked:
Gladys522
  • 3
  • 2
1 Solution
 
Gladys522Author Commented:
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;
0
 
alexnuijtenCommented:
Using dynamic SQL, roles are disabled. Do you have the approp. privileges granted directly to you?

Alex
0
 
Gladys522Author Commented:
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.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
sujit_kumarCommented:
Check the SQL you are passing. You code seems OK to me.
0
 
alexnuijtenCommented:
I noticed you're using double-quotes in the populate_mail_data procedure, there should be two single quotes.

Is this the whole code? Most of it is commented out, but the end */ is missing...

Alex
0
 
Gladys522Author Commented:
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 =)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now