Home » SQL & PL/SQL » SQL & PL/SQL » How to create a procedure to read data from database and export it into .csv format (Oracle 11g)
How to create a procedure to read data from database and export it into .csv format [message #572472] Wed, 12 December 2012 05:21 Go to next message
ravidwivedi2288
Messages: 8
Registered: December 2012
Location: Malaysia
Junior Member
How to create a procedure to read data from database and export it into .csv format without using utilities
Re: How to create a procedure to read data from database and export it into .csv format [message #572474 is a reply to message #572472] Wed, 12 December 2012 05:41 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
This question has already been answered.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:95212348059


Regards,
Dariyoosh

[Updated on: Wed, 12 December 2012 05:43]

Report message to a moderator

Re: How to create a procedure to read data from database and export it into .csv format [message #572483 is a reply to message #572474] Wed, 12 December 2012 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58860
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The url in AskTom thread is an old one, the new one is:
http://tkyte.blogspot.fr/2009/10/httpasktomoraclecomtkyteflat.html

Regards
Michel
Re: How to create a procedure to read data from database and export it into .csv format [message #572493 is a reply to message #572483] Wed, 12 December 2012 08:24 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
> export it into .csv format without using utilities

What exactly do you consider to be "utilities"?
Re: How to create a procedure to read data from database and export it into .csv format [message #572494 is a reply to message #572493] Wed, 12 December 2012 08:42 Go to previous messageGo to next message
ravidwivedi2288
Messages: 8
Registered: December 2012
Location: Malaysia
Junior Member
I want to read the data from database and convert it into a CSV file. i need to remove the calls to UTL_FILE and replacing them with
dbms_output.put calls instead.can anyone please provide me an urgent solution
Re: How to create a procedure to read data from database and export it into .csv format [message #572495 is a reply to message #572494] Wed, 12 December 2012 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
>can anyone please provide me an urgent solution
why is it urgent for me to provide solution for you?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

set colsep ,
spool csv.lis
select * fom emp;
spool off

Re: How to create a procedure to read data from database and export it into .csv format [message #572498 is a reply to message #572495] Wed, 12 December 2012 09:00 Go to previous messageGo to next message
ravidwivedi2288
Messages: 8
Registered: December 2012
Location: Malaysia
Junior Member
I want to read the data from database and convert it into a .CSV file. i need to use this procedure with using the calls to UTL_FILE and would like to replace them with
dbms_output.put calls instead.
Re: How to create a procedure to read data from database and export it into .csv format [message #572499 is a reply to message #572495] Wed, 12 December 2012 09:05 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
BlackSwan wrote on Wed, 12 December 2012 15:48

set colsep ,
spool csv.lis
select * fom emp;
spool off



The problem with colsep is that SQL*Plus introduces undesired (within the context of the OP) extra spaces among fields. Depending on the application reading this output file, this may cause problems.

Regards,
Dariyoosh
Re: How to create a procedure to read data from database and export it into .csv format [message #572500 is a reply to message #572498] Wed, 12 December 2012 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
ravidwivedi2288 wrote on Wed, 12 December 2012 07:00
I want to read the data from database and convert it into a .CSV file. i need to use this procedure with using the calls to UTL_FILE and would like to replace them with
dbms_output.put calls instead.


Nobody here prevents you from doing so.
please proceed to do so.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I posted code to produce CSV file.
Re: How to create a procedure to read data from database and export it into .csv format [message #572501 is a reply to message #572500] Wed, 12 December 2012 09:12 Go to previous messageGo to next message
ravidwivedi2288
Messages: 8
Registered: December 2012
Location: Malaysia
Junior Member
the above code can be written as procedure in my sql developer tool , as i am not aware of sql developer so much ....
please guide me
Re: How to create a procedure to read data from database and export it into .csv format [message #572502 is a reply to message #572501] Wed, 12 December 2012 09:15 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
ravidwivedi2288 wrote on Wed, 12 December 2012 16:12
the above code can be written as procedure in my sql developer tool , as i am not aware of sql developer so much ....
please guide me


The question is not whether the above mentioned code can be written using SQL Developer. The question is do you know how to create a PL/SQL procedure?

In the case where it might be helpful:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_procedure.htm#LNPLS01373


Regards,
Dariyoosh

[Updated on: Wed, 12 December 2012 09:17]

Report message to a moderator

Re: How to create a procedure to read data from database and export it into .csv format [message #572503 is a reply to message #572502] Wed, 12 December 2012 09:22 Go to previous messageGo to next message
ravidwivedi2288
Messages: 8
Registered: December 2012
Location: Malaysia
Junior Member
thank you for the help. I have a very liitle knowledge in procedures. can you please help in writing a procedure which queries for any table in database and reads them and exports it into .csv file without using UTL_FILE and would like to replace them with
dbms_output.put calls instead.
Re: How to create a procedure to read data from database and export it into .csv format [message #572506 is a reply to message #572503] Wed, 12 December 2012 09:33 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
ravidwivedi2288 wrote on Wed, 12 December 2012 16:22
thank you for the help. I have a very liitle knowledge in procedures. can you please help in writing a procedure which queries for any table in database and reads them and exports it into .csv file without using UTL_FILE and would like to replace them with
dbms_output.put calls instead.


May seem a trivial question, but did you by any chance read at least one time what others posted in answer to your question ?


Regards,
Dariyoosh
Re: How to create a procedure to read data from database and export it into .csv format [message #572507 is a reply to message #572506] Wed, 12 December 2012 09:35 Go to previous messageGo to next message
ravidwivedi2288
Messages: 8
Registered: December 2012
Location: Malaysia
Junior Member
yes i did but could not able to get it
Re: How to create a procedure to read data from database and export it into .csv format [message #572508 is a reply to message #572507] Wed, 12 December 2012 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58860
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 good books for you:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: How to create a procedure to read data from database and export it into .csv format [message #572516 is a reply to message #572503] Wed, 12 December 2012 12:38 Go to previous message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
ravidwivedi2288 wrote on Wed, 12 December 2012 10:22
thank you for the help. I have a very liitle knowledge in procedures. can you please help in writing a procedure which queries for any table in database...


What may be a trivial task for some will most likely be a daunting task for others. If you have very little knowledge of procedures, then this will be a difficult task for you as you will also need dynamic sql in the form of EXECUTE IMMEDIATE. I do not think you are ready for this. I would spend time learning the basics before trying something like you want.
Previous Topic: Merge statement - Intermediate commit
Next Topic: How to display data in a required format by SQL Select Query?
Goto Forum:
  


Current Time: Fri Aug 22 08:48:49 CDT 2014

Total time taken to generate the page: 0.05641 seconds