703: c_commit_point CONSTANT NUMBER := 20;
704: c_data_exchange_dir CONSTANT VARCHAR2 (30) := 'PER_DATA_EXCHANGE_DIR';
705:
706: -- File Handling variables
707: l_file_type UTL_FILE.file_type;
708: l_filename VARCHAR2 (240);
709: l_location VARCHAR2 (4000);
710: l_line_read VARCHAR2 (4000) := NULL;
711:
742:
743:
744: -- Open flat file
745: l_file_type :=
746: UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
747:
748:
749: -- Loop over the file, reading in each line.
750: -- GET_LINE will raise NO_DATA_FOUND when it r4eaches EOF
752:
753: <
754: LOOP
755: BEGIN
756: UTL_FILE.get_line (l_file_type, l_line_read);
757: l_batch_seq := l_batch_seq + 1;
758:
759: hr_utility.set_location ( ' line read: ' || l_line_read ,60);
760:
843:
844: -- Commit the outstanding records
845: COMMIT;
846:
847: UTL_FILE.fclose (l_file_type);
848: hr_utility.set_location ( 'Leaving:'|| l_proc, 260);
849:
850:
851: EXCEPTION
854: WHEN e_fatal_error
855: -- No directory specified
856: THEN
857: -- Close the file in case of error
858: IF UTL_FILE.is_open (l_file_type)
859: THEN
860: UTL_FILE.fclose (l_file_type);
861: END IF;
862:
856: THEN
857: -- Close the file in case of error
858: IF UTL_FILE.is_open (l_file_type)
859: THEN
860: UTL_FILE.fclose (l_file_type);
861: END IF;
862:
863: hr_utility.set_location (l_proc, 270);
864: -- Set retcode to 2, indicating an ERROR to the ConcMgr
871: errbuf := hr_utility.get_message;
872:
873: -- ***********************************************
874:
875: WHEN UTL_FILE.invalid_operation
876:
877: -- File could not be opened as requested, perhaps because of operating system permissions
878: -- Also raised when attempting a write operation on a file opened for read, or a read operation
879: -- on a file opened for write.
877: -- File could not be opened as requested, perhaps because of operating system permissions
878: -- Also raised when attempting a write operation on a file opened for read, or a read operation
879: -- on a file opened for write.
880: THEN
881: IF UTL_FILE.is_open (l_file_type)
882: THEN
883: UTL_FILE.fclose (l_file_type);
884: END IF;
885:
879: -- on a file opened for write.
880: THEN
881: IF UTL_FILE.is_open (l_file_type)
882: THEN
883: UTL_FILE.fclose (l_file_type);
884: END IF;
885:
886: hr_utility.set_location (l_proc, 280);
887: retcode := g_error;
888: errbuf := 'Reading File ('||l_location ||' -> ' || l_filename || ') - Invalid Operation.';
889:
890: -- ***********************************************
891:
892: WHEN UTL_FILE.internal_error
893: -- Unspecified internal error
894: THEN
895: IF UTL_FILE.is_open (l_file_type)
896: THEN
891:
892: WHEN UTL_FILE.internal_error
893: -- Unspecified internal error
894: THEN
895: IF UTL_FILE.is_open (l_file_type)
896: THEN
897: UTL_FILE.fclose (l_file_type);
898: END IF;
899:
893: -- Unspecified internal error
894: THEN
895: IF UTL_FILE.is_open (l_file_type)
896: THEN
897: UTL_FILE.fclose (l_file_type);
898: END IF;
899:
900: hr_utility.set_location (l_proc, 290);
901: retcode := g_error;
902: errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Internal Error.';
903:
904: -- ***********************************************
905:
906: WHEN UTL_FILE.invalid_mode
907: -- Invalid string specified for file mode
908: THEN
909: IF UTL_FILE.is_open (l_file_type)
910: THEN
905:
906: WHEN UTL_FILE.invalid_mode
907: -- Invalid string specified for file mode
908: THEN
909: IF UTL_FILE.is_open (l_file_type)
910: THEN
911: UTL_FILE.fclose (l_file_type);
912: END IF;
913:
907: -- Invalid string specified for file mode
908: THEN
909: IF UTL_FILE.is_open (l_file_type)
910: THEN
911: UTL_FILE.fclose (l_file_type);
912: END IF;
913:
914: hr_utility.set_location (l_proc, 300);
915: retcode := g_error;
916: errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid Mode.';
917:
918: -- ***********************************************
919:
920: WHEN UTL_FILE.invalid_path
921: -- Directory or filename is invalid or not accessible
922: THEN
923: IF UTL_FILE.is_open (l_file_type)
924: THEN
919:
920: WHEN UTL_FILE.invalid_path
921: -- Directory or filename is invalid or not accessible
922: THEN
923: IF UTL_FILE.is_open (l_file_type)
924: THEN
925: UTL_FILE.fclose (l_file_type);
926: END IF;
927:
921: -- Directory or filename is invalid or not accessible
922: THEN
923: IF UTL_FILE.is_open (l_file_type)
924: THEN
925: UTL_FILE.fclose (l_file_type);
926: END IF;
927:
928: retcode := g_error;
929: errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid Path or Filename.';
930: hr_utility.set_location (l_proc, 310);
931:
932: -- ***********************************************
933:
934: WHEN UTL_FILE.invalid_filehandle
935: -- File type does not specify an open file
936: THEN
937: IF UTL_FILE.is_open (l_file_type)
938: THEN
933:
934: WHEN UTL_FILE.invalid_filehandle
935: -- File type does not specify an open file
936: THEN
937: IF UTL_FILE.is_open (l_file_type)
938: THEN
939: UTL_FILE.fclose (l_file_type);
940: END IF;
941:
935: -- File type does not specify an open file
936: THEN
937: IF UTL_FILE.is_open (l_file_type)
938: THEN
939: UTL_FILE.fclose (l_file_type);
940: END IF;
941:
942: hr_utility.set_location (l_proc, 320);
943: retcode := g_error;
941:
942: hr_utility.set_location (l_proc, 320);
943: retcode := g_error;
944: errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid File Type.';
945: WHEN UTL_FILE.read_error
946:
947: -- ***********************************************
948:
949: -- Operating system error occurred during a read operation
947: -- ***********************************************
948:
949: -- Operating system error occurred during a read operation
950: THEN
951: IF UTL_FILE.is_open (l_file_type)
952: THEN
953: UTL_FILE.fclose (l_file_type);
954: END IF;
955:
949: -- Operating system error occurred during a read operation
950: THEN
951: IF UTL_FILE.is_open (l_file_type)
952: THEN
953: UTL_FILE.fclose (l_file_type);
954: END IF;
955:
956: hr_utility.set_location (l_proc, 330);
957: retcode := g_error;