Read from a text file... 3

[CIMTEET]

I am trying to read text from a text file and need to use a different directory. In my reading I found how to do it using the init.ora file in the UNIX OS. Unfortunately my DBA is being rebellious and not allowing me to use the init.ora method because of having to bounce the dbase AND the fact that this would have to (in her eyes) be implemented world wide at all similar sites (that are closed to outside world). So my next thought would be how do I change this thing dynamically? In windows I found an article that describes this process but have not been able to find one for unix.

In the init.ora file there needs to be defined a file handle.

UTL_FILE_DIR = /greg/tmp

so in my program I can ...

DECLARE
config_file UTL_FILE.FILE_TYPE
BEGIN
config_file :=UTL_FILE.FOPEN ('/greg/tmp,'blah.txt', 'R')
......read operation......
END;

Is there any way at all in UNIX where you don't need to bounce the dbase by altering hte init.ora file??

 

[CIMTEET]

Is there any way at all I can dynamically change the directory I read from with violating the security of our database.

(did not finish the question at the end of my last post)

 

[CIMTEET]

Thats without violating security, not with...Maybe I should call it a day!

 

[SantaMufasa]

Following is the content of a flat text file created in Notepad. I named the file 'c:\dhunt\sqldba\ALM\Input\Cimteet.txt').
The file simulates flat files that you indicated that you want to process in Oracle.

This is random, flat-file text, on multiple lines,
in a file named 'c:\dhunt\sqldba\ALM\Input\Cimteet.txt').
I can cause Oracle to treat this flat file as a TABLE,
without any implication on the Oracle instance.

I can write logic to parse the lines and process them any
way I want.

I can also create the records in this flat file as having
columns that are fixed-length or delimited by commas,
tabs, or any other special character that I choose.

This posting shows that not only can you process one or more
flat files without having the "utl_file_dir" parameter set,
but you can also process the flat file(s) as one or more
Oracle tables!

This means that you can avoid using all of the syntactical
intricacies of Oracle's "utl_file" infrastructure and even
process flat files as Oracle tables, without even using PL/SQL.

The following code shows:

1) That my instance's "utl_file_dir" parameter is undefined,
2) How to specify an o/s path as a location for a flat file to process.
(This command requires DBA privileges, but you do not need to
bounce the database or change any init.ora parameters.)
3) How to create a flat-file "external" table
4) How to access the flat-file, external table as a normal Oracle table.

col p heading "Parameter" format a15
col v heading "Value" format a15
select name p, value v from v$parameter
 where name like 'utl%';

Parameter       Value
--------------- ---------------
utl_file_dir            <-- Notice there is no valid value for a directory

create or replace directory ALMInput as 'c:\dhunt\sqldba\ALM\Input'
/

Directory created.

create table cimteet (txt varchar2(1000))
organization external
(  type oracle_loader
   default directory ALMInput
   access parameters
   (records delimited by newline
    fields terminated by '^'
   )
location ('Cimteet.txt')
)
reject limit unlimited;

Table created.

desc cimteet

Name                    Null?    Type
----------------------- -------- --------------
TXT                              VARCHAR2(1000)

select * from cimteet;

TXT
---------------------------------------------------------
This is random, flat-file text, on multiple lines,
in a file named 'c:\dhunt\sqldba\ALM\Input\Cimteet.txt').
I can cause Oracle to treat this flat file as a TABLE,
without any implication on the Oracle instance.

I can write logic to parse the lines and process them any
way I want.

I can also create the records in this flat file as having
columns that are fixed-length or delimited by commas,
tabs, or any other special character that I choose.

11 rows selected.

Your DBA should be delighted to co-operate with the
"CREATE DIRECTORY..." command since it resolves your need without any negative implications on your, or any other, Oracle instance. (Pretty cool, huh!)

Let us know if this satisfactorily overcomes your challenge.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

BTW, in my post, above, my code included the line:

...
fields terminated by '^'...

The default field terminator for external tables is a comma. Since my sample, flat-file text included commas, I overrode the default by using a very obscure character as a delimiter ('^') that typically does not appear in any of my flat-file text.

I just thought I should explain that "quirk" in my code. If you have other questions about "external table" organization, please feel free to reply.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

That's awesome!

I have one question, what if the file coming in was text and wasn't created by the database? The usage here is i'm building a general query tool where someone form the outside would create the text file with the format:

msg_name, field_name, field_option1 field_option2
msg_name, field_name, field_option1
msg_name, field_name, na

Msg_name carries field_names which, when text, has certain options and when its a number would have an na or some character indicating it would be a numerical return. I have two dynamic cursors. One will pass msg_names and the other field_names. Right now I picture teh for loops referencing these cursors to be nested. Basically, find field names from this returned msg_name, and the field data with them.

 

[CIMTEET]

The tool we would be using would probably be openoffice. But wouldn't have to be. I know it would be created by word in a windows environment then transferred in

 

