0

please help me to create a procedure to export data to specified path from oracle database as .csv file take study names from another table.

QUERY : select * from enrollment where study_name = 'FTY67' ;

I have another table(studies) in same database with all studynames.

is there any way to create procedure that will take study names from studies table and repeat this procedure to create .csv files for all studies?

read some articles in internet but not found anything related to this. please help.

Freelancer
  • 9,008
  • 7
  • 42
  • 81
user2039795
  • 79
  • 4
  • 12
  • The answer in the referenced thread is only of limited use, because the package has to be enabled by an admin and usually is disabled for security reasons. There is however a way to do this on the client side with regular sql commands, as this is probably the more common szenario. – Devolus Apr 22 '13 at 11:00
  • If you want to answer the duplicate then go right ahead @Devolus. In my company we use UTL_FILE hundreds of times a day whereas no one has _ever_ used spool to do this... I'm not sure it's "more common". – Ben Apr 22 '13 at 12:48
  • In your company you have access to the server? – Devolus Apr 22 '13 at 14:29

1 Answers1

0

You should look into the spool and set command. Since the database is usually running on a remote server, it can not write files to your local computer. In order to achieve this yu must write an sql where you disable certain characteristics in the terminal, and then spool the result into a file that you can access.

Something like this might get you started:

set term off colsep ";" pause off
spool myfile
select * from x;
spool off
exit

For an overview of the options you can use with SET refer to the oracle documentation here: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm

With the proper set commands you are able to create the CSV file.

The above set commands are just a few you might need, but you will probably need addtional parameters to make your CSV usable.

Best to write this in a file.sql and run it using sqlplus:

sqlplus user@db @file
Devolus
  • 21,661
  • 13
  • 66
  • 113