how to use pl sql to export a csv file to another server

Why not call the sp in Java and then move the file in Java?

:)

I know it's a little late but if you're already using pl/sql, you can simply ftp your csv results directly to the remote server.  You don't even need to create a local file.  Just a clob of the contents and send them over.  You can create a file if you want a local copy but if the local serves no purpose then skip that step.


https://www.experts-exchange.com/Database/Oracle/A_3043-How-to-FTP-with-Oracle-PL-SQL.html

I'm late to this as well and even though it has already been closed, I'll make another suggestion:

Create the file on the remote server with sqlplus from the remote server.

slightwv - thank you for this idea.  how would I schedule a job to trigger the creation of the file daily?  I have sqlplus on the other server, just not sure how to schedule a recurring task

On the remote server:

Windows:
Create a BAT file and use task scheduler.

Unix:
Create a shell script and use cron.

sdstuber - yes please reopen this question  I think this will be a much better solution for my needs, I just need some examples and this should work.  thank you!

Examples of what pieces?

What OS is the remote server?

It's actually a Windows XP box we are using as a server (have apache loaded on this and IIS as well)  
I have the ORCALE SQL developer on this computer and I can open SQLPLUS as well.  I am very comforatable within sql developer but have not had much experience with batch files to perform actions with SQLPLUS.  I'm building procedures and functions and Views in SQL Developer to handle the necessary business logic.  so I basically need to push the contents of a view into a csv file and place it in a specific folder on this computer  (others will be able to access this folder as I have shared it)

so if you can give me an idea of ho this would work/ what it would look like in a batch file then I am pretty certain I can adapt it for my query and schedule a task to execute the batch file.  hopefully that is clear

Create a .sql file on the remote machine, say mycsv.sql

in it write the select to generate the CSV (I'll use some XML tricks for csv aggregation that will allow large extracts.  There are others out there.).

The only query you need to worry about is the inner most one.  Replace it with your query and you should be fine:
select col1, col2, ''Hello'' col3 from tab1

set pages 0
set lines 10000
set trimspool on
set feedback off

spool myfile.csv
select xmlquery(
			'
				for $i in /ROW
				return string-join($i/child::*, ",")
			'
			passing t.column_value
			returning content
		).getclobval()
from (
select column_value
from table(xmlsequence(extract(
(
	select xmltype(dbms_xmlgen.getxml('select col1, col2, ''Hello'' col3 from tab1')) myxml from dual
)
, '/ROWSET/ROW'))) 
) t
/

spool off

Select allOpen in new window

The create mybatch.bat:

sqlplus username/password @mycsv.sql

Select allOpen in new window

Then in Task scheduler, schedule mybatch.bat


You might need to set some environment variables inside the BAT script so Oracle can find sqlplus, etc...

Bear in mind that a pure Java solution is the only portable one.

given that the data is coming from Oracle, any sqlplus script or stored procedure written in/for Oracle should be at least as portable as java

Of course, a java stored procedure could be used too

ok sdstuber, it's taken me a few days to succesfully get sqlplus working and establish a connection so that everything is ready to go for your code example above.

here are the errors that are being recorded in the csv file

ERROR:      
ORA-19112: error raised during evaluation:      
ORA-06550: line 1       column 13:
PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared      
ORA-06550: line 1       column 7:
PL/SQL: Statement ignored      

and here is the exact code I used in the .sql file

set pages 0
set lines 10000
set trimspool on
set feedback off

spool C:\eBay2013\SCRIPT\mytestfile.csv
select xmlquery(
                  '
                        for $i in /ROW
                        return string-join($i/child::*, ",")
                  '
                  passing t.column_value
                  returning content
            ).getclobval()
from (
select column_value
from table(xmlsequence(extract(
(
      select xmltype(dbms_xmlgen.getxml('select ''a'', ''b'' from dual')) myxml from dual
)
, '/ROWSET/ROW')))
) t
/

spool off

***ALSO, how do I close the database connection...or is this autmoatic when the SQLPLUS window closes?

>>ok sdstuber

You used sdstuber by name but my code example?

>>PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared      

Looks like this could be a couple of things:
https://forums.oracle.com/thread/366195

Can you provide the version and type of database?  For example: 10.2.0.4 Enterprise Edition.

>>ALSO, how do I close the database connection...or is this autmoatic when the SQLPLUS window closes?

It is automatic.

oh, slightwv, thank you for noticing!!!  I intended that for you.  

I ran select * from v$verion;  and here are the results:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE      10.2.0.4.0      Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

I also ran the query in the link you posted

select * from dba_objects where object_name = 'DBMS_XQUERYINT';

and 0 rows were returned, so it looks like this technique will not work with our database

or just use sqlplus  "colsep"  to define "," to be a delimiter

sure, for this example there are 2 fields, 1 text less than 100chars, and a number field less than 30 characters

hey, I got it!!  thank you so much for the detailed examples!

Great detail!

I think it's ok to do this, and I figured it was more appropriate to open another question than to tack this last bit onto this question.  I need a little more help to complete this automation.  Here is the link to thenew question if you think you can help!
https://www.experts-exchange.com/questions/28186798/calling-sql-from-bat-Need-sql-to-add-add-date-time-to-csv-filename-it-creates.html

I think it's ok to do this, and I figured it was more appropriate to open another question than to tack this last bit onto this question.

You've already assigned points for this, so if it doesn't work you should persue it here

>>so if it doesn't work you should persue it here

This question was how to create the values themselves.  The new question is how to name the file.  Technically two different questions.