Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » could not import data from 10g forms (10g forms on server 2003)
could not import data from 10g forms [message #419883] Tue, 25 August 2009 23:21
jai_g
Messages: 23
Registered: July 2009
Location: India
Junior Member
I could not import data into table after obtaining the file from client using webutil.pll.

The code is under.

PROCEDURE INSERT_DATA_ACCTS IS
BEGIN
declare
p_username varchar2(100) := get_application_property(username);
p_password varchar2(100) := get_application_property(password);
p_connect varchar2(100);
p_userid varchar2(300) := null;
p_text varchar2(500) := null;
N_FILE CLIENT_text_io.file_type;
v_entity_dtl varchar2(100);
v_entity_dtl_file varchar2(100);
v_file varchar2(150);
batch_file varchar2(150);
b_file CLIENT_text_io.file_type;
T_file CLIENT_text_io.file_type;
v_table_name varchar2(40):= 'MDT_DISSRCP_IMP_'||:HDR1.DEPOT;
v_columns varchar2(1000) := 'FILENAME';
v_block varchar2(512);
O_TABLE_NAME VARCHAR2(40) := 'MDT_DISSRCP_'||:HDR1.DEPOT;
O_FILE CLIENT_text_io.file_type;
sritc_ins varchar2(150);
sritc varchar2(150);
UNIT VARCHAR2(50);

begin
CLIENT_tool_env.getvar('service_name' ,p_connect);

-- GET THE PATH
v_entity_dtl := :FILENAME;
--v_entity_dtl_file := :FILENAME;
--v_file := v_entity_dtl||v_entity_dtl_file;
V_FILE := V_ENTITY_DTL;
--MESSAGE := 'Please Wait...';
--MESSAGE('PLEASE WAIT ...');

--:GLOBAL.g_bad := v_entity_dtl||v_entity_dtl_file||'.bad';
--:GLOBAL.g_log := v_entity_dtl||v_entity_dtl_file||'.log';

:GLOBAL.g_bad := v_entity_dtl||'.bad';
:GLOBAL.g_log := v_entity_dtl||'.log';

if :FILENAME is null then
--alert_msg('Invalid data file...','I',true );
--MESSAGE('Invalid data file...','I',true);
MESSAGE('INVALID DATA FILE');
MESSAGE('INVALID DATA FILE');
end if;

n_file := CLIENT_TEXT_IO.FOPEN(:FILENAME,'R');
if not CLIENT_text_io.is_open( n_file ) then
--alert_msg('Source file not found...!!','I',true);
MESSAGE('SOURCE FILE NOT FOUND');
MESSAGE('SOURCE FILE NOT FOUND');
else
CLIENT_text_io.fclose( n_file );
end if;

IF :GLOBAL.USER_GRP_CODE = '0100' THEN
UNIT := 'PER';
ELSIF :GLOBAL.USER_GRP_CODE = '0200' THEN
UNIT := 'GOC';
ELSIF :GLOBAL.USER_GRP_CODE = '0300' THEN
UNIT := 'PTJ';
ELSIF :GLOBAL.USER_GRP_CODE = '1000' THEN
UNIT := 'SR';
ELSE NULL;
END IF;

-- CREATE A CONTROL FILE
-------------n_file := TEXT_IO.FOPEN( v_file||'.ctl','W');
N_FILE := CLIENT_TEXT_IO.FOPEN ( 'SRITC'||UNIT||'.ctl','W');

-- INSERT THE TEXT ENTRY TO THE CONTROL FILE
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'LOAD DATA' );
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'INFILE '||''''||:FILENAME||'''');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,' INTO TABLE '||'MDT_DISSRCP_IMP_'||UNIT);
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'(VCH_CODE POSITION (01:02) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'ADJ_IND POSITION (03) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'BNDL_NO POSITION (04:06) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'DEPOT_CODE POSITION (07:08) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'WARD_CODE POSITION (09:10) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'VCH_ISS_RCP_NO POSITION (11:14) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'VCH_DATE POSITION (15:20) char,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'PL_NO POSITION (21:28) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'PL_CAT POSITION (29:30) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'UOM_CCODE POSITION (31:32) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'QTY_ISSUED POSITION (33:41) char,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'SIGN POSITION (42) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'VALUE POSITION (43:51) char,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'ALLOC_CODE POSITION (52:59) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'BLANK POSITION (60) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'CONSG_CODE POSITION (61:65) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'PO_NO POSITION (66:81) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'RR_NO POSITION (82:87) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'ADV_PAY POSITION (88) CHAR,');
CLIENT_TEXT_IO.PUT_LINE( N_FILE ,'VCH_REQ_NO POSITION (89:97) CHAR)');

