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 insert a column using a script (instead of appending)?

Hi,

I recently had to add a new column to a table that resides off-site,
in a customer's environment. Previously, the table had around 300
columns, all of which were in alphabetical order.

I found that the ALTER TABLE command appeared to be only capable of
appending a new column, but wasn't capable of inserting one into the
middle of the table.

Since alphabetical order wasn't a requirement, but just a "nice to
look at" feature, I appended it anyway, and forgot about it.

Anyone know of a clever way to do this though?

Curiously,

Warren Wright
Scorex Development TeamWarren Wright (warren.wright@.us.scorex.com) writes:
> I recently had to add a new column to a table that resides off-site,
> in a customer's environment. Previously, the table had around 300
> columns, all of which were in alphabetical order.
> I found that the ALTER TABLE command appeared to be only capable of
> appending a new column, but wasn't capable of inserting one into the
> middle of the table.
> Since alphabetical order wasn't a requirement, but just a "nice to
> look at" feature, I appended it anyway, and forgot about it.
> Anyone know of a clever way to do this though?

The "clever" way is to fiddle with syscolumns, but I highly discourge
from this.

The normal way is to go the long way: rename the old table, create
the new table including triggers, constrains and clustered index, insert
data from the old table to the new, move referencing foreign keys, drop the
old table. Restore non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How can I get the username from an application?

Hi everybody,

Here the environment is application in 3 layers.
So the user in his client machine, access the application in an application server that sends the request to my sql server database server.
My problem is: when I run sp_who from Query Analyzer, I see the host name that is pre-defined inside code application. But, actually, I need to know the user who is requesting the transactio so I can know right the person who is slowing my database performance...
Do you know, any function, procedure or anything else that I can use in the application code in order to provide the user or the host name from the client machine???
I hope some answers...

Thank you all,Look up NILADIC functions in Books Online.|||host_name()|||Hi all,

I have this doubt: may I apply SP3a in my SQL Server production database while it is operational?
I mean, may I do this version upgrade while the databases are online and users continue accessing the databases normally?
My actual version is 8.00.194 (no SP applied), and I am planning to apply SP3a.

Thank you in advance,|||No, when applying sp3a to a server that is currently at RTM (what you load from the CD) will require a reboot of Windows. There will be a brief service interruption while the machine reboots.

-PatP|||host_name()Will host_name() help in a three tier environment? I would expect it to return the name of the middleware server.

-PatP|||So this function host_name() doesn't help me, because I need to see the client host name during sp_who...
Does someone can explain a solution for this?

Thanks,|||The easiest way is to have your middleware set the hostname (http://support.microsoft.com/default.aspx?scid=kb;en-us;140301) equal to however it identifies the caller (user name, account number, etc). There are other ways to do this, but they get ugly very quickly and each solution is VERY specific to the installation/application involved.

-PatP