Home » SQL & PL/SQL » SQL & PL/SQL » SQL Loader / Temp table
SQL Loader / Temp table [message #238166] Wed, 16 May 2007 22:19 Go to next message
ataufique
Messages: 79
Registered: November 2006
Member
Is it possible to load data using sqlloader to Oracle Global temporary table...If yes..then how...?
Re: SQL Loader / Temp table [message #238177 is a reply to message #238166] Thu, 17 May 2007 00:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
How to get a quick answer to your question: TIPS AND TRICKS
Quote:
Before you decide to open a new topic, you should ask yourself:
1. First of all: Did I try myself? Or am I just hoping that someone else is willing to do my work?

So what happened, when you tried?
I suppose it could be possible, however do not find any use from this, as the inserted data would be visible only to the loading (sqlldr) session.
Re: SQL Loader / Temp table [message #238210 is a reply to message #238177] Thu, 17 May 2007 02:06 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Dear SIR,

Please read the question again "Is it possible".
so if u know the answer (YES / NO ?) then only reply
else dont waste ur time pretending to help others.

FYI...i tried , but it gave error that the table is GTT, But when i changed this to permanent table the data gets loaded.

Being a senior member how can u use words like "I suppose it could be possible " ??

I just want to know DOES ORACLE SUPPORTS SQLLOADER WITH GTT ?

Re: SQL Loader / Temp table [message #238232 is a reply to message #238210] Thu, 17 May 2007 02:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What @flyboy is getting at is: have you really thought this through?

What do you think Oracle means by "temporary"? Even if SQL*Loader did permit loads into GTTs, what do you think would happen to your data as soon as the SQL*Loader session closed?

@flyboy's point is that there is no point in trying because one of two things is going to happen:
- It will throw an error (this seens to be the case)
- It does load, but then it purges everything you loaded as soon as the session closes because its a Global TEMPORARY table.

Either way, you're left with an empty temporary table, and the data is still not "in" your database.

Suggest you do a search on Externally Organized Tables in the doco. If you want to "stage" your data in a non-permanent structure for the purpose of transforming it into a permanent structure, then it may be more suited to your purpose.

Ross Leishman
Re: SQL Loader / Temp table [message #238235 is a reply to message #238210] Thu, 17 May 2007 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We can use word like 'I suppose it could be possible' because we're talking about something totally pointless that we lack the enthusiasm to try, because even if it did work, it would still be pointless.

Consider: A GTT is defined so that data in it will either be purged when a commit/rollback is issued in your session, or when you session ends.
With SQL*Loader, it will commit after a number of rows defined by the ROWS parameter, and will end it's session after loading the data.
So, no matter how you define the GTT, even if SQL*Loader will load data in to it, after SQL*Loader finished the data will be gone.
Thus the excercise is futile.

[Drat. Too slow]

[Updated on: Thu, 17 May 2007 02:58]

Report message to a moderator

Re: SQL Loader / Temp table [message #238258 is a reply to message #238166] Thu, 17 May 2007 04:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Being a senior member how can u use words like "I suppose it could be possible "

Maybe I should use "Even if it would be possible ...". I hope the point is clear although.

On the other hand, when asking a question how can u hide all steps you taken and reveal just some blurry ideas/conclusions?
Re: SQL Loader / Temp table [message #239025 is a reply to message #238210] Mon, 21 May 2007 05:17 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ataufique

Being a senior member how can u use words like "I suppose it could be possible " ??



I'll try to keep an eye on you and wait for another ~30 posts you make on this Forum. Then you'll become a SENIOR MEMBER too, and I expect you won't have such questions any more. All Oracle secrets will be - all of the sudden - known to you and you'll be able to answer any question just like that (:imagine_me_snipping_my_fingers:)

Let me paraphrase you:
LF
Being a member, how can you be so impolite when communicating with other OraFAQ Forum members?
Re: SQL Loader / Temp table [message #239032 is a reply to message #239025] Mon, 21 May 2007 05:35 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Dear,

Its a Discussion Forum.Not a Geini Lamp you Rub and your wish Completed in a Ziffy.Please be Polite and if Possible Say Sorry to our Seniors who are always here to help us Out from our Hard Times.(I am not making a Issue I just thought i should be)

Javed Khan
Re: SQL Loader / Temp table [message #242500 is a reply to message #239032] Sat, 02 June 2007 23:17 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Dear all

Even after all this, The question remains unanswered...

Does Oracle SQL Loader supports Global temp table...

Best Regards.
Re: SQL Loader / Temp table [message #242503 is a reply to message #238166] Sat, 02 June 2007 23:44 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Does Oracle SQL Loader supports Global temp table
YES/NO/MAYBE, SQL*Loader can load data into a Global Temporary Table.

Since NO other session can ever see this data what difference does it make what the answer is?

NOBODY here owes you an answer.

What is/has prevented YOU from conducting your own benchmark tests and answering this question for yourself?

You're On Your Own (YOYO)!

[Updated on: Sun, 03 June 2007 00:29] by Moderator

Report message to a moderator

Re: SQL Loader / Temp table [message #242524 is a reply to message #242500] Sun, 03 June 2007 10:09 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
ataufique wrote on Sun, 03 June 2007 05:17
Dear all

Even after all this, The question remains unanswered...

Does Oracle SQL Loader supports Global temp table...

Best Regards.

The question has been fully answered above. What part of it remains unanswered?

As others have explained, the only possible results of using SQL*Loader to populate a global temporary table would be either an empty table or an error message. I just tried and it's the latter.

As I'm sure you saw when you looked up that error code, that means

SQL*Loader-00280: table string is a temporary table
Cause: The sqlldr utility does not load temporary tables. Note that if sqlldr did allow loading of temporary tables, the data would disappear after the load completed.

Action: Load the data into a non-temporary table.

So no, SQL*Loader does not support loads into global temporary tables, because if it did, it would do nothing.

Why would you want to load into a global temporary table?

[Updated on: Sun, 03 June 2007 10:10]

Report message to a moderator

Re: SQL Loader / Temp table [message #243897 is a reply to message #242524] Sun, 10 June 2007 22:40 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Thanks William and others..
When i tried to load data to GTT through sql loader / external table, i got the error that the destination table is GTT, So to check if i am missing some syntax, i posted the topic.

The purpose is that i receive flat files containing millions
of records and around 81 columns, This i load to intermediate
table and after doing the validation the required columns(42) is loaded to permanent table.
I am trying to process around 10 flat files at a time, i tried
SQL loader and External tables..
Since GTT is not working , at present i am using permanent table as intermediate,table is created based on filename and is droped once the data is loaded to permanent..

Thanks again to all.
Re: SQL Loader / Temp table [message #244025 is a reply to message #243897] Mon, 11 June 2007 06:17 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why would you need to create and drop a table every time you get a new file?
What idea did you have with the GTT that you cannot use with an ordinary table?
Previous Topic: how can we get multiple SUM() from single table
Next Topic: how to round the datetime field to the nearest hour?
Goto Forum:
  


Current Time: Sat Dec 03 09:52:19 CST 2016

Total time taken to generate the page: 0.12784 seconds