Home » SQL & PL/SQL » SQL & PL/SQL » How to search a string in a text file
How to search a string in a text file [message #286434] Fri, 07 December 2007 06:22 Go to next message
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 #286435 is a reply to message #286434] Fri, 07 December 2007 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
INSTR
LIKE

Regards
Michel

[Updated on: Fri, 07 December 2007 06:25]

Report message to a moderator

Re: How to search a string in a text file [message #286443 is a reply to message #286435] Fri, 07 December 2007 07:12 Go to previous messageGo to next message
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 #286445 is a reply to message #286443] Fri, 07 December 2007 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't use utl_file.
Use external table then you have the power of SQL.

Regards
Michel
Re: How to search a string in a text file [message #286449 is a reply to message #286443] Fri, 07 December 2007 07:58 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
navaneethdba wrote on Fri, 07 December 2007 08:12

Tx for ur reply.



Please stop using nonsensical scribble like this. It is, after all a rule of the forum.
Re: How to search a string in a text file [message #286450 is a reply to message #286445] Fri, 07 December 2007 08:02 Go to previous messageGo to next message
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 #286451 is a reply to message #286450] Fri, 07 December 2007 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

Ya, but i dont have any idea on external tables

Ya, but search.

Regards
Michel
Re: How to search a string in a text file [message #286461 is a reply to message #286434] Fri, 07 December 2007 09:07 Go to previous messageGo to next message
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 #286693 is a reply to message #286451] Sun, 09 December 2007 21:31 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Hi,

Ok michel i will try that too.But this is halfway done so i will try to complete it and try that one.

@cmerry
Thanks for ur reply..

TIA,
Re: How to search a string in a text file [message #286694 is a reply to message #286434] Sun, 09 December 2007 21:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://en.wikipedia.org/wiki/Ur

Ur was an ancient city in southern Mesopotamia, located near the mouth (at the time) of the Euphrates and Tigris rivers
Re: How to search a string in a text file [message #286724 is a reply to message #286434] Mon, 10 December 2007 00:45 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Thanks for information about Ur Wink

Kiran.
Re: How to search a string in a text file [message #286851 is a reply to message #286451] Mon, 10 December 2007 04:31 Go to previous messageGo to next message
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 #287018 is a reply to message #286851] Mon, 10 December 2007 23:12 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Any guidance plz...
Re: How to search a string in a text file [message #287021 is a reply to message #287018] Mon, 10 December 2007 23:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #287048 is a reply to message #287029] Tue, 11 December 2007 00:25 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Thanks all,

I find the problem.

The directory which i am acessing the file is not oracle's home directory, so it is not working.

How to read the file from different directory which is not oracles home directory.

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 Go to previous messageGo to next message
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

Re: How to search a string in a text file [message #287058 is a reply to message #287052] Tue, 11 December 2007 00:44 Go to previous message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
sorry! It was my mistake..


SQL> select * from all_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------
SYS TT_BCK /nava_db/backup/



TIA,

[Updated on: Tue, 11 December 2007 00:47]

Report message to a moderator

Previous Topic: help~~problem of Trigger
Next Topic: Delete records with limit
Goto Forum:
  


Current Time: Sat Nov 09 16:58:12 CST 2024