Procedure for creating .csv ouput

Question

please help me create a procedure for exporting data on a specified path from an oracle database, since the CSV file accepts research names from another table.

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

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

Is there a way to create a procedure that will take the research names from the research table and repeat this procedure to create .csv files for all studies?

I read some articles on the Internet, but did not find anything related to this. please, help.

Answer 1

You have to look at the reel and set the command. Because the database usually runs on a remote server, it cannot write files to the local computer. To achieve this, yu should write sql, where you disable certain characteristics in the terminal, and then buffer the result into a file that you can access.

Something like this might start you up:

 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, see the oracle documentation here: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm

With the right set commands, you can create a CSV file.

The above recruitment commands are just a few that you may need, but you will probably need additional options to make your CSV useful.

It is best to write this in a .sql file and run it using sqlplus:

 sqlplus user@db @file