[email protected](10);
2.用UTL_FILE包来操作文件
2.用UTL_FILE包来操作文件
解决方案 »
- 请教大神
- 一个update 语句,大家进来看看。急。
- 一个关于数据库的问题
- SQLLoader导中文,结果数据错位
- 求适合初学者的oracle书籍。
- 请教存储过程的编写
- 新手发问!!!
- 一个多列 行列倒置的问题,各位牛人帮帮忙。。。
- 求"指定文件"所在目录从根起的文件目录路径sql,急呀,急呀,急呀
- archivelog与noarchivelog的作用是什么?什么区别?向各位求教(希望详细一些)
- 请问哪儿有完整详细的SQL92,SQL99标准 的SQL语法?
- 我在文档上看到这样的一句话,“variable (or PL/SQL output variable)”,请问如果这个pl/sql是一个语句块,而不是存储过程,那么可以有
Creating a fileA common way to use files does not involve the contents of the file as much as a confirmation that the file does in fact exist. You can use the two modules defined next to create a file and then check to see if that file exists. Notice that when we create a file in this type of situation, we do not even bother to return the handle to the file. The purpose of the first program, create_file, is simply to make sure that a file with the specified name (and optional line of text) is out there on disk.PROCEDURE create_file
(loc_in IN VARCHAR2, file_in IN VARCHAR2, line_in IN VARCHAR2 := NULL)
IS
file_handle UTL_FILE.FILE_TYPE;
BEGIN
/*
|| Open the file, write a single line and close the file.
*/
file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'W');
IF line_in IS NOT NULL
THEN
UTL_FILE.PUT_LINE (file_handle, line_in);
ELSE
UTL_FILE.PUT_LINE
(file_handle, 'I make my disk light blink, therefore I am.');
END IF;
UTL_FILE.FCLOSE (file_handle);
END;Testing for a file existenceThe second program checks to see if a file exists. Notice that it creates a local procedure to handle the close logic (which is called both in the body of the function and in the exception section). CCREATE OR REPLACE FUNCTION file_exists
(loc_in IN VARCHAR2,
file_in IN VARCHAR2,
close_in IN BOOLEAN := FALSE)
RETURN BOOLEAN
IS
file_handle UTL_FILE.FILE_TYPE;
retval BOOLEAN; PROCEDURE closeif IS
BEGIN
IF close_in AND UTL_FILE.IS_OPEN (file_handle)
THEN
UTL_FILE.FCLOSE (file_handle);
END IF;
END;
BEGIN
/* Open the file. */
file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'R'); /* Return the result of a check with IS_OPEN. */
retval := UTL_FILE.IS_OPEN (file_handle); closeif; RETURN retval;
EXCEPTION
WHEN OTHERS
THEN
closeif;
RETURN FALSE;
END;
/Searching a file for a stringThis example builds a function that simulates the INSTR function with operating system files. This function returns the line number in a file containing the specified text. It will also take into consideration if we want to find the second occurrence in the file, need to start the search from the tenth line, or want to perform a case-insensitive search. Note that it's important when building a utility like line_with_text how it might be used before you build it. Don just build for today requirement. Anticipate what you will need tomorrow and next week as well. For line_with_text, our 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 INTEGERIt's a lot of parameter passing, but let抯 take a look at the kind of flexibility we gain from using these additional arguments. The following list provides a description of each parameter.Parameter Description
loc_in The location of the file on the operating system
file_in The name of the file to be opened
text_in The chunk of text to be searched for in each line of the file
occurrence_in The number of times the text should be found in distinct lines in the file before the function returns the line number
srart_line_in The first line in the file from which the function should start its search
end_line_in The last line in the file to which the function should continue its search; if zero, then search through end of file
ignore_case_in Indicates whether the case of the file contents and text_in should be ignored when checking for its presence in the lineNotice that the parameters occurrence_in through ignore_case_in have default values, so we can call this function with a minimum of parameters and find the first line in a file that contains our text:IF line_with_text ('names.vp', 'Hanubi') > 0
THEN
MESSAGE ('Josephine Hanubi is a vice president!');
END IF;However, we can also do much more:w Confirm that the role assigned to this user is SUPERVISOR:
line_with_text ('c:\temp', 'config.usr', 'ROLE=SUPERVISOR')w Find the second occurrence of DELETE starting with the fifth line:
line_with_text ('/tmp', 'commands.dat', 'delete', 2, 5)w Verify that the third line contains a terminal type specification:
line_with_text ('g:\apps\user\', 'setup.cfg', 'termtype=', 1, 3, 3)Here is the code for the line_with_text function:CREATE OR REPLACE 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
/*
|| An "INSTR" for operating system files. Returns the line number of
|| a file in which a text string was found.
*/
IS
/* Handle to the file. Only will open if arguments are valid. */
file_handle UTL_FILE.FILE_TYPE; /* Holds a line of text from the file. */
line_of_text VARCHAR2(1000); text_loc INTEGER;
found_count INTEGER := 0; /* Boolean to determine if there are more values to read */
no_more_lines BOOLEAN := FALSE; /* Function return value */
return_value INTEGER := 0;
BEGIN
/* Assert valid arguments. If any fail, return NULL. */
IF loc_in IS NULL OR
file_in IS NULL OR
text_in IS NULL OR
occurrence_in <= 0 OR
start_line_in < 1 OR
end_line_in < 0
THEN
return_value := NULL;
ELSE
/* All arguments are fine. Open and read through the file. */
file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'R');
LOOP
/* Get next line and exit if at end of file. */
get_nextline (file_handle, line_of_text, no_more_lines);
EXIT WHEN no_more_lines; /* Have another line from file. */
return_value := return_value + 1; /* If this line is between the search range... */
IF (return_value BETWEEN start_line_in AND end_line_in) OR
(return_value >= start_line_in AND end_line_in = 0)
THEN
/* Use INSTR to see if text is present. */
IF NOT ignore_case_in
THEN
text_loc := INSTR (line_of_text, text_in);
ELSE
text_loc := INSTR (UPPER (line_of_text), UPPER (text_in));
END IF; /* If text location is positive, have a match. */
IF text_loc > 0
THEN
/* Increment found counter. Exit if matches request. */
found_count := found_count + 1;
EXIT WHEN found_count = occurrence_in;
END IF;
END IF;
END LOOP;
UTL_FILE.FCLOSE (file_handle);
END IF; IF no_more_lines
THEN
/* read through whole file without success. */
return_value := NULL;
END IF; RETURN return_value;
END;
pl/sql只能操作文本文件