help import file
adry, November 17, 2004 - 5:45 am UTC
helo,
my procedure gives the following error to me
why?
CREATE OR REPLACE PROCEDURE inserimento AS
V_TIPO_RECORD CHAR(1);
V_TIPO_ELEMENTO CHAR(1);
V_POLO_CREDITO CHAR(1);
V_PROG_RIMB CHAR(19);
V_COD_ESITO CHAR(6);
V_DESC_ESITO CHAR(30);
V_DATA_ESITO CHAR(8);
V_COD_ALL CHAR(35);
V_CODE_CT CHAR(4);
V_COGNOME CHAR(30);
V_INDIRIZZO CHAR(30);
V_CITTA CHAR(21);
V_PROVINCIA CHAR(2);
V_CAP CHAR(5);
V_IBAN CHAR(4);
V_CIN CHAR(1);
V_ABI CHAR(5);
V_CAB CHAR(5);
V_NUM_CC CHAR(12);
V_IMPT_RIMB_A CHAR(13);
V_IMPT_RIMB_B CHAR(2);
V_PROG_RIMB_B CHAR(8);
V_PART_IVA CHAR(16);
v_file UTL_FILE.FILE_TYPE;
v_text VARCHAR2(1000);
v_fld CHAR(5);
v_sql VARCHAR2(1000);
d_directory1 varchar2(80):= 'c:/';
BEGIN
v_file := UTL_FILE.FOPEN(d_directory1,'xx.txt','r',2000);
--v_ename := '';
--V_TIPO_RECORD :=1;
v_sql := 'INSERT INTO appo_cb VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23 )';
UTL_FILE.FCLOSE(v_file);
COMMIT;
/*LOOP
UTL_FILE.GET_LINE(v_file, v_text);
IF V_PROG_RIMB <> 0 THEN
UTL_FILE.FCLOSE(v_file);
COMMIT;
EXIT;
*/
/*END IF;
v_fld := SUBSTR(v_text,1,5);
IF v_fld = 'ename' AND NOT v_ename = '' THEN
IF v_commi = NULL THEN
v_commi := 0;
END IF; */
/* EXECUTE IMMEDIATE v_sql USING V_TIPO_RECORD,
V_TIPO_ELEMENTO,
V_POLO_CREDITO,
V_PROG_RIMB,
V_COD_ESITO ,
V_DESC_ESITO ,
V_DATA_ESITO ,
V_COD_ALL ,
V_CODE_CT ,
V_COGNOME,
V_INDIRIZZO ,
V_CITTA ,
V_PROVINCIA ,
V_CAP ,
V_IBAN ,
V_CIN ,
V_ABI ,
V_CAB ,
V_NUM_CC ,
V_IMPT_RIMB_A ,
V_IMPT_RIMB_B ,
V_PROG_RIMB_B ,
V_PART_IVA
;
*/
-- variabile := '';
/* END IF;
END LOOP;*/
/*CASE v_fld
WHEN 'empno' THEN v_empno := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'ename' THEN v_ename := SUBSTR(v_text, 8);
WHEN 'jobid' THEN v_jobid := SUBSTR(v_text, 8);
WHEN 'manag' THEN v_manag := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'hired' THEN v_hired := TO_DATE(SUBSTR(v_text, 8));
WHEN 'salar' THEN v_salar := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'commi' THEN v_commi := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'depno' THEN v_depno := TO_NUMBER(SUBSTR(v_text, 8)); */
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No_data_found');
UTL_FILE.FCLOSE(v_file);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(v_file);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(v_file);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(v_file);
when utl_file.invalid_mode then
raise_application_error(-20002,
'INVALID_MODE: The open_mode parameter in FOPEN was
invalid.');
when utl_file.invalid_filehandle then
raise_application_error(-20002,
'INVALID_FILEHANDLE: The file handle was invalid.');
when utl_file.invalid_operation then
raise_application_error(-20003,
'INVALID_OPERATION: The file could not be opened or
operated on as requested.');
when utl_file.internal_error then
raise_application_error(-20006,
'INTERNAL_ERROR: An unspecified error in PL/SQL.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('No file');
UTL_FILE.FCLOSE(v_file);
END;
/