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

 

[CIMTEET]

The query is a full pl/sql file.

 

[SantaMufasa]

Greg,

Why not just use a standard table? That way, you can use the data as long as you want, then drop it when you are satisfied that you no longer need it. (The CPU time to drop the table is negligible.)

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

 

[CIMTEET]

Dave,

Will I run into the same privilege issues? Are you referring to creating and dropping on hte fly? What would be great is if I could invoke sqlldr from my pl/sql program. Then it would be a cinch.

My dba would have to go through all this process (internal to the company) to do create a table. Something she is unwilling to do, she is swamped with other stuff. The temp tables were created for this usage.


Greg

 

[SantaMufasa]

Greg,

To help me better assist you in resolving your issue(s), could you please run the following code, then post the results back here:

col username format a20
col privilege format a21
select username, privilege from user_sys_privs;
col granted_role format a12
select granted_role from user_role_privs;

The results will tell us what latitude you have to solve this issue.

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

 

[CIMTEET]

session1 create view
session1 create table
session1 Alter Session
session1 Create session
session1 create sequence
session1 create procedure
session1 create any directory
session1 global query rewrite
session1 unlimited tablespace


Granted_Role

connect
ctxapp
execute_cata
log_role

exp_full_dat
abase

imp_full_dat
abase

Granted_Role
javadebudpri
v

javasyspriv
javauserpriv
resource
select_catal
og_role

 

[SantaMufasa]

Greg,

Don't disclose any of this to your DBA <grin>, but you have virtually DBA privileges: "imp_full_database" and "exp_full_database" have a lion's share of DBA privileges. In addition, since you have "create table" privileges, you can certainly CREATE TABLEs. You also have "create any directory" privileges, which means that you can create the external tables (from flat-files) that we discussed earlier.

So, bottom line, you currently have the privileges to do everything that we have talked about in this, and your other, threads, without getting your "sadly overworked" DBA involved.

Let us know what you end up doing.

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

 

[CIMTEET]

I was creating tables, creating directories, but when i went to run a cursor against it, thats when the privilege error occured. We (me and you) did a little discovery and found out it was an oracle thing. thread759-1351139

 

[SantaMufasa]

If you successfully created directories and created external tables from flat files in those directories, but you had trouble with a cursor accessing those objects, then we should revisit your cursor-access issues. I'm not aware of any issues accessing successfully created tables and directories (given the proper inside-Oracle GRANTs).

In any case, we know that you have the privileges to create standard tables, which was the central issue to this thread, right?

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

 

[CIMTEET]

I was told today that she would fight creating of tables with every fiber of her being...thats tough considering I am contracting to this company. I gotta think, I could create a table separatley I suppose, in theory, but this software will be peer reviewed before implimentation in may.

 

[SantaMufasa]

Greg,

What is your DBA's aversion to creating another table or two? Oracle databases exist to store tables to solve business problems. If someone prevents the creation of tables necessary to solve business problems, then it seems to me that "The Inhibitor" is preventing the solution of business problems, true or false?

My analysis for the creation of a data object is:

1) What are the risks of creating the object?
2) How can we manage those risks?
3) What are the business risks of not creating the object?
4) What are the explicit and implicit costs of creating the object?
5) What are the explicit and implicit returns/benefits/competitive advantages of creating the object?
6) Do the net benefits outweight the costs of the above items?

If the answer is "yes", then the decision is clear.

Has your DBA (or someone higher up, with a clearer vision of business costs/benefits) done the above analysis?

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

 

[CIMTEET]

I might just do the table creation. It would be at my own risk though.

The main question is there a way to use sqlldr and sqlplus together without losing the session.

 

[CIMTEET]

That is sound logic. I am not quite what types of sharks lurk in these waters. I haven't in my 5 months here ever opposed someone. Anyways, done fo rthe evening. Thanks for you advice!!

Greg

 

[SantaMufasa]

Greg,

Typically, SQL*Plus cannot call SQL*Loader or vice versa; they typically run in two different sessions. Under such circumstances, the two are independent.

If you want SQL*Loader results to live long enough for a SQL*Plus session to access them, then you should load into a standard table -- not one that disappears at the end of a session.

The only way to cause SQL*Loader to become part of a SQL*Plus session is via the "external table" stategy of which we spoke earlier.



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

 

[CIMTEET]

Dude!

I got it to work on the database upstairs! I started looking at the chmod function and then decided to put my flat file in the home/oracle/tmp directory. This dir is owned by oracle and group is ointall I think. OK, now my question is why does it work here and not in my home directory? If oracle is the user trying to read my text file (while script is running) I opened all permissions to the file and folder. The location of the unusable spot is home/marting/test. Is it an user group thing?? Or am I back to changing the init.ora file? (This was the thing that kept me from using utl_file function suite, and currently home/oracle/tmp is listed as an utl_file_dir directory).

I was thinking that is I check file/dir attributes I would find a difference in the folders. Directory is created regardless of where i located teh flat file. So what is the directory actually doing if I still don't have access to it in some areas.

Greg

 

[CIMTEET]

Look what I just found

<Quote>
Directories must be created if external tables are used. Created directories are shown in either dba_directories or all_directories. There is no user_directories.

http://www.adp-gmbh.ch/ora/sql/create_directory.html

Does that say what I think it says?

Greg

 

[SantaMufasa]

Greg, Remember SantaMufasa's First Law of Expert Opinions:

One test is worth 100 expert opinions.

So, I suggest you just try it (regardless of what you think it, I, or Oracle, says).

Be sure to let us know your findings.

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

 

[CIMTEET]

Dave,

Thank you for all the time you have spent coaching me in this development phase. You make me look perty smart at work.

I heard that setting the UTL_FILE_DIR temporarily on the fly from pl/sql is possible. I found some syntax on how to do it, but I wanted to bounce it off you to see what you thought.

Declare
Begin
execute immediate ('alter system set utl_file_dir=''<file location>'' scope = spfile');
end;

What do you think? Anything stand out that concerns you? This will change it for the system. My question is will it undo utl_file_dir and set it to only this one directory, or will it add it to the others?

Greg

Greg

 

[SantaMufasa]

Greg,

Sorry I didn't see this sooner. Don't try that code yet...I have some other code that I'll test and post as soon as I've confirmed whether or not it works.

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

 

[CIMTEET]

OK, i have to go pretty soon for the evening but I'll touch base tomorrow.

Ideally, this should change utl_file_dir temporarily and change back when the session is done.

thanks

Greg