How can I open an OS text file inside one Stored Procedure

HI,
I have one Operating System text file in some directory in UNIX environment . How can I open this file inside one Stored Procedure.
Thanks in advance.
DillipHello,

to open flat files in the OS use the package SYS.UTL_FILE. You can use the functions/procedures fopen, get_line and fclose to access the datas in the ff.

You get direct access to the file you must enter the path of the file in your initial parameter. Otherwise Oracle can not read or write the file.

The parameter is UTL_FILE_DIR.

Here is a short example:

declare
fptr utl_file.file_type;
buff varchar2(2048);
line_no number(10):=0;
loc_no integer;
begin
fptr:=utl_file.fopen('C:\Oracle\admin\PENT\udump', 'ORA00324.TRC','R');
utl_file.get_line(fptr,buff);
utl_file.fclose(fptr);
exception
when no_data_found then
utl_file.fclose(fptr);
dbms_output.put_line('Number of lines parsed ='||line_no);
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
raise_application_error(-20100,'file error');
when utl_file.invalid_mode then
dbms_output.put_line('invalid_mode');
raise_application_error(-20100,'file error');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid_filehandle');
raise_application_error(-20100,'file error');
when utl_file.invalid_operation then
dbms_output.put_line('invalid_operation');
raise_application_error(-20100,'file error');
when utl_file.read_error then
dbms_output.put_line('read_error');
raise_application_error(-20100,'file error');
when utl_file.write_error then
dbms_output.put_line('write_error');
raise_application_error(-20100,'file error');
when utl_file.internal_error then
dbms_output.put_line('internal_error');
raise_application_error(-20100,'file error');
when others then
dbms_output.put_line('un-handled');
raise_application_error(-20100,'file error');
end;

Hope that helps ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

how can i move a local directory with all files within a procedure

Hello i'd like to know how i can move all my files from C:\SQL to C:\DB
using a Table with a reference to the batch file.
it looks like that:
1.) i have batch.bat file with " call move c:\sql\* to c:\db "
2.) a table MyTable contains a column with value 'c:\db\batch.bat'
3.) i'd like to know if there are any procedure possible, that can execute
this batch.bat from MyTable.
maybe using ASP...?
can anybody tell if thats possible'Felix
Try xp_cmdshell system stored procedure in the BOL
"Felix" <andreas21@.gmx.net> wrote in message
news:uj0LKps1EHA.2292@.TK2MSFTNGP15.phx.gbl...
> Hello i'd like to know how i can move all my files from C:\SQL to C:\DB
> using a Table with a reference to the batch file.
> it looks like that:
> 1.) i have batch.bat file with " call move c:\sql\* to c:\db "
> 2.) a table MyTable contains a column with value 'c:\db\batch.bat'
> 3.) i'd like to know if there are any procedure possible, that can execute
> this batch.bat from MyTable.
> maybe using ASP...?
> can anybody tell if thats possible'
>|||thanks its working now! great!!!
master.dbo.xp_cmdshell procedure...
same question, is that possible to use such a systm procedure on mysql
server? any idea?
ive tried but there are no such cmdshell procedure like that. maybe there is
another way?
"Uri Dimant" schrieb im Newsbeitrag
news:eNzgelu1EHA.2612@.TK2MSFTNGP14.phx.gbl...
> Felix
> Try xp_cmdshell system stored procedure in the BOL
>
>
> "Felix" wrote in message
> news:uj0LKps1EHA.2292@.TK2MSFTNGP15.phx.gbl...
>

How can I get the files under a special directory on a remote sql server?

we can get directory information by EnumDirectories method of sql server
object from a remote sql server, but how can I get the files under a special
directory on a remote sql server?
I tried finding result in MSDN, but I can't get it. Any one can help me?
Thanks in advance.
Bill WhiteBill White wrote:
> we can get directory information by EnumDirectories method of sql
> server object from a remote sql server, but how can I get the files
> under a special directory on a remote sql server?
> I tried finding result in MSDN, but I can't get it. Any one can help
> me?
> Thanks in advance.
> Bill White
From SQL Server?
Exec master..xp_cmdshell N'DIR C:\MyFolder\*.*'
You can create a temp table and insert the results of xp_cmdshell into
the temp table for processing if you need to. If this is SQL 2005, then
xp_cmdshell could be disabled on the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||It's a good idea, but I have another question about it.
Does SQL Server do it in this manner? or does it by another unopened method?
Thanks a lot.
Bill White
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u4i%23wJL9FHA.2816@.tk2msftngp13.phx.gbl...
Bill White wrote:
> we can get directory information by EnumDirectories method of sql
> server object from a remote sql server, but how can I get the files
> under a special directory on a remote sql server?
> I tried finding result in MSDN, but I can't get it. Any one can help
> me?
> Thanks in advance.
> Bill White
From SQL Server?
Exec master..xp_cmdshell N'DIR C:\MyFolder\*.*'
You can create a temp table and insert the results of xp_cmdshell into
the temp table for processing if you need to. If this is SQL 2005, then
xp_cmdshell could be disabled on the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com