[CIMTEET]

And, does this database include itself with the current schema?

 

[SantaMufasa]

As I mentioned, above, I created the text file with Windows Notepad. You can use any software to create the flat file. You can either parse the incoming flat-file data strings within your code, or you can use commas, or any other delimiter, to designate where fields terminate, and, at that point, Oracle can "read" your flat file as a TABLE, placing the delimited columns of flat-file data into logical columns of the "external table".

So, if you have already built the logic to do the parsing you want, then I recommend that you build your flat-file external table with a single colunn. You then parse the single column of data with the logic that you originally intended for your "utl_file" access.

BTW, have you pitched this to your DBA yet? Did she, in turn, pitch a fit right back at you, or did she pat you on the back for finding such a non-invasive solution?

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Just saw your question

...does this database include itself with the current schema?

I'm sorry, but I don't understand the question. Can you please re-state it, perhaps with an example? (Sorry for my dimness...it's the end of a day.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

The problem with using Word^tm is all the control-character crap that MS includes at the front of their (obviously not-)flat files.

I recommend that whatever flat file you send into your application, that it is a dumbed-down, truly flat file.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

No, I haven't presented it to her yet. She is gone for the day. I'll need to do an example first to show her what I mean. She is a seeing is believing type. So data retrieval is written in sql then. cool! I love beating the system!! Thannks SantaMufasa.

 

[CIMTEET]

Some tables exist in a current schema that I make reference to in my sql statement. Others I declare in my sql program and are used only during program execution. I use them as a user defined type substitute. You don't actaully use sequal statements when searching them. Its more of a example(i).color and example(i).weight syntax.

If I have a person enter in 100 of these flat files and then run each one, will these tables remain created on our present database? I have never used the create table function before. And if I don't respond to this tonight, I have to go, but will be back to the forum in the morning.

Greg

 

[CIMTEET]

Oh, i think I see why you didn't answer. All this does is treat the text file as if it were a table...OOOHHHHH! I am not really creating a table but pointing to one that exists in a separate file...Santa, you earned more than 1 pink star on that...Also, how do you feel winning so many pink stars...I think I would feel funny about that...Kidding, thanks again.

 

[CIMTEET]

SANTAMUFASA,

You know where you explained using an obscure terminator for teh fields? What does it mean to terminate the fields. Oh, and I have been making great headway on my project so thank you.

Greg

 

[CIMTEET]

I have been declaring the field names during the create phase of the code which is cool. But the only thing that I don't think I can do is make my cursor dynamic in the FROM area. I would need this to COMPLETELY automate my general query. From what I saw I can only pass in vars to the where clause portion of a cursor.

Basically, I am trying to build a series of tools that would take care of most of the testers query needs. Currently they go into the a template and add there own message and field names then code is there to process the output in a general sense. So, I want to have this flat file come in, my code read it and process it without having to do any changes to the logic itself. Anyways, tomorrow I plan to try making hte FROM area dynamic, we'll see. Thanks again for your help.

Greg

 

[taupirho]

Dave, followed your instructions and it was fine up until I tried the select, see the following

desc cimteet

Name Null? Type
----------------------------------------- -------- ----------------------------
TXT VARCHAR2(1000)

select * from cimteet
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file CIMTEET_000255DD.log
OS error file specification syntax error
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


Any ideas?

 

[CIMTEET]

Taupirho,
Did you do a select in the same database session or did you Create the database in one program and run select in another.

I created the dbase first by actually running a .sql file with the create code, and then after execution I still had the SQL command prompt open and ran my select statements from there.

I struggled a little finding out where to put the creat table code when I wanted it to run together. It goes above the declare statement. You probably know this stuff. Both methods worked for me, so I wonder if its a major OS thing. Are you on unix or windows?

 

[SantaMufasa]

Taupirho,

The Oracle error messages that appear when encountering errors with "external tables" are, IMHO, absolutely, unacceptably obscure, esoteric, and virtually useless.

Having said that, I have found that that cryptic error you received usually results from Oracle's inability to actually locate the flat file that you specified. So,

1) confirm that your flat source file actually exists in the DIRECTORY path that you stated, and

2) that the file name is, character for character, identical to the name that you specified when you successfully executed the "CREATE TABLE..." command. (Remember that if you are using Unix, file names are completely case sensitive.)

If this troubleshooting tip does not resolve your issue, please reply with your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

Dave, my database is on a Compaq Alpha. I connect to it from my PC using Oracle client software (via sqlnet).

I created the file c:\cimteet.txt containing the text you posted originally.

This file definitely exists and contains what it should.

I then did a

create or replace directory ALMInput as 'c:\’

then


create table cimteet (txt varchar2(1000))
organization external
( type oracle_loader
default directory ALMInput
access parameters
(records delimited by newline
fields terminated by '^'
)
location ('Cimteet.txt')
)
reject limit unlimited

and finally the desc and select which resulted in the errors I posted