Home » SQL & PL/SQL » SQL & PL/SQL » writing qyery ouput on text file and saving at some specific location of local system (oracle 9i)
writing qyery ouput on text file and saving at some specific location of local system [message #387999] Mon, 23 February 2009 00:16 Go to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
dear all,
my requirement is whenever i will run below procedure on
session ,it has to write the data in text file and that text has
to be generated on my system (i.e my p.c) at specifice directory location


create or replace procedure emp_pr
AS
BEGIN
for r1 in (select * from emp);
loop
dbms_output.put_line(r1.empno||' '||r1.ename||' '||r1.esal||' '||r1.deptno);
END;
/


begin
emp_pr;
end;

please guide


regadrs
vivek
Re: writing qyery ouput on text file and saving at some specific location of local system [message #388001 is a reply to message #387999] Mon, 23 February 2009 00:18 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Congratulations. Proceed.
Re: writing qyery ouput on text file and saving at some specific location of local system [message #388008 is a reply to message #387999] Mon, 23 February 2009 00:28 Go to previous messageGo to next message
TJPokala
Messages: 17
Registered: March 2008
Junior Member
Hi Vivek,

Something like this:

HEADER
------
create or replace PACKAGE extraction_pkg
AS
-- GENERIC VARIABLE DECLARATIONS
-----------------------------------------------------------------
-- WILL BE WRITEN TO THE FILES IN THIS PATH.
-----------------------------------------------------------------
gv_exp_directory VARCHAR2 (20) := 'EXPORTDATA_DIR';

PROCEDURE extraction;

BODY
-----
create or replace PACKAGE BODY extraction_pkg
AS

--local variables
outfile UTL_FILE.file_type;

--open cursor
CURSOR analyzed_cursor1
IS
select * from emp


each_rec cursor1%ROWTYPE;
BEGIN
/* Opening output file name in 'Write' mode */
outfile := UTL_FILE.fopen (gv_exp_directory, gv_file_name, 'w', 32767);
Re: writing qyery ouput on text file and saving at some specific location of local system [message #388012 is a reply to message #387999] Mon, 23 February 2009 00:35 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If you want to store your whole work you have done, you can use SPOOL option

regards,
Delna
Previous Topic: Updating 'LONG' column
Next Topic: Using Weekly data updates to Update a Table by row growth and not column growth
Goto Forum:
  


Current Time: Wed Dec 07 03:10:02 CST 2016

Total time taken to generate the page: 0.11012 seconds