Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql io
There are at least two ways you could solve this. In Oracle8, you
could use BFILEs, a LOB datatype that is stored outside of the
database.
Or, you could use the UTL_FILE package for I/O to operating-system files on the server. As an example:
CREATE OR REPLACE PROCEDURE fooey
AS
fileid UTL_FILE.FILE_TYPE;
buffer VARCHAR2(200);
BEGIN
fileid := UTL_FILE.FOPEN( 'c:\temp', 'utl.txt', 'R');
LOOP
UTL_FILE.GET_LINE( fileid, buffer );
DBMS_OUTPUT.PUT_LINE( 'I read: ' || buffer );
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.FCLOSE( fileid );
END;
/
Then, if I have the following 2 lines in the file utl.txt:
This is the first line.
And this is the second line.
Then I could do the following in SQL*Plus:
SQL> set serveroutput on;
SQL> exec fooey;
I read: This is the first line.
I read: And this is the second line.
PL/SQL procedure successfully completed.
Once you get your data in the buffer, you can use PL/SQL to dissect it. You can find more information in the Server Application Developer's Guide.
(Don't forget, you need to specify the accessible directories for your server using the UTL_FILE_DIR parameter in your database initialization file)
On Fri, 10 Apr 1998 05:48:12 -0500, Martin Meadows <mmeadows_at_indy.net> wrote:
>I have to write a program to read an ascii file & load the data into a
>table. I would like to use pl/sql to do this but I don't see any way to
>open the file & read it with this language. Is there any way?
>
>Thanks,
>Martin Meadows
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com