Run SQL Loader from a stored procedure

[smsinger3]

Hello. I just posted a message asking if it is possible to run a .bat file from a stored procedure. I don't think I was specific enough... What I really want to load a text file into a table. I have a SQL Loader .bat file that does this already, but I would like to run it in a stored procedure. After I thought about it, I realized that I don't need to actually run the .bat file, but instead just run the SQL Loader. Make sense?

I placed this article as a separate thread because I would like to know the answer to both questions. They both will come in handy!

Thanks,

SteveS

 

[sem]

Why do you need to call it from database? You may create some simple library (dll or so) calling loader and call it as EXTERNAL PROCEDURE. But I suppose that calling it externally is more efficient(and flexible!) way. Regards, Dima

 

[smsinger3]

I just thought it was a littler "cleaner" to call it from a stored procedure because I have sql that I need to run before and after it.

 

[sem]

You may call them from some shell script as well. In any case you can not obtain TRANSACTIONAL processing unless you implement your own loading procedure (without sql*loader). Regards, Dima

 

[phisq]

hi, hth...

FUNCTION UPLOAD_FILE (p_dir in varchar2, p_file in varchar2, p_maxpos number) RETURN NUMBER IS
/*
drop table XX_FILE;

create sequence XX_FILE_S

create table XX_FILE (batch_id number, line_num number,
file_name varchar2 (240), dir_path varchar2 (240),
creation_date date, created_by number,
col1 varchar2
(240), col2 varchar2 (240), col3 varchar2 (240), col4
varchar2 (240), col5 varchar2 (240), col6 varchar2 (240),
col7 varchar2 (240), col8 varchar2 (240), col9 varchar2
(240), col10 varchar2 (240),
col11 varchar2
(240), col12 varchar2 (240), col13 varchar2 (240), col14
varchar2 (240), col15 varchar2 (240), col16 varchar2 (240),
col17 varchar2 (240), col18 varchar2 (240), col19 varchar2
(240), col20 varchar2 (240),
col21 varchar2
(240), col22 varchar2 (240), col23 varchar2 (240), col24
varchar2 (240), col25 varchar2 (240), col26 varchar2 (240),
col27 varchar2 (240), col28 varchar2 (240), col29 varchar2
(240), col30 varchar2 (240),
col31 varchar2
(240), col32 varchar2 (240), col33 varchar2 (240), col34
varchar2 (240), col35 varchar2 (240), col36 varchar2 (240),
col37 varchar2 (240), col38 varchar2 (240), col39 varchar2
(240), col40 varchar2 (240),
col41 varchar2
(240), col42 varchar2 (240), col43 varchar2 (240), col44
varchar2 (240), col45 varchar2 (240), col46 varchar2 (240),
col47 varchar2 (240), col48 varchar2 (240), col49 varchar2
(240), col50 varchar2 (240)
);

drop index XX_FILE_U1

CREATE UNIQUE INDEX APPS.XX_FILE_U1
ON APPS.XX_FILE(BATCH_ID, LINE_NUM) PCTFREE 10 INITRANS
2 MAXTRANS 255
STORAGE(
INITIAL 256 K
NEXT 256 K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 100
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING TABLESPACE DATA
*/

type table_col is table of varchar2(240) index by
binary_integer;
v_col table_col;

infile UTL_FILE.FILE_TYPE;
fstr varchar2(4000);
v_BId number;
v_LnNum integer;
v_maxpos integer := 50;


BEGIN
log ('***** START UPLOAD_FILE *****', g_tog);
select XX_FILE_S.nextval
into v_BId
from dual;

log('BId: '||v_BId);
UTL_FILE.FCLOSE(infile);
log('FCLOSE', g_tog);
infile := UTL_FILE.FOPEN(p_dir, p_file, 'r');
v_LnNum := 0;
--log('FileOPen', g_tog);
loop begin

UTL_FILE.GET_LINE(infile, fstr);
--log(fstr, g_tog);
v_LnNum := v_LnNum + 1;

for i in 1..v_maxpos loop

v_col(i) := GET_COL (fstr, i);
--log(v_col(i));

end loop;

INSERT INTO XX_FILE (BATCH_ID, LINE_NUM,
FILE_NAME, DIR_PATH, CREATION_DATE, CREATED_BY,
COL1, COL2, COL3, COL4,
COL5, COL6, COL7, COL8, COL9,
COL10, COL11, COL12,
COL13, COL14, COL15,
COL16, COL17, COL18, COL19,
COL20, COL21, COL22, COL23,
COL24, COL25, COL26, COL27,
COL28, COL29, COL30, COL31,
COL32, COL33, COL34,
COL35, COL36, COL37, COL38,
COL39, COL40, COL41, COL42,
COL43, COL44, COL45,
COL46, COL47, COL48, COL49,
COL50)
VALUES (v_BId,
v_LnNum, p_file, p_dir,
sysdate, fnd_global.user_id, v_col(1), v_col
(2), v_col(3), v_col(4), v_col(5), v_col(6), v_col(7), v_col
(8), v_col(9), v_col(10), v_col(11), v_col(12), v_col
(13), v_col(14), v_col(15), v_col(16), v_col(17), v_col
(18), v_col(19), v_col(20), v_col(21), v_col
(22), v_col(23), v_col(24), v_col(25), v_col(26), v_col
(27), v_col(28), v_col(29), v_col(30), v_col
(31), v_col(32), v_col(33), v_col(34), v_col(35), v_col
(36), v_col(37), v_col(38), v_col(39), v_col(40),
v_col(41), v_col(42), v_col(43), v_col(44), v_col(45),
v_col(46), v_col(47), v_col(48), v_col(49), v_col(50));

EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
WHEN OTHERS THEN
begin
log(SQLERRM, g_tog);
v_BId := 0;
end;

end; end loop;

UTL_FILE.FCLOSE(infile);

log ('***** END UPLOAD_FILE *****', g_tog);
return (v_BId);

END UPLOAD_FILE;

 

[sem]

Using UTL_FILE is a reasonable approach when loading sufficiently small data (some ten thousand records or less) from a file with simple structure. Pl/sql is not a good language for writing parsers. Of course, you may use pl/sql wrappers on java procedures (trippling context switching ), but using loader in most cases is MUCH MORE EFFICIENT. This also provides better APPLICATION PARTITIONING and thus SCALING, as you may place your loading procedure out of database server (or you may don't do it). Regards, Dima