/* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This small package was used to export names of people that were to be placed on a 'Do Not Call' List.
When a person requests that they be taken from a marketing list, their name is flagged in the
telemarketing(campaign)table. The resulting names are then exported by this Package to a comma delimited
file where the names can be inputted into another database holding the Do Not Call List. This
is a common situation because many of the automatic dialing software use a common database such as SQL Server.
However access to the database is restricted, therefore most of the records are held in a different
database where they can be easily updated and queried without affecting the dialer. The main tables are fl.lead
which holds all the records and fl.remove which keeps a record of all records placed on the 'Do Not Call' List.
The CREATE_LIST procedure gets the name of all the campaigns (telemarketing lists) in the main
table. The names are then passed one at a time to the PRINT_TO_FILE procedure which creates a file for
each campaign and fills the file with the required records. The init.ora parameter _UTL_FILE
must be set to the folder where the procedure will write or the procedure will fail.
Created by Chris Kirlew - 02/2004
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
CREATE OR REPLACE PACKAGE DNC_EXPORT
AS
Procedure CREATE_LIST(table_name in varchar2 := NULL);
Procedure PRINT_TO_FILE(campaign in varchar2,new_table in varchar2);
END DNC_EXPORT;
/
CREATE OR REPLACE PACKAGE BODY DNC_EXPORT
AS
-- Create a new table to record records removed, insert records from external table, query table
-- for campaign name. Pass campaign name to the PRINT_TO_FILE procedure
Procedure CREATE_LIST(table_name in varchar2 := NULL)
AS
campaignName varchar2(32);
new_table varchar2(32);
default_table varchar2(32);
sqlstr varchar2(1000);
p_query varchar2(1000);
l_status integer;
l_cnt integer default 0;
columnValue varchar2(32);
l_theCursor integer default dbms_sql.open_cursor;
BEGIN
-- Create table to hold records to be removed
select to_char(sysdate,'"ckirlew.remove_"YYMMDD') into default_table from dual;
if table_name is NULL then new_table := default_table;
else new_table := table_name;
end if;
execute immediate 'create table '|| new_table ||' tablespace users as select * from fl.removes_ext';
-- Get the Campaign names and pass each name to the PRINT_TO_FILE procedure
p_query := 'select distinct( case when substr( campaign, 1, 1 ) = ''W'' then ''WELLSCARGO''
else campaign end) as campaign from fl.lead
where lead_id in ( select lead_id from ' ||new_table||' ) and
lead_id not in ( select lead_id from fl.remove )
order by 1';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.define_column( l_theCursor, 1, columnValue, 4000 );
l_status := dbms_sql.execute(l_theCursor);
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
dbms_sql.column_value( l_theCursor, 1, columnValue );
l_cnt := l_cnt + 1;
PRINT_TO_FILE(columnValue, new_table);
end loop;
dbms_sql.close_cursor(l_theCursor);
END CREATE_LIST;
-- Export the records to a comma delimited file.
Procedure PRINT_TO_FILE(campaign in varchar2,new_table in varchar2)
AS
l_campaign varchar2(30);
p_dir varchar2(30) := 'CELLIT';
p_filename varchar2(30) default null;
p_separator varchar2(1) default ',';
l_separator varchar2(10) default '';
l_colCnt number default 0;
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_status integer;
l_columnValue varchar2(4000);
l_cnt number default 0;
l_date varchar2(4) default null;
p_query varchar2(4000);
BEGIN
l_campaign := campaign;
p_filename := ''||campaign||'_'||to_char(sysdate,'mmddyy')||'.csv';
l_output := utl_file.fopen( p_dir, p_filename, 'w',32000 );
IF campaign = 'WELLSCARGO'
THEN
p_query :='select case when substr( campaign, 1, 1 ) = ''W'' then ''WELLSCARGO''
else campaign end as campaign,last_name as lname,first_name as fname,
substr( home_phone, 1, 3 ) as areac,
substr( home_phone, 4, 3 ) as prefix,
substr( home_phone, 7, 4 ) as telroot,
address, city, state, zip
from fl.lead
where substr( campaign, 1, 1 ) = ''W''
and lead_id in ( select lead_id from ' ||new_table||' ) and
lead_id not in ( select lead_id from fl.remove )
order by 1';
ELSE
p_query :='select case when substr( campaign, 1, 1 ) = ''W'' then ''WELLSCARGO''
else campaign end as campaign,last_name as lname,first_name as fname,
substr( home_phone, 1, 3 ) as areac,
substr( home_phone, 4, 3 ) as prefix,
substr( home_phone, 7, 4 ) as telroot,
address, city, state, zip
from fl.lead
where campaign ='''||campaign||'''
and lead_id in ( select lead_id from ' ||new_table||' ) and
lead_id not in ( select lead_id from fl.remove )
order by 1';
END IF;
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 4000 );
l_status := dbms_sql.execute(l_theCursor);
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put(l_output, l_separator || l_columnValue);
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output);
END PRINT_TO_FILE;
END DNC_EXPORT;
/
|