Oracle SQL Developer or SQLPlus for creating a scheduled query


I have the pleasure of having to attempt to export a daily CSV file every night from a query in Oracle 12.1. I am struggling mightily at this as I have next to nothing in terms of experience with Oracle. I have an existing query that works great if I run it in SQL Developer and then manually export, but I have to schedule this. I have read bits and pieces about this being able to be done in sql developer. I have heard far more about sqlplus, but for the life of me I cannot figure out how to use this with my query. The syntax appears to be very different from what I did in straight SQL. Left Outer Join is apparently not a sqlplus option, or at least there is different syntax required for this.

What seems easiest to me is to simply save my query as a SQL file, which contains the spool command to create the CSV, and schedule that via a windows task, but need something to be able to run the SQL file.

Is this better suited for a newbie in SQL Manager or sqlplus or some other nice and nifty tool. I checked out Toad, but that appears to need to be purchased to get functionality I would need.

I am happy to explore other reporting tools, and I do have Crystal, but not the server so I dont think I can have it auto create the CSV.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
SQL Developer, sqlplus and Oracle in general all use the same syntax.  There is only one SQL engine in the database.

I don't think you can schedule sql developer scripts.

You should go with sqlplus.

What is the error you have?

My guess is there is a blank line in the script.  By default sqlplus doesn't like blank lines in the middle of a single command.

If you cannot figure it out, please post the SQL and the error message.
Crystal might be an option but you would also have to develop a viewer or use one from this link 

Many can be scheduled or called from the command line (bat file could be scheduled).

slightwv (䄆 Netminder) Commented:
Crystal would be overkill to generate a simple CSV.

Now it it ends up being a complex CSV, then maybe...
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

once you have the csv file then what?

how should it get to whomever it supposed to read it?
You can try R-Tag ( The Pro version can schedule and run SQL queries (including Oracle) , export them ( including to CSV file) and further process them, for example send the generated files by e-mail or save them on the disk. I am not sure if it worth to purchase a tool like that for just one query, but you can at least check how it will work for  you and how much time you are going to save. The same tool has a free version for Crystal reports, so if you decide to create a report and schedule it to generate a CSV file you can use it for free. Here is a link to the community edition:

As mlmcc said, there are plenty of 3rd party tools that can handle the Crystal reports part and  some of them are even free.
if all you want to do is send CSV data by email then check the 3rd and 4th examples in this article

the 3rd code snippet shows how to send the csv data in-line, the 4th shows is the same thing except it sends the data as an attachment

No additional purchases needed.

Someone might comes along and suggest using UTL_MAIL to send an attachment but I don't recommend it.    The article includes code for a set of procedures that exceed the functionality of utl_mail, as well as getting around a bug in oracle's code - plus, they don't require a separate installation.
ademboAuthor Commented:
Hello all, thanks for the suggestions so far. What I need to do is to have the CSV file sent via FTP to a remote system. So the file can be overwrote every day with the new version.

Here is the query I use in sql developer. It is very important that I mention the column names as given in the query and the order is very important. I have to map this to what the receiving system needs, and this is exactly what is needed.

    		CC.TermID as Termid,
		Schools.Name as "[39]Name",
		Teachers.FIRST_NAME as "[5]first_name",
		Teachers.LAST_NAME as "[5]last_name",
		Teachers.EMAIL_ADDR as "[5]Email_Addr",		
		Teachers.TEACHERNUMBER as "[5]TeacherNumber",		
		CustomText2.VALUE as "[5]paylocity_id",
		Courses.COURSE_NAME as "[2]course_name",
		Courses.COURSE_NUMBER "[2]Course_Number",		
		CustomText1.VALUE as "[3]section_name_schoolsuse",		
		Students.FIRST_NAME as "[1]first_name",
		Students.LAST_NAME as "[1]last_name",
		CONCAT (CC.SchoolID,Students.STUDENT_NUMBER)  as "[1]student_number"
		left outer join Teachers  Teachers
		ON CC.TeacherID = Teachers.ID and CC.SchoolID = Teachers.SchoolID
		left outer join Courses  Courses
		ON CC.Course_Number = Courses.Course_Number and CC.SchoolID = Courses.SchoolID 
		left outer join Students  Students
		ON CC.StudentID = Students.ID and CC.SchoolID = Students.SchoolID  
		left outer join Schools  Schools
		ON CC.SchoolID = Schools.School_Number
		left outer join CustomText CustomText1
		ON CC.SectionID = CustomText1.KeyNo and CustomText1.FieldNo = 571		
		left outer join CustomText  CustomText2
		ON Teachers.ID = CustomText2.KeyNo and CustomText2.FieldNo = 551

		left outer join CustomText  CustomText3
		ON Courses.ID = CustomText3.KeyNo and CustomText3.FieldNo = 771

 		WHERE CC.TermID > 0 
		--and CustomText3.VALUE like 'yes'
		and (Students.ExitCode not like ('NS') or Students.ExitCode is NULL)
    		and Students.STUDENT_NUMBER is not NULL
		and Courses.COURSE_NAME not in ('ACE','AE','Lunch','Meet','Recess','Snack','Study Hall','TA','TLC', 'Enrichment')
   		ORDER BY Schools.Name asc, Teachers.LAST_NAME asc, Teachers.FIRST_NAME asc, Courses.COURSE_NAME asc, Students.LAST_NAME asc, Students.FIRST_NAME

Open in new window

So I have tried in SQLPlus and had removed all spaces in between the lines of the query. I dont get anything back from it. It just shows me a 2 which when I then hit enter just exits the command. As I understand it, that is simply stating that it is line 2 of the script. I have the CSV file set to be created, but it never writes anything to it. It stays at 0kb.
slightwv (䄆 Netminder) Commented:
Place a ';' (semi-colon) at the end of the command.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
example of sending csv via FTP can be found in the comments following this article.  Just change the query and contatenations in the FOR loop
ademboAuthor Commented:

Thanks for the suggestion of the semi-colon. This should probably be obvious, but after what command(s)?
slightwv (䄆 Netminder) Commented:
A semi-colon terminates and executes the statement.  So after the entire select.

Also, the select you posted above has a lot of blank lines.  Double check they have been removed.
immediately after each sql statement
ademboAuthor Commented:
Thanks! Are the " " a problem in sqlplus where i define my column name? Does it need to be a single quote or does it not matter?
slightwv (䄆 Netminder) Commented:
Double quotes force case sensitivity.  It is discouraged but allowed.
ademboAuthor Commented:
Awesome!!! Its now working! Thank you all for the tips.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.