Home » SQL & PL/SQL » SQL & PL/SQL » External Tables
External Tables [message #211394] Thu, 28 December 2006 06:46 Go to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hi....

can someone please explain me the concept of external tables....and how to do it practically...??

Thanks
Re: External Tables [message #211395 is a reply to message #211394] Thu, 28 December 2006 06:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This should help explain it (External Tables Concepts).
Re: External Tables [message #211396 is a reply to message #211394] Thu, 28 December 2006 06:49 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

go through below link
http://www.orafaq.com/forum/?SQ=3f70f405fd1a1612db40794c64dad946&t=search&srch=external+Table&btn_submit=Search&field =all&forum_limiter=&search_logic=AND&sort_order=DESC&author=



regards
Taj
Re: External Tables [message #211405 is a reply to message #211394] Thu, 28 December 2006 07:49 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

@ ebrian

tough to understand...

@taj

did'nt find a specific answer on external tables from those links....
Re: External Tables [message #211409 is a reply to message #211405] Thu, 28 December 2006 08:06 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

http://www.adp-gmbh.ch/ora/misc/ext_table.html

http://www.oracle.com/technology/pub/articles/saternos_tables.html


http://www.akadia.com/services/ora_etl.html



hope this helps
Taj
Re: External Tables [message #211411 is a reply to message #211409] Thu, 28 December 2006 08:39 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

wow.!!

ya the first link helped a lot...thanks...
Re: External Tables [message #211535 is a reply to message #211394] Fri, 29 December 2006 04:44 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

ok..i have a confusion now.....where do i store this flat file???
Re: External Tables [message #211543 is a reply to message #211535] Fri, 29 December 2006 05:29 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But of course you are confused!

This is what happens when you read documentation excerpts instead of trying to understand what it really is about.

Return to ebrian's post again and stop excusing yourself because it is too tough. If it really is too tough, perhaps you shouldn't use external tables at all (until you grow up).
Re: External Tables [message #211575 is a reply to message #211543] Fri, 29 December 2006 10:29 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

thanks for that littlefoot.....lolz

i read all of it...and now i understood the whole concept....
Re: External Tables [message #211581 is a reply to message #211394] Fri, 29 December 2006 11:49 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hi...

i did'nt know i should create a new thread out for one single question...so i am rather continuing in this thread only.....

the thing is......i am doing this excercise from chapter 20th of introduction to oracle 9i : sql from oracle university.....i came across this question which says...

"run the cre_sal_history.sql script in the lab folder to create the sal_history table"

i did'nt understand where is this lab folder located..??

i tried doing

@ \labs\cre_sal_history.sql

but it gave me an error "SP2-0310: unable to open file "\labs\cre_sal_history.sql"

PS: i am logged in as sysdba
Re: External Tables [message #211585 is a reply to message #211581] Fri, 29 December 2006 13:23 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, you should have access to "cre_sal_history.sql". Do you have it? As an ordinary text file, you should be able to open it in any text editor. Can you do that? If so, how does it look like? I *guess* it contains CREATE TABLE sal_history ... statement.

Basically, it can be run anywhere, current directory shouldn't matter at all. But, if you use SQL*Plus from the command prompt and follow instructions from the book you mentioned, I believe there must be a line or two which says how to create working environment. This would include operating system command as "CREATE DIRECTORY lab" which would then contain all files necessary for your advance through the book.

Once you have it, there must be a source of all those .sql files (including cre_sal_history.sql). It is, I presume, download section of some web site. So, when you find this file (or type it on your own into a text editor and save into previously created "lab" directory), go to "lab", start SQL*Plus session and execute this file:

SQL> @cre_sal_history.sql

It would also help if you start reading the book from chapter 1 (instead of chapter 20). Perhaps you'd find answers to your questions in there.

P.S. I also believe that SYSDBA isn't the one who is supposed to do your exercises. If it were me, I'd create another user (something like Scott) and work in its schema. Again, all this might be found in chapter 1 of the mentioned book. Or may be not.

[Updated on: Fri, 29 December 2006 13:25]

Report message to a moderator

Re: External Tables [message #211586 is a reply to message #211394] Fri, 29 December 2006 13:32 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

i don't have cre_sal_history.sql .... i have searched for it...

and dude...i have read the whole book already...
regarding the login....i was trying to do it from sysdba as scott did'nt have all the privilages....
Re: External Tables [message #211591 is a reply to message #211586] Fri, 29 December 2006 13:46 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but I don't have it either.

What "all" privileges are you talking about? Why didn't you GRANT those privileges to Scott? Working the way you are is excellent if you enjoy recreating the database.
Re: External Tables [message #211595 is a reply to message #211586] Fri, 29 December 2006 14:54 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
cooler2005 wrote on Fri, 29 December 2006 14:32
i don't have cre_sal_history.sql .... i have searched for it...



It should have accompanied the book. Did you take a class? Did the professor give you the files? Were they on a network filesystem you were supposed to get them from? Does the book have the source code?
Re: External Tables [message #211605 is a reply to message #211595] Fri, 29 December 2006 17:15 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
My thought is you may have bought this Oracle University book on E-bay or some other source.

As joy_division indicated, the lab directory that the book mentions is part of the actual Instructor Led Class the the book was intended to go along with. You won't find this file on your local box or your network, unless you somehow got a copy of the lab files from Oracle University.
Re: External Tables [message #211616 is a reply to message #211394] Sat, 30 December 2006 00:11 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

yeah...i bought this book from e-bay...

i'll try and look for the sql file on oracle's website....
Re: External Tables [message #211666 is a reply to message #211394] Sat, 30 December 2006 23:42 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

i did'nt find it anywhere....can some one else help me in finding the script file....
Re: External Tables [message #211675 is a reply to message #211666] Sun, 31 December 2006 03:17 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to create one by yourself; viewing the code in chapter 20 *might* give you an idea of what this table consists of. You probably won't succeed from the first attempt, but in a few iterations you might get your table.
Re: External Tables [message #211682 is a reply to message #211675] Sun, 31 December 2006 06:37 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

there's no description for the script file nor any of the table in that chapter...
Re: External Tables [message #211690 is a reply to message #211682] Mon, 01 January 2007 01:53 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I didn't mean "CREATE TABLE ..." code.

You've said that, reading through Chapter 20, you came to the point which said "run @cre_sal_history.sql". You don't have it.

Now, if you read further, is there something like "... to see how to compute SUM of all salaries, do this:
SELECT SUM(sal) FROM sal_history 
WHERE emp_id = 123
AND dept_id = 10
AND payment_date = TO_DATE('31.12.2006', 'dd.mm.yyyy');"

Now, it would reveal several columns:
CREATE TABLE sal_history
(sal          NUMBER,
 emp_id       NUMBER,
 dept_id      NUMBER,
 payment_date DATE
);

So, is there anything like this? If so, lucky you. Next pages might reveal other columns as well. (Of course, the table would be empty - you'd have to create your own sample data).

But, if it looks like "... to see how to compute SUM of all salaries, see 'find_sum_sal.sql' and run it at the SQL*Plus prompt", you're completely out of luck.
Re: External Tables [message #211691 is a reply to message #211690] Mon, 01 January 2007 05:05 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

no no....i came across this script file in the excercise part of the chapter....i've read the whole chapter again...adn there's nothing which resembles this script file or any table in that script file....
Re: External Tables [message #211707 is a reply to message #211691] Mon, 01 January 2007 09:43 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but I don't know how to help you further. Try to find those scripts on eBay or contact the person who sold you the book.
Re: External Tables [message #211847 is a reply to message #211394] Tue, 02 January 2007 11:53 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hmm

is there anyone who has studied from this book and can help me..??
Re: External Tables [message #211899 is a reply to message #211847] Wed, 03 January 2007 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have been asking for this file for the last 4 days now. I googled for it and found at least 2 occurrences!
Being a developer is all about copying existing stuff and ask others how to do things. Being a good developer is all about being creative with the resources you have (and asking people for second opinions and alternatives).
Re: External Tables [message #212034 is a reply to message #211899] Wed, 03 January 2007 10:06 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Can't help being curious...

How did you get as far as chapter 20 if you don't have the scripts that come with the course??
Re: External Tables [message #212089 is a reply to message #211899] Wed, 03 January 2007 14:22 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

if u have found the scripts ... plz post em....coz i did'nt find the ones i needed...on google...maybe u've find the correct ones....
Re: External Tables [message #212090 is a reply to message #212034] Wed, 03 January 2007 14:25 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

skooman wrote on Wed, 03 January 2007 21:36
Can't help being curious...

How did you get as far as chapter 20 if you don't have the scripts that come with the course??


dude...till chapter 19.....i used the tables which are by default in oracle databse....plus made many more whose desc were given and the chapter needed those tables....
Re: External Tables [message #212091 is a reply to message #212089] Wed, 03 January 2007 14:27 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Actually I don't like these kind of things very much. But hey, if you promise not to tell anyone (and it's out on the internet already anyway...) Very Happy

DROP TABLE SAL_HISTORY;
CREATE table SAL_HISTORY
(EMPLOYEE_ID NUMBER(6),
HIRE_DATE DATE,
SALARY NUMBER(8,2));


Source:
http://www.fh-nuernberg.de/en/seitenbaum/home/it_center/labore/multimedia_und_datenbank_labor/lehrveranstaltungen/vorlesungen/page.ht ml
Re: External Tables [message #212154 is a reply to message #212091] Thu, 04 January 2007 00:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You see? It reaaally isn't that hard to find!
(no offence meant Sabine Wink)
Previous Topic: Union error
Next Topic: for loop
Goto Forum:
  


Current Time: Sat Dec 03 10:17:23 CST 2016

Total time taken to generate the page: 0.07856 seconds