tailieunhanh - Oracle Built−in Packages- P74

Oracle Built−in Packages- P74: 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 I include a trace message in the package commented out on the companion disk so that we can watch the path-based open doing its work SQL @ .looking in c temp .looking in d oreilly builtins code CREATE OR REPLACE PACKAGE filelO It s nice having programs do your work for you isn t it Here is the implementation of the fileIO package with path usage Filename on companion disk CREATE OR REPLACE PACKAGE BODY filelO IS g_path VARCHAR2 2000 PROCEDURE setpath str IN VARCHAR2 IS BEGIN g_path str END FUNCTION path RETURN VARCHAR2 IS BEGIN RETURN g_path END FUNCTION open file IN VARCHAR2 filemode IN VARCHAR2 RETURN IS Location of next path separator v_lastsep PLS_INTEGER 1 v_sep PLS_INTEGER INSTR g_path c_delim v_dir VARCHAR2 500 retval BEGIN For each directory in the path attempt to open the file. LOOP BEGIN IF v_sep 0 THEN v_dir SUBSTR g_path v_lastsep ELSE v_dir SUBSTR g_path v_lastsep v_sep - v_lastsep END IF retval v_dir file R EXIT EXCEPTION WHEN OTHERS THEN IF v_sep 0 THEN RAISE ELSE v_lastsep v_sep 1 v_sep INSTR g_path c_delim v_sep 1 END IF END END LOOP RETURN retval END END Tips on Using UTL_FILE 356 Appendix A What s on the Companion Disk The logic in this is a little bit complicated because I need to parse the semicolon-delimited list. The v_sep variable contains the location in the path of the next delimiter. The v_lastsep variable contains the location of the last delimiter. I have to include special handling for recognizing when I am at the last directory in the path v_sep equals 0 . Notice that I do not hard-code the semi-colon into this program. Instead I reference the c_delim constant. The most important implementation detail is that I place the call to FOPEN inside a loop. With each iteration of the loop body I extract a directory from the path. Once I have the next directory to search I call the FOPEN function to see if I