How to search a string in a text file [message #286434] |
Fri, 07 December 2007 06:22 |
navaneethdba
Messages: 71 Registered: July 2007 Location: chennai
|
Member |
|
|
Hi,
version 10.2.0
How to find a string in a text file using pl/sql.
I am able to read a whole text file but i need to find a specified string in that file. AM using this procedure..
CREATE OR REPLACE procedure multi_lines
as
f utl_file.file_type;
s varchar2(32000);
--c number :=0;
begin
f := utl_file.fopen('SAMPLE','rman_backup.log','R');
loop
utl_file.get_line(f,s);
-- insert into i values(s);
-- commit;
dbms_output.put_line(s);
--instr(s,'error')
-- c:= c + 1;
end loop;
exception
when NO_DATA_FOUND then
utl_file.fclose(f);
--dbms_output.put_line('No. of rows inserted : '|| c);
end;
/
I dont know how to find it can any one guide me plz...
TIA,
|
|
|
|
Re: How to search a string in a text file [message #286443 is a reply to message #286435] |
Fri, 07 December 2007 07:12 |
navaneethdba
Messages: 71 Registered: July 2007 Location: chennai
|
Member |
|
|
Tx for ur reply.
But what i am doing is
Taking the logfile created by rman and need to check the string
"Finished backup at 'date'".
Am taking the textfile line by line, so i dont know how to match the string whether present in the line or not.
Can u help me in this issue...
TIA,
|
|
|
|
|
Re: How to search a string in a text file [message #286450 is a reply to message #286445] |
Fri, 07 December 2007 08:02 |
navaneethdba
Messages: 71 Registered: July 2007 Location: chennai
|
Member |
|
|
Hi,
Ya, but i dont have any idea on external tables
shall i us like this..plz advice..
CREATE OR REPLACE PROCEDURE MULTI_LINES
AS
F UTL_FILE.FILE_TYPE;
S VARCHAR2(32000);
STR VARCHAR2(1000);
BEGIN
SELECT TO_CHAR(SYSDATE) INTO STR FROM DUAL;
F := UTL_FILE.FOPEN('SAMPLE','rman_backup.log','R');
STR := 'Finished backup at '||STR;
LOOP
UTL_FILE.GET_LINE(F,S);
-- DBMS_OUTPUT.PUT_LINE(S);
IF (SUBSTR(S,1)= STR)
THEN UTL_MAIL.SEND(SENDER=>'RMAN',RECIPIENTS=>'hhash@yeye.com',SUBJECT=>'Backup status',MESSAGE=>'Finished backup sucessfully');
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
--DBMS_OUTPUT.PUT_LINE('No. of rows inserted : '|| C);
END;
/
|
|
|
|
Re: How to search a string in a text file [message #286461 is a reply to message #286434] |
Fri, 07 December 2007 09:07 |
|
cmerry
Messages: 109 Registered: November 2005 Location: Idaho
|
Senior Member |
|
|
Another option would be to create a table-valued function that reads the file and returns each line as a row.
(This was originally found on asktom to read trace files.)
CREATE OR REPLACE DIRECTORY file_dir
AS 'path to file';
CREATE OR REPLACE TYPE vcArray AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION file_contents
(p_file IN VARCHAR2)
RETURN vcArray PIPELINED AS
l_bfile BFILE;
l_last NUMBER := 1;
l_current NUMBER := 1;
BEGIN
l_bfile := BFILENAME( 'FILE_DIR', p_file );
dbms_lob.fileopen( l_bfile );
LOOP
l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
EXIT WHEN ( NVL( l_current, 0 ) = 0 );
PIPE ROW( utl_raw.cast_to_varchar2( dbms_lob.substr( l_bfile
, l_current-l_last+1
, l_last ) ) );
l_last := l_current + 1;
END LOOP;
dbms_lob.fileclose( l_bfile );
RETURN;
END file_contents;
Then just query the result of the function using the TABLE keyword and whatever functions you need.
[Updated on: Fri, 07 December 2007 09:08] Report message to a moderator
|
|
|
|
|
|
Re: How to search a string in a text file [message #286851 is a reply to message #286451] |
Mon, 10 December 2007 04:31 |
navaneethdba
Messages: 71 Registered: July 2007 Location: chennai
|
Member |
|
|
Hi,
The procedure was sucessfull in Windows, I tried it in AIX its
giving error.I am in client machine.
SQL> execute multi_lines
BEGIN multi_lines; END;
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "NAVA.MULTI_LINES", line 8
ORA-06512: at line 1
SQL> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ -----------------------------------------------------------------
SYS TT_BCK /nava/backup/
$ ls -l /nava/backup/rmanbckp_status.log
-rwxrwxrwx /nava/backup/rmanbckp_status.log
Plz Advice...
TIA,
|
|
|
|
Re: How to search a string in a text file [message #287021 is a reply to message #287018] |
Mon, 10 December 2007 23:18 |
Mohammad Taj
Messages: 2412 Registered: September 2006 Location: Dubai, UAE
|
Senior Member |
|
|
ORA-29283: invalid file operation
Cause: An attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system.
Action: Verify file and directory access privileges on the file system, and if reading, verify that the file exists.
[Updated on: Mon, 10 December 2007 23:18] Report message to a moderator
|
|
|
Re: How to search a string in a text file [message #287029 is a reply to message #287021] |
Mon, 10 December 2007 23:41 |
navaneethdba
Messages: 71 Registered: July 2007 Location: chennai
|
Member |
|
|
Hi,
Thanks for your reply...
$ ls -l nava_db
drwxr-xr-x 2 oracle oinstall 4096 Dec 11 11:25 backup
$ pwd
/nava_db/backup
$ ls -l rmanbckp_status.log
-rwxrwxrwx 1 oracle oinstall 148625 Dec 10 16:15 rmanbckp_status.log
The file exists in the location and it has all rights.
TIA,
|
|
|
|
Re: How to search a string in a text file [message #287052 is a reply to message #287029] |
Tue, 11 December 2007 00:38 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
navaneethdba wrote on Tue, 11 December 2007 06:41 |
$ pwd
/nava_db/backup
|
The same poster wrote earlier |
SQL> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ -----------------------------------------------------------------
SYS TT_BCK /nava/backup/
|
nava_db != nava
[Updated on: Tue, 11 December 2007 00:38] Report message to a moderator
|
|
|
|