Tuesday, August 10, 2010

UPLOAD A File Into Temp table In APEX








DECLARE
CRLF1 VARCHAR2(10) := utl_raw.cast_to_raw(CHR(13),CHR(10)); -- Carriage Return+Line Feed
CRLF VARCHAR2(10) := utl_raw.cast_to_raw(CHR(10)); -- Line Feed
u_id NUMBER;
v_cnt NUMBER;
v_tbl_nm VARCHAR2(30);
l_cr INTEGER;
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_vcbuffer VARCHAR(4000);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
v_sql VARCHAR2(3000);
v_maxcols NUMBER;
v_delim NUMBER := 9;--Tab
v_batch_nm VARCHAR2(10) := TO_CHAR (SYSDATE,'MMDDHHMISS');
v_message varchar2(32557):= null;
V_MESSAGE_DATA VARCHAR2(32555) := NULL;


BEGIN
:P50_ERROR := null;
--Get Blob from holing table and put in varables
SELECT ID, blob_content
INTO u_id, l_blob
FROM APEX_APPLICATION_FILES
WHERE name= TRIM (:P35_UPLOAD);


--Remove blob from holding table
DELETE FROM APEX_APPLICATION_FILES
WHERE ID=u_id;


COMMIT;


--Find the end of the Linedata
l_cr := DBMS_LOB.INSTR(l_blob,CRLF);
--convert the Blob to a Varchar2
l_vcbuffer := utl_raw.cast_to_varchar2(DBMS_LOB.substr(l_blob, l_cr, 1));


--Makes the columes
select length(l_vcbuffer) - length(REPLACE(l_vcbuffer,CHR(v_delim))) as "count"
INTO v_maxcols
from dual;




--add one more column to the selection
v_maxcols := v_maxcols+1;


-- Open the operating system (OS) text destination file on the server.--Creates the file
l_file := UTL_FILE.fopen('ESCRMGR',v_batch_nm||'.csv','w', 32767);
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos && l_blob_len LOOP--for each line
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;


-- Close the file.
UTL_FILE.fclose(l_file);


-- drop table if it exists already
v_tbl_nm := 'BU_TT_'
v_batch_nm;


SELECT count(*)
INTO v_cnt
FROM all_tables
WHERE table_name= v_tbl_nm AND owner = 'ESCRMGR';


iF v_cnt > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE ESCRMGR.'
v_tbl_nm;
END IF;
--
SELECT count(*)
INTO v_cnt
FROM bu_imports
WHERE table_name=v_tbl_nm;


--Log the table being removed
IF v_cnt > 0 THEN
EXECUTE IMMEDIATE 'UPDATE bu_imports SET PROCESSED_DTM=SYSDATE,NOTES=''UPLOAD OVERWROTE TABLE NAME''
WHERE TABLE_NAME='''
v_tbl_nm
'''';
END IF;




-- create table
v_sql := 'CREATE TABLE '
v_tbl_nm
' (';


--Create the Sql that creates a temp table of the columns
FOR i IN 1..v_maxcols
LOOP
v_sql := v_sql
'COL'
i
' VARCHAR2(100 BYTE),';
END LOOP;


v_sql := RTRIM (v_sql,',')
')
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY ESCRMGR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
NOLOGFILE
SKIP 1
FIELDS TERMINATED BY x'''
to_hex(v_delim)
'''
MISSING FIELD VALUES ARE NULL
)
LOCATION (ESCRMGR:'''
v_batch_nm
'.csv'')
)';


EXECUTE IMMEDIATE v_sql;
--


--Display the import Seq to the users
SELECT BU_IMPORTS_SEQ.nextval
INTO :P35_IMPORT_ID
FROM dual;


--Log what was imported along with the table name
EXECUTE IMMEDIATE 'INSERT into bu_imports (import_id, import_name, added_sid,
max_columns, max_field_length, table_name,
process_id)
VALUES ('
TO_NUMBER (:P35_IMPORT_ID)
',
'''
v_batch_nm
''',
'''
:APP_USER
''',
'
v_maxcols
',
'
30
',
'''
v_tbl_nm
''',
'
TO_NUMBER (:P35_PROCESS)
')';


COMMIT;


if :P35_PROCESS = '3' then
v_sql := null;
v_sql := 'merge into LU_BRANCHES lb using (select * from '
v_tbl_nm
' where upper(col3) != ''CLOSED'') tt on (lb.branch_nbr = to_number(tt.col1))
when matched then
update set lb.branch_name = tt.col2,
lb.status = tt.col3,
lb.region =tt.col4,
lb.underwriting =tt.col5,
lb.processing =tt.col6,
lb.closing = tt.col7,
lb.MODIFIED_DTM =sysdate
WHEN NOT MATCHED
then
insert (lb.branch_nbr,lb.branch_name,lb.status, lb.region,lb.underwriting,lb.processing, lb.closing,lb.MODIFIED_DTM )
values (tt.col1,tt.col2,tt.col3,tt.col4,tt.col5,tt.col6,tt.col7,sysdate )';
dbms_output.put_line(v_sql);
:P35_x := v_sql;
execute immediate v_sql;
commit;


update LU_BRANCHES set status = 'Closed' ,modified_dtm = sysdate where trunc(nvl(modified_dtm, '01-JAN-0001')) != trunc(sysdate);
end if;
EXECUTE IMMEDIATE 'DROP TABLE '
v_tbl_nm ;


UTL_FILE.fremove ('ESCRMGR', v_batch_nm||'.csv');


commit;


EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;

1 comment: