Import from Excel to Database

DECLARE

application client_ole2.obj_type;

workbooks client_ole2.obj_type;

workbook client_ole2.obj_type;

worksheets client_ole2.obj_type;

worksheet client_ole2.obj_type;

args client_ole2.list_type;

cell client_ole2.obj_type;

v_range client_ole2.obj_type;

num NUMBER;

col_num NUMBER := 64;

mess VARCHAR2(30000);

v_temp VARCHAR2(4000);

var_initiate VARCHAR2(50) := :file_name;

c1 VARCHAR2(1000);

v_cnt NUMBER(10);

j NUMBER;

BEGIN

SELECT COUNT(*)

INTO v_cnt

FROM user_tab_columns

WHERE table_name = :bl1.table_name;

IF :t_columns IS NOT NULL THEN

IF v_cnt <> :t_columns THEN

alert_msg(

'Columns in the Excel Spreadsheet and Table Do not Match',

'S'

);

END IF;

ELSE

alert_msg('No Columns Should not be Null', 'S');

END IF;

IF :table_name IS NULL THEN

alert_msg('Table Name Should not be Null', 'S');

END IF;

IF :file_name IS NULL THEN

alert_msg('File Name Should not be Null', 'S');

END IF;

IF :head_not = 'Y' THEN

num := 1;

END IF;

IF :head_not = 'N' THEN

num := 2;

END IF;

IF (client_ole2.issupported) THEN

application := client_ole2.create_obj('Excel.Application');

client_ole2.set_property(application, 'Visible', 'False');

workbooks := client_ole2.get_obj_property(application, 'Workbooks');

args := client_ole2.create_arglist;

client_ole2.add_arg(args, var_initiate);

workbook := client_ole2.invoke_obj(workbooks, 'Open', args);

client_ole2.destroy_arglist(args);

worksheets := client_ole2.get_obj_property(workbook, 'Worksheets');

args := client_ole2.create_arglist;

client_ole2.add_arg(args, 1);

worksheet := client_ole2.get_obj_property(worksheets, 'Item', args);

client_ole2.destroy_arglist(args);

LOOP

FOR i IN 1 .. :t_columns

LOOP

j := i;

args := client_ole2.create_arglist;

client_ole2.add_arg(args, CHR(col_num + i) || TO_CHAR(

num

));

v_range := client_ole2.get_obj_property(

worksheet,

'Range',

args

);

client_ole2.destroy_arglist(args);

mess := client_ole2.get_char_property(v_range, 'text');

c1 := c1 || ''',''' || mess;

EXIT WHEN mess IS NULL

AND i = 1;

END LOOP;

c1 := LTRIM(c1, '''');

c1 := LTRIM(c1, ',');

v_temp := 'INSERT INTO ' || :table_name || ' VALUES ( ' || c1 || ''' )';

FORMS_DDL(v_temp);

-- MESSAGE(V_TEMP);MESSAGE('');

c1 := NULL;

EXIT WHEN mess IS NULL

AND j = 1;

num := num + 1;

END LOOP;

proc_commit;

alert_msg('Successfully extracted from the excel file', 'N');

client_ole2.RELEASE_OBJ(v_range);

client_ole2.RELEASE_OBJ(worksheet);

client_ole2.RELEASE_OBJ(workbook);

client_ole2.RELEASE_OBJ(workbooks);

--client_ole2.release_obj(application);

client_ole2.invoke(application, 'Quit');

/*

args := CLIENT_OLE2.CREATE_ARGLIST;

CLIENT_OLE2.ADD_ARG(args, 0);

workbook:=CLIENT_OLE2.INVOKE_OBJ(workbook,'Close',args);

CLIENT_OLE2.DESTROY_ARGLIST(args);

CLIENT_OLE2.INVOKE(application,'Quit');

*/

ELSE

alert_msg('OLE2 NOT SUPPORTED', 'S');

END IF;

/*

EXCEPTION

WHEN CLIENT_OLE2.OLE_ERROR THEN

num:=CLIENT_OLE2.last_exception;

message('OLE Error #: ' ||num);

message(' ');

WHEN OTHERS THEN

MESSAGE('Exception');

MESSAGE(error_text);

MESSAGE(dbms_error_text);

MESSAGE(' ');*/

END;