Help for exporting to CSV file

Toad Data Point & Toad Intelligence Central
1

Hi

I need to pick some brains on exporting to a csv file.
As part of an automated routine, I need to be able to create a CSV file that contains not only my extracted data but also some file format rules to allow it to be uploaded by a process on a customers system.

The code I have is set echo offset verify offset termout onset heading offset pages 50000set feedback offset newpage noneset linesize 160　column dcol new_value myFile noprintSELECT DISTINCT PALLETS.FFSPORRECNO dcolfrom CUKLIVE.PALLETS, cloudspace1.PO_PALLETS@cukcloud,cuklive.itestowhere PALLETS.FFSPORRECNO = PO_PALLETS.PO_NUMBERand PALLETS.FFSPORRECNO = ITESTO.ISTPONOAND ITESTO.ISTSTKLOC = 2081AND PO_PALLETS.PROCESSED =‘Y’ AND PALLETS.FFSPORRECNO=88333;spool h:&myFile-Pallet.csv　SELECT ‘#PO’||&myFile from dual;SELECT ‘TYP:RCV’ from dual;SELECT rtrim(nvl(PALLETS.SSCCPALNO,PALLETS.STOREPALNO))||’,’||’,’||PALLETS.QUANTITYfrom CUKLIVE.PALLETS, cloudspace1.PO_PALLETS@cukcloud,cuklive.itestowhere PALLETS.FFSPORRECNO = PO_PALLETS.PO_NUMBERand PALLETS.FFSPORRECNO = ITESTO.ISTPONOAND ITESTO.ISTSTKLOC = 2081AND PO_PALLETS.PROCESSED =‘Y’ AND PALLETS.FFSPORRECNO=88333;spool off;exit;

The header lines ‘#PO’ & ‘TYP:RCV’ must go at the top of each file before the data extracted in the select statement.
Also each file needs to have a unique referance number define by the value &myFile in the line spool h:&myFile-Pallet.csv

The value extracted by the line PALLETS.FFSPORRECNO=88333
change on each run of the automation script.

When I try and run this in toad for data analysts it error due to &myFile being a bind variable.

Does anyone have any ideas on how I can get this to run, or even an alternative solution.

Thanks in advance

Dave

2

You can include a single SQL statement at the top of an exported CSV. This is an option Output Format page of the export wizard.

You are using SQL Plus, and this is not supported in TDA automation. You can use a single SQL statement that uses bind variables. You are using a literal replacement variable. IE: ‘&myfile’. Instead use the colin and define an automation variable of the same name. You will need to set the value of the variable in the automation script.

Use this statement to set the variable value.

SELECT ‘#PO’|| :myFile from dual;\

See this blog post for using variables in automation.
http://www.toadworld.com/Blogs/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx

It shows the old UI so don’t get confused by that.

Debbie