Solved

Oracle SQL Developer or SQLPlus for creating a scheduled query

Posted on 2014-09-10
16
1,589 Views
Last Modified: 2014-09-11
Hello,

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.

Thanks
0
Comment
Question by:adembo
  • 5
  • 4
  • 4
  • +2
16 Comments
 
LVL 76

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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40316048
Crystal might be an option but you would also have to develop a viewer or use one from this link

http://www.kenhamady.com/bookmarks.html#viewers

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

mlmcc
0
 
LVL 76

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...
0
 
LVL 73

Expert Comment

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

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

Expert Comment

by:vasto
ID: 40316200
You can try R-Tag ( www.r-tag.com). 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: http://www.r-tag.com/Pages/CommunityEdition.aspx

As mlmcc said, there are plenty of 3rd party tools that can handle the Crystal reports part and  some of them are even free.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40316816
if all you want to do is send CSV data by email then check the 3rd and 4th examples in this article


http://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html

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.
0
 
LVL 2

Author Comment

by:adembo
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.

SELECT 
    		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"
		
		FROM CC  CC
		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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 76

Accepted Solution

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

Expert Comment

by:sdstuber
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


http://www.experts-exchange.com/Database/Oracle/A_3043-How-to-FTP-with-Oracle-PL-SQL.html
0
 
LVL 2

Author Comment

by:adembo
ID: 40317072
Slightwv,

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

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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40317082
immediately after each sql statement
0
 
LVL 2

Author Comment

by:adembo
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?
0
 
LVL 76

Expert Comment

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

Author Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query 15 63
Oracle - Query Insert and Update multiple tables 5 43
EXECUTE IMMEDIATE 5 36
Oracle -- identify blocking session 24 22
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now