Oracle Built−in Packages- P75: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk THEN file_handle END IF END BEGIN Open the file. file_handle loc_in file_in R Return the result of a check with IS_OPEN. retval file_handle closeif RETURN retval EXCEPTION WHEN OTHERS THEN closeif RETURN FALSE END Searching a file for a string Because I found the INSTR function to be so useful I figured that this same kind of operation would also really come in handy with operating system files. The line_with_text function coming up shortly returns the line number in a file containing the specified text. The simplest version of such a function would have a specification like this FUNCTION line_with_text loc_in IN VARCHAR2 file_in IN VARCHAR2 text_in IN VARCHAR2 RETURN INTEGER In other words given a location a filename and a chunk of text find the first line in the file that contains the text. You could call this function as follows IF line_with_text h pers Hanubi 0 THEN MESSAGE Josephine Hanubi is a vice president END IF The problem with this version of line_with_text is its total lack of vision. What if I want to find the second occurrence in the file What if I need to start my search from the tenth line What if I want to perform a case-insensitive search None of these variations are supported. I urge you strongly to think through all the different ways a utility like line_with_text might be used before you build it. Don t just build for today s requirement. Anticipate what you will need tomorrow and next week as well. For line_with_text a broader vision would yield a specification like this FUNCTION line_with_text loc_in IN VARCHAR2 file_in IN VARCHAR2 text_in IN VARCHAR2 occurrence_in IN INTEGER 1 start_line_in IN INTEGER 1 end_line_in IN INTEGER 0 ignore_case_in IN BOOLEAN TRUE RETURN INTEGER UTL_FILE Examples 361 Appendix A What s on the Companion Disk Wow That s a lot more parameter passing. Let s take a look at the kind of flexibility we gain from
