Sql * Loader 2

[marieannie]

Hi all, I'm studying Oracle and I know very little how it works. I been reading previous post about importing and exporting tables. I have oracle 10g database - the oracle CDs comes with the course text book.
My question is that I don't have SQL*Loader - I don't know if comes with the CDs or not. But I don't see it installed...

Can I still import/export with SQL*Plus?? if so, can anyone help me what instruction should I use..
(I only try to import / export tables that I am using for my class - so doesn't have much information)

Thank you
Regards,
MA

 

[Welshbird]

I use SQLLDR from a command prompt when I use it, sp you should looking for SQLLDR.exe, which I believe is always installed with Oracle.

You need first to create a table with the correct columns, then create an SQLLDR control file and then call this from the command prompt.

Here are some examples to get you going:

create table MyTable
(
ID number,
DESC_TXT varchar2(15),
DT_loaded date
)

and then to load a CSV into this create a control file called Mine.ctl that looks like:

 load data
          INFILE 'C:\SQLLDR\mine.csv' 
          REPLACE
          into table MyTable
          FIELDS TERMINATED BY ',' 
          OPTIONALLY  ENCLOSED BY '"' 
          TRAILING NULLCOLS
         (ID,
	 DESC_TXT,
	 DT_loaded sysdate 
         )

This then loads data from mine.csv into the table.

You call all of these from a command prompt, such as:

SQLLDR username/password@tns control = mine.ctl

Hope this helps get you started at least!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[KenCunningham]

Be aware that imp/exp have little or nothing to do with sqlldr.

I want to be good, is that not enough?

 

[marieannie]

Thank you for the information and the examples...

I am going to check this later in the afternoon.

Regards,
MA

 

[Welshbird]

Marieannie - let me know if you need more assistance.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[marieannie]

Thank you Willif. This works great.
any chance you can help me to export the table content to a CSV?


Another question: Is it able to import all my database from microsoft access to oracle??

Regards,
MA

 

[SantaMufasa]

MarieAnnie,

Since Fee is in the Arms of Morpheus presently (circa 4:00 a.m. her time [GMT]), I'll jump in here in case time is of the essence for you.

First, you should be able to access Access tables directly from Oracle in a variety of ways, most notably via ODBC (Open-DataBase Connectivity). Another method would be to have Access output .csv (Comma-Separated Values) files of the tables you want to have in Oracle, then you can access the .csv files directly by Oracle as EXTERNAL tables.

Second, Oracle was WAAAAAAAY silly to name their "exp" and "imp" utilities by those names since, to non-Oracle people, the terms "export" and "import" imply the creation and access of (.csv-style) flat files for use between Oracle and non-Oracle environment...And nothing could be further from reality.

"exp" and "imp" should have been named "dump" and "restore" respectively since Oracle is the only environment that can create and read back in "exp" and "imp" files.

Oracle has no built-in utility (that I know of) that creates .csv files. Therefore, it is up to us, as users, to create our own code for .csv files. I have a generic utility script that I wrote to create variable-length, delimited fields from rows in Oracle tables. If you want that script, let me know and I'll post it here. (I'm just headed out presently, however, to do three hours of late-night volunteer work, so it may be morning before I can post the script.)

If you want to create a .csv script yourself, just do a SELECT that concatenates all of your table's columns, spliced together with commas and (if necessary) double quotes, to separate each column.

Let us know,

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

 

[marieannie]

Thank you SantaMufasa for the prompt response...
and for the information.
I already made the example Fee gave me..
I was able to load a csv file into my oracle table

Do you have a script in which I can do the viceversa process?? the rows for an Oracle table to a CSV file?


Thank you
Regards,
MA

 

[SantaMufasa]

I just returned from the volunteer assignment, and before I go to bed, I wanted you to have my generic script that creates .csv flat files.

Section 1 -- "GENASCII.sql" source code:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept w prompt "Enter the Oracle username that owns the source table: "
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"'''
  from all_tab_columns
 where owner = upper('&w')
   and table_name = upper('&x');
prompt from &w..&x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Section 2 -- Contents of a sample table, S_REGION:

SQL> select * from s_region;

        ID NAME
---------- --------------------
         1 North America
         2 South America
         3 Africa / Middle East
         4 Asia
         5 Europe
         6 Australia

6 rows selected.
************************************************************************

Section 3 -- Sample invocation of "GENASCII.sql" that creates a .csv file of the "S_REGION" table:

SQL> @genascii
Enter the Oracle username that owns the source table: test
Enter the table to ASCII: s_region
Enter the flat file to write: region.txt

Following output is generated script that writes text output from table "s_region"

set echo off
set feedback off
set heading off
set pagesize 0
spool region.txt
Select
'"'||ID|| '"'
||',"'||NAME|| '"'
from test.s_region
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "region.txt"

"1","North America"
"2","South America"
"3","Africa / Middle East"
"4","Asia"
"5","Europe"
"6","Australia"

Output file = "region.txt"

Let us know if this takes care of your need.

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

 

[Welshbird]

Morning!

(Sorry - was asleep as Santa said.)

I always find exporting from SQL*Plus a bit of a pain to be honest. I use a different method to export to a CSV which might be a bit of a cheat but works really well for me.

I use SQLTools. Its a tiny piece of freeware that allows you to just run queries on Oracle, but it also has an 'export to CSV' button. So, you run your query and then push the button and your results appear in a CSV file opened in your defaul app (usually excel).

If it helps you can find the software here:

http://www.sqltools.net/

(Dave - I know its a bit of a cheat, but it's awfully quick to use )

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[marieannie]

Thank you both for the information.
I am trying to use the utility but isn't working as I expected.. do not generate anything.. I don't know if it is because I have office 2007??

I tried plain text but doesn't work either...

 

[marieannie]

It's already working... I just restarted the PC...
Thank you

 

[Welshbird]

Are you using the SQLTools utility?

If so, I believe it will just export whatever appears in the bottom window, so can you see data in there before you push the button?

If so, then you need to check what is your default application to open CSV files. It might be an office 2007 issue, but I don't have that so I don't know if that is what causes you the problem.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[marieannie]

Thank you Fee. The utility is working great after I restarted the computer.

I have another question I'm trying to create my tables using the Editor (I don't know if this is possible?)
is giving me the following error:

(
*
Error at line 2:
Ora-00922: missing or invalid option

Here is the create instruction:
create table EMPLOYEES
(
Employee_ID number,
Employee_Name Varchar2(30),
Employee_LastName Varchar2(30),
Employee_Address Varchar2(80)
)

Thanks again for your help
MA

 

[Welshbird]

Hmm. I've just copied and pasted that into a SQLPlus window and ran it with no problems at all.

So I don't see what the problem is with that to be honest.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[marieannie]

Hello, I don't know what was it.. I just re-type
the instruction....

Thank you for all your help..
Regars,
MA

 

[Welshbird]

I'm guessing it worked this time then?

Hope so.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[marieannie]

Yes it worked.

Regards
MA