Oracle SQL Developer or SQLPlus for creating a scheduled query

Posted on 2014-09-10
Last Modified: 2014-09-11

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.

Question by:adembo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40315999
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.
LVL 100

Expert Comment

ID: 40316048
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).

LVL 77

Expert Comment

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

Now it it ends up being a complex CSV, then maybe...
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 74

Expert Comment

ID: 40316111
once you have the csv file then what?

how should it get to whomever it supposed to read it?
LVL 18

Expert Comment

ID: 40316200
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.
LVL 74

Expert Comment

ID: 40316816
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.

Author Comment

ID: 40316990
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.
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 40317038
Place a ';' (semi-colon) at the end of the command.
LVL 74

Expert Comment

ID: 40317049
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

Author Comment

ID: 40317072

Thanks for the suggestion of the semi-colon. This should probably be obvious, but after what command(s)?
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40317081
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.
LVL 74

Expert Comment

ID: 40317082
immediately after each sql statement

Author Comment

ID: 40317115
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?
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40317123
Double quotes force case sensitivity.  It is discouraged but allowed.

Author Comment

ID: 40317527
Awesome!!! Its now working! Thank you all for the tips.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sybase and replication server 13 83
add more rows to hierarchy 3 48
Toad 12.10 Enterprise visual interface 4 47
SQL query to select row with MAX date 7 68
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question