4 Replies Latest reply on May 1, 2018 3:05 PM by 792311

    Email query results using SQL Developer

    792311

      Server: Oracle RAC 11.2.0.3.0

      OS: RH Linux 6.8

      SQL Developer Version: 4.1.0.17

       

      Question:  I run a query in SQL Developer manually every Friday, next I save the results to CSV file, next I import CSV into Excel sheet, next I email that sheet to several recipients.

       

      1 - Is there a way to create a job on SQL Developer that will run this query, convert into CSV and email the results?

       

      What I have done so far:  In SQL Developer, I right clicked Scheduler --> right click jobs-->Created a job, using most of the following article.

       

      But I never receive the results in my email.

       

      Job attributes:

       

      Enabled= Checked

      Job Class: Sys.Default_Job_Class

      Type of Job: PL/SQL Block

      When to Execute Job: Repeating

      Destination: Local

      Notification: Recipients:  My email address

                          Sender: My email address

      Select Events: All of them

       

      SQL Query: I copied and pasted right below Type of Job column.

       

      Thanks for your help.

       

       

      Article:

      https://docs.oracle.com/cd/E55747_01/doc.41/e58244/GUID-E6812A89-197A-4515-82EF-4B26C05741EA.htm

        • 1. Re: Email query results using SQL Developer
          John_K

          SQL Developer is an interactive client tool. What you have scheduled is a job on the database - nothing to do with SQL Developer. If you want to do this you'll need to write a process in PL/SQL (or a spool script or otherwise - your choice) to produce your output file and schedule that for emailing.

           

          If it was me and this is a one-off requirement, I'd...

           

          • Create a shell script that was scheduled on a cron process which...
            • Calls SQLPLUS/SQLCL to spool an output file to a temporary location. Store the username and password in a .gz file and use zcat to pass it to your command.
            • Email the file to yourself.
            • Remove the file

           

          That is simple and easy - and doesn't involve writing jobs to write CSV yourself (which I can assure you is an art in itself! )

          • 2. Re: Email query results using SQL Developer
            792311

            John,

             

            Thanks for your kind reply and your help. I will follow your suggested steps. I would appreciate it, If you or anyone else can point me to docs that will show me how to do your suggested steps. Of course, I am googling and searching them as well.

             

            1 Question: what is the purpose of scheduler/jobs? Is it same as scheduling a Cron job?

             

             

            Thanks.

            • 3. Re: Email query results using SQL Developer
              John_K
              echo user/password > credentials.txt
              
              compress -f credentials.txt
              

              Then create a shell script something like this:

               

              #!/bin/sh
              sqlplus -S $(zcat credentials.txt.Z) <<EOF
                set feedback off
                set linesize 200
                set pages 200
                set markup csv
                spool /tmp/a.txt
                select 'col' a, 'col2' b from dual
                connect by level <= 10;
                spool off
              EOF
              mail -s "My Report" me@myemail.com < /tmp/a.txt
              rm /tmp/a.txt
              

               

              and add that to your cron process. To be honest, if you have your own user with access to sqlplus, you could omit the email at the end and let cron email you the results.

               

              Obviously this is a test - NOT production code

               

              Scheduled jobs are kind of like cron jobs conceptually - however not the same. They are jobs in the database that are run on a schedule.

              1 person found this helpful
              • 4. Re: Email query results using SQL Developer
                792311

                John,

                 

                Thank you so much for your help and time. I really appreciate it.