DECLARE
V_EXISTS NUMBER;
BEGIN
select count(*) into v_exists from dba_tables where table_name = 'MDT_DISSRCP_IMP_'||UNIT;
if v_exists = 1 THEN
--insert into
NULL;
else
MESSAGE('TABLE DOES NOT EXIST! CONTACT EDPM!');
MESSAGE('TABLE DOES NOT EXIST! CONTACT EDPM!');
--create table
--insert into
end if;
END;

if CLIENT_text_io.is_open( n_file ) then
CLIENT_TEXT_IO.FCLOSE(N_FILE);
end if;

-- USERID PARAMETER OF THE SQLLDR
p_userid := 'DEPOT'|| '/' || 'DEPOT' || '@' || 'SRITCMM';

-- THE EXACT SQLLDR COMMAND INCLUDING THE PARAMETER
------------------p_text := 'sqlldr userid=' || p_userid ||' control='||v_file||'.ctl '||'log='||v_file||'.log';
p_text := 'sqlldr userid=' || p_userid ||' control='||'SRITC'||UNIT||'.ctl '||'log='||'SRITC'||UNIT||'.log';

-- create a batch file here before a call by the host
-----------b_file := TEXT_IO.FOPEN( v_file||'.bat','W');
b_file := CLIENT_TEXT_IO.FOPEN( 'SRITC'||UNIT||'.bat','W');

-- INSERT THE TEXT COMMAND TO THE BATCH FILE
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo off' );
CLIENT_TEXT_IO.PUT_LINE( b_file ,p_text );
CLIENT_TEXT_IO.PUT_LINE( b_file ,'' );
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo ...............................................');
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo * *');
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo --- C L O S E T H I S W I N D O W N O W ---');
if CLIENT_text_io.is_open( b_file ) then
CLIENT_TEXT_IO.FCLOSE(b_file);
end if;
Set_application_property(Cursor_style,'BUSY');
-------------batch_file := v_file||'.bat';
batch_file := 'SRITC'||UNIT||'.bat';

-- EXECUTE THE BATCH FILE
CLIENT_host( batch_file,NO_SCREEN );

-- DELETE THE BATCH FILE
---host( 'Del '||v_file||'.bat',NO_SCREEN );
---HOST ( 'DEL '||:FILENAME||'.bat',NO_SCREEN);
-----------------HOST ( 'DEL '||'SRITC'||'.bat',NO_SCREEN);
--host( 'Del '||:FILENAME||'.CTL',NO_SCREEN );
-----------------host( 'Del '||'SRITC'||'.ctl',NO_SCREEN );

if not form_success or form_fatal or form_failure then
Set_application_property(Cursor_style,'DEFAULT');
--alert_msg('Errors occured during the process, pls. check all the recources..','I',true);
MESSAGE('ERRORS OCCURED DURING PROCESS. PLEASE CHECK ALL THE RESOURECES!');
MESSAGE('ERRORS OCCURED DURING PROCESS. PLEASE CHECK ALL THE RESOURECES!');
end if;

-----------------O_file := TEXT_IO.FOPEN( v_file||'.sql','W');
O_file := CLIENT_TEXT_IO.FOPEN( 'sritc_ins'||UNIT||'.sql','W');

commit;

FORMS_DDL( 'DELETE FROM MDT_DISSRCP_IMP_'||UNIT||' ' ||
'WHERE DEPOT_CODE IS NULL AND PL_NO IS NULL AND QTY_ISSUED IS NULL');
COMMIT;

