I have a procedure that receives the contents of a file in a single variable and I divide it into lines and divide the fields of each line delimited by commas, all that process I do it correctly but now I must generate a new file with some specific fields of the divided ones I have previously seen that all write files using utl_file write but I want to insert it in a table instead of an FTP server. Is it possible to do this? If yes, which is the best way to do it?
CREATE OR replace PROCEDURE process_file (filecontent CLOB)
AS
contador NUMBER := 0;
BEGIN
FOR i IN (SELECT Trim (column_value) l
FROM dual,
XMLTABLE ( ('"'|| replace (filecontent, '\r\n', '","')||'"')))
LOOP
FOR i2 IN (select regexp_substr(i.l,'[^,]+',1) UNIDAD,
regexp_substr(i.l,'[^,]+',1,2) PERIODO,
regexp_substr(i.l,'[^,]+',1,3) COMPANIA,
regexp_substr(i.l,'[^,]+',1,4) ID_ORGANIZACION,
regexp_substr(i.l,'[^,]+',1,5) ID_LIBRO_CONTABLE,
regexp_substr(i.l,'[^,]+',1,6) ID_PERSONA_REG,
regexp_substr(i.l,'[^,]+',1,7) NUMERO_EMPLEADO_REG,
regexp_substr(i.l,'[^,]+',1,8) ID_PERSONA_DIS,
regexp_substr(i.l,'[^,]+',1,9) NUMERO_EMPLEADO_DIS,
regexp_substr(i.l,'[^,]+',1,10) ID_PERSONA_SUP,
regexp_substr(i.l,'[^,]+',1,11) NUMERO_EMPLEADO_SUP,
regexp_substr(i.l,'[^,]+',1,12) ID_PERSONA_GTE,
regexp_substr(i.l,'[^,]+',1,13) NUMERO_EMPLEADO_GTE,
regexp_substr(i.l,'[^,]+',1,14) NUMERO_EMPLEADO_CHEFF_GTE,
regexp_substr(i.l,'[^,]+',1,15) COMENTARIOS,
regexp_substr(i.l,'[^,]+',1,16) TIPO_SUPERVISOR,
regexp_substr(i.l,'[^,]+',1,17) ID_PERSONA_CHEFF_GTE
from dual) LOOP
IF(i2.UNIDAD = 'UNIDAD'
and i2.PERIODO = 'PERIODO'
and i2.COMPANIA = 'COMPANIA'
and i2.ID_ORGANIZACION = 'ID_ORGANIZACION'
and i2.ID_LIBRO_CONTABLE = 'ID_LIBRO_CONTABLE'
and i2.ID_PERSONA_REG = 'ID_PERSONA_REG'
and i2.NUMERO_EMPLEADO_REG = 'NUMERO_EMPLEADO_REG'
and i2.ID_PERSONA_DIS = 'ID_PERSONA_DIS'
and i2.NUMERO_EMPLEADO_DIS = 'NUMERO_EMPLEADO_DIS'
and i2.ID_PERSONA_SUP = 'ID_PERSONA_SUP'
and i2.NUMERO_EMPLEADO_SUP = 'NUMERO_EMPLEADO_SUP'
and i2.ID_PERSONA_GTE = 'ID_PERSONA_GTE'
and i2.NUMERO_EMPLEADO_GTE = 'NUMERO_EMPLEADO_GTE'
and i2.NUMERO_EMPLEADO_CHEFF_GTE = 'NUMERO_EMPLEADO_CHEFF_GTE'
and i2.COMENTARIOS = 'COMENTARIOS'
and i2.TIPO_SUPERVISOR = 'TIPO_SUPERVISOR'
and i2.ID_PERSONA_CHEFF_GTE = 'ID_PERSONA_CHEFF_GTE')THEN
dbms_output.Put_line ('No se procesan los cabeceros');
ELSE
dbms_output.Put_line ('UNIDAD : '
|| i2.UNIDAD
|| Chr (10));
dbms_output.Put_line ('PERIODO : '
|| i2.PERIODO
|| Chr (10));
dbms_output.Put_line ('COMPANIA : '
|| i2.COMPANIA
|| Chr (10));
dbms_output.Put_line ('ID_ORGANIZACION : '
|| i2.ID_ORGANIZACION
|| Chr (10));
dbms_output.Put_line ('ID_LIBRO_CONTABLE : '
|| i2.ID_LIBRO_CONTABLE
|| Chr (10));
dbms_output.Put_line ('ID_PERSONA_REG : '
|| i2.ID_PERSONA_REG
|| Chr (10));
dbms_output.Put_line ('NUMERO_EMPLEADO_REG : '
|| i2.NUMERO_EMPLEADO_REG
|| Chr (10));
dbms_output.Put_line ('ID_PERSONA_DIS : '
|| i2.ID_PERSONA_DIS
|| Chr (10));
dbms_output.Put_line ('NUMERO_EMPLEADO_DIS : '
|| i2.NUMERO_EMPLEADO_DIS
|| Chr (10));
dbms_output.Put_line ('ID_PERSONA_SUP : '
|| i2.ID_PERSONA_SUP
|| Chr (10));
dbms_output.Put_line ('NUMERO_EMPLEADO_SUP : '
|| i2.NUMERO_EMPLEADO_SUP
|| Chr (10));
dbms_output.Put_line ('ID_PERSONA_GTE : '
|| i2.ID_PERSONA_GTE
|| Chr (10));
dbms_output.Put_line ('NUMERO_EMPLEADO_GTE : '
|| i2.NUMERO_EMPLEADO_GTE
|| Chr (10));
dbms_output.Put_line ('NUMERO_EMPLEADO_CHEFF_GTE : '
|| i2.NUMERO_EMPLEADO_CHEFF_GTE
|| Chr (10));
dbms_output.Put_line ('COMENTARIOS : '
|| i2.COMENTARIOS
|| Chr (10));
dbms_output.Put_line ('TIPO_SUPERVISOR : '
|| i2.TIPO_SUPERVISOR
|| Chr (10));
dbms_output.Put_line ('ID_PERSONA_CHEFF_GTE : '
|| i2.ID_PERSONA_CHEFF_GTE
|| Chr (10));
dbms_output.Put_line ('***Termina línea***'
|| Chr (10));
END IF;
END LOOP;
END LOOP;
END;
and to run:
DECLARE
var_FILECONTENT CLOB;
BEGIN
var_FILECONTENT := 'UNIDAD,PERIODO,COMPANIA,ID_ORGANIZACION,ID_LIBRO_CONTABLE,ID_PERSONA_REG,NUMERO_EMPLEADO_REG,ID_PERSONA_DIS,NUMERO_EMPLEADO_DIS,ID_PERSONA_SUP,NUMERO_EMPLEADO_SUP,ID_PERSONA_GTE,NUMERO_EMPLEADO_GTE,NUMERO_EMPLEADO_CHEFF_GTE,COMENTARIOS,TIPO_SUPERVISOR,ID_PERSONA_CHEFF_GTE\r\n0101,42125,2525,300000001698016,78563,300000002861315,22,4855,e4730,155,e855,455,255,7525,Test_procedure,P,1525\r\n';
TOKS_HCM_PROCESS_FILE (FILECONTENT => var_FILECONTENT);
END;
I want to insert it in a table
Why didn't you do that already? Instead of displaying values on the screen (using DBMS_OUTPUT.PUT_LINE), use INSERT command. The rest of the procedure - if it works well - doesn't have to be modified.
Something like this (simplified):
CREATE OR replace PROCEDURE process_file (filecontent CLOB)
AS
contador NUMBER := 0;
BEGIN
FOR i IN (SELECT Trim (column_value) l
FROM dual,
XMLTABLE ( ('"'|| replace (filecontent, '\r\n', '","')||'"')))
LOOP
FOR i2 IN (select regexp_substr(i.l,'[^,]+',1) UNIDAD,
regexp_substr(i.l,'[^,]+',1,2) PERIODO,
regexp_substr(i.l,'[^,]+',1,3) COMPANIA, ...
from dual
) LOOP
IF( i2.UNIDAD = 'UNIDAD'
and i2.PERIODO = 'PERIODO'
and i2.COMPANIA = 'COMPANIA' ...
) THEN
dbms_output.Put_line ('No se procesan los cabeceros');
ELSE
--> this is what you need
insert into target_table(unidad, periodo, compania, ...)
values (i1.unidad, i2.periodo, i2.compania, ...);
--> end of what you need
END IF;
END LOOP;
END LOOP;
END;