SQL Loader to a temp table 1

[CIMTEET]

I have a temp table where I am trying to dump text from a flat file so i can query it out and work with it. When sqlldr is ran it finishes its session and then clear the table by default. I wanted to run a query afterwards but couldn't. Is there anyway to ::

1.Run sqlldr to dump data into a temp table
2.Then query on the data

Greg

 

[SantaMufasa]

Greg,

(I hope you haven't left for the weekend yet.)

There is a better way than your changing the value of "utl_file_dir", above.

Notice that I currently have no value for utl_file_dir:

Parameter    Value
------------ -----------
utl_file_dir

Yet, I can write to, then read, a flat file using the following code (which I simply cut and pasted from the link that you showed me earlier):

create or replace directory dir_temp as 'c:\temp';

Directory created.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;
/

PL/SQL procedure successfully completed.

SQL> get c:\temp\something.txt
line one: some text
line two: more text

Woo-hoo!...and voilà! You have the full flat-file access that you had hoped for without impacting the parameter file or the instance by trying to set "utl_file_dir".

Let us know if this makes your day.


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

 

[CIMTEET]

I like it. When I tried to put the get into a var oracle cussed at me. Is that possible to do?

 

[SantaMufasa]

CIM,

I'm not sure what you are asking...My "get" was simply to show (from SQL*Plus) that the contents of the file were what we wanted.

Let us know what you are wanting to do.

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

 

[CIMTEET]

Oh, I see, you were showing me that I didn't need utl_file_dir. Thank you. I am currently experimenting with get_nextline procedure. I'll let you know how I fare. Thanks again, I wasn't going to try utl_file because of that.

Greg