CLIENT_TEXT_IO.PUT_LINE (o_file,'insert into'||' '||' mdt_dissrcp_'||UNIT||' '||
' (vch_code, adj_ind, BNDL_no, DEPOT_code,ward_code, vch_iss_rcp_no,
vch_date, PL_No, pl_CAT, uom_ccode, QTY_issued, SIGN, VALue, ALLOC_code,
BLaNK, CONSG_code, PO_NO, RR_NO, ADV_PAY, vch_REQ_NO)
(select
VCH_CODE, ADJ_IND, bndl_NO, depot_CODE, ward_CODE, VCH_ISS_RCP_NO, to_date(VCH_DATE,''ddmmyy'') VCH_DATE,
PL_NO, PL_CAT, UOM_CCODE,to_number(QTY_ISSUED/1000) QTY_ISSUED, sign,
to_number(valUE/100) valUE, alloc_CODE,
blAnk, consg_CODE, po_no, rr_no, adv_pay, VCH_req_no From mdt_dissrcp_imp_'||UNIT||');');
CLIENT_TEXT_IO.PUT_LINE (o_file,'commit;');
CLIENT_TEXT_IO.PUT_LINE (o_file,'truncate table mdt_dissrcp_imp_'||UNIT||';');
CLIENT_TEXT_IO.PUT_LINE (o_file,'commit;');
CLIENT_TEXT_IO.PUT_LINE (o_file,'exit');
if CLIENT_text_io.is_open( o_file ) then
CLIENT_TEXT_IO.FCLOSE(o_FILE);
end if;
-----------------------------------------------------------------
p_userid := 'DEPOT'|| '/' || 'DEPOT' || '@' || 'SRITCMM';

-- THE EXACT SQLLDR COMMAND INCLUDING THE PARAMETER

p_text := 'sqlplusw'||' '|| p_userid ||' '||'@'||'sritc_ins'||UNIT||'.sql';


-- create a batch file here before a call by the host
-----------------b_file := TEXT_IO.FOPEN( v_file||'.bat','W');
b_file := CLIENT_TEXT_IO.FOPEN( 'sritc'||UNIT||'.bat','W');

-- INSERT THE TEXT COMMAND TO THE BATCH FILE
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo off' );
CLIENT_TEXT_IO.PUT_LINE( b_file ,p_text );
CLIENT_TEXT_IO.PUT_LINE( b_file ,'' );
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo ...............................................');
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo * *');
CLIENT_TEXT_IO.PUT_LINE( b_file ,'echo --- C L O S E T H I S W I N D O W N O W ---');

if CLIENT_text_io.is_open( b_file ) then
CLIENT_TEXT_IO.FCLOSE(b_file);
end if;
Set_application_property(Cursor_style,'BUSY');
-------------batch_file := v_file||'.bat';
batch_file := 'SRITC'||UNIT||'.bat';

-- EXECUTE THE BATCH FILE
CLIENT_host( batch_file,NO_SCREEN );

COMMIT;

v_block := 'MDT_DISSRCP_'||UNIT;
set_block_property('mdt_dissrcp_temp',query_data_source_name,v_block);
go_block('mdt_dissrcp_temp');
execute_query;

---host( 'Del '||'sritc'||UNIT||'.CTL',NO_SCREEN );
CLIENT_host( 'CMD /C Del '||'sritc'||UNIT||'.CTL',NO_SCREEN );
CLIENT_host( 'CMD /C Del '||'sritc'||UNIT||'.bat',NO_SCREEN );
CLIENT_host( 'CMD /C Del '||'sritc_ins'||unit||'.SQL',NO_SCREEN );

Set_application_property(Cursor_style,'DEFAULT');
END;

GO_BLOCK('MDT_DISSRCP_TEMP');
GO_ITEM('MDT_DISSRCP_TEMP.DEPOT_CODE');

SET_ITEM_PROPERTY('TOOLBAR.VIEW',ENABLED,PROPERTY_ON);
SET_ITEM_PROPERTY('TOOLBAR.VIEW',LABEL,'UPLOAD');

END;
Previous Topic: ORACLE LICENSING
Next Topic: Launch a Workbook in Viewer without signing in again
Goto Forum:
  


Current Time: Mon Mar 18 21:46:48 CDT 2024