Home » SQL & PL/SQL » SQL & PL/SQL » load script for loading data from oracle table to excel
load script for loading data from oracle table to excel [message #667921] Wed, 24 January 2018 07:29 Go to next message
sshree12
Messages: 20
Registered: December 2017
Junior Member
Hi Expert,

i wanted to load data from oracle to excel sheet every day. can i get load script to do that?
my table:
CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
ORGANIZATION INDEX;
INSERT INTO countries VALUES
( 'IT'
, 'Italy'
, 1
);
INSERT INTO countries VALUES
( 'JP'
, 'Japan'
, 3
);
INSERT INTO countries VALUES
( 'US'
, 'United States of America'
, 2
);
INSERT INTO countries VALUES
( 'CA'
, 'Canada'
, 2
);
INSERT INTO countries VALUES
( 'CN'
, 'China'
, 3
);
INSERT INTO countries VALUES
( 'IN'
, 'India'
, 3
);
INSERT INTO countries VALUES
( 'AU'
, 'Australia'
, 3
);
INSERT INTO countries VALUES
( 'ZW'
, 'Zimbabwe'
, 4
);
INSERT INTO countries VALUES
( 'SG'
, 'Singapore'
, 3
);
INSERT INTO countries VALUES
( 'UK'
, 'United Kingdom'
, 1
);
INSERT INTO countries VALUES
( 'FR'
, 'France'
, 1
);
INSERT INTO countries VALUES
( 'DE'
, 'Germany'
, 1
);
INSERT INTO countries VALUES
( 'ZM'
, 'Zambia'
, 4
);
INSERT INTO countries VALUES
( 'EG'
, 'Egypt'
, 4
);
INSERT INTO countries VALUES
( 'BR'
, 'Brazil'
, 2
);
INSERT INTO countries VALUES
( 'CH'
, 'Switzerland'
, 1
);
INSERT INTO countries VALUES
( 'NL'
, 'Netherlands'
, 1
);
INSERT INTO countries VALUES
( 'MX'
, 'Mexico'
, 2
);
INSERT INTO countries VALUES
( 'KW'
, 'Kuwait'
, 4
);
INSERT INTO countries VALUES
( 'IL'
, 'Israel'
, 4
);
INSERT INTO countries VALUES
( 'DK'
, 'Denmark'
, 1
);
INSERT INTO countries VALUES
( 'HK'
, 'HongKong'
, 3
);
INSERT INTO countries VALUES
( 'NG'
, 'Nigeria'
, 4
);

thanks
shree
Re: load script for loading data from oracle table to excel [message #667922 is a reply to message #667921] Wed, 24 January 2018 08:06 Go to previous messageGo to next message
joy_division
Messages: 4909
Registered: February 2005
Location: East Coast USA
Senior Member
You really didn't give a good description, so I'll give it a shot. It's crude, but it will work (in most cases).

set colsep ,
Spool to a file ending in .csv
select * from your table
spool off
Double clicking the file will open it in excel.

You should user varchar2 and not char for your datatypes.

[Updated on: Wed, 24 January 2018 12:09]

Report message to a moderator

Re: load script for loading data from oracle table to excel [message #667923 is a reply to message #667921] Wed, 24 January 2018 08:07 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
Oracle does NOT know how to write directly to Excel sheet.
Oracle can write text file either in CSV or XML format that Excel knows how to import

http://lmgtfy.com/?q=oracle+write+excel+file+example
Re: load script for loading data from oracle table to excel [message #667947 is a reply to message #667923] Thu, 25 January 2018 11:50 Go to previous message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
However their are free packages that will generate native xlsx files. See the following link

https://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/

[Updated on: Thu, 25 January 2018 11:51]

Report message to a moderator

Previous Topic: Alternate to DECODE function to be used in elsif statment in PLSQL procedure
Next Topic: Is there a way to do a rollup on only one dimension
Goto Forum:
  


Current Time: Sat Nov 17 15:26:24 CST 2018