4 Replies Latest reply on Sep 23, 2015 5:20 PM by rp0428

    Schedule and Export SQL Results to CSV using SQL Developer

    1871773

      Hello,

       

      I have a handful of SQL scripts that I would like to schedule using Oracle SQL Developer and have the results saved to a network or SharePoint site.  Can you please help me or point me to some links that would be useful?  I had a hard time finding this information thru the knowledge base and web searches. Note that I am a novice in SQL and have no experience with PL (but am willing to learn the basics). 

       

      For example, here is a script I would like to run and have the results stored somewhere else where I can pick it up when finished:

       

      Select

      mmt.ORGANIZATION_ID,

      mmt.acct_period_id,

      ood.SET_OF_BOOKS_ID,

      ood.ORGANIZATION_NAME,

      --oap.PERIOD_YEAR,

      --oap.PERIOD_NUM,

      --oap.PERIOD_NAME,

      mmt.TRANSACTION_ID,

      gcc.SEGMENT1,

      gcc.segment4,

      gcc.CODE_COMBINATION_ID,

      mmt.TRANSACTION_DATE,

      mmt.INVENTORY_ITEM_ID,

      mmt.TRANSACTION_TYPE_ID,
      mtt.TRANSACTION_TYPE_NAME,

      mtt.TRANSACTION_ACTION_ID,

      mtt.TRANSACTION_SOURCE_TYPE_ID,

      mtt.DESCRIPTION,

      mmt.PRIMARY_QUANTITY,

      mmt.TRANSACTION_UOM,

      mta.ACCOUNTING_LINE_TYPE,

      mta.BASE_TRANSACTION_VALUE,

      mta.RATE_OR_AMOUNT,

      mta.GL_BATCH_ID

      From mtl_material_transactions mmt,

      mtl_transaction_accounts mta,

      MTL_TRANSACTION_TYPES mtt,

      GL_CODE_COMBINATIONS gcc,

      org_organization_definitions ood

      Where mmt.transaction_ID = mta.transaction_ID

      AND mmt.TRANSACTION_TYPE_ID=mtt.TRANSACTION_TYPE_ID

      AND mta.REFERENCE_ACCOUNT = gcc.CODE_COMBINATION_ID

      AND mta.ORGANIZATION_ID=ood.ORGANIZATION_ID

      AND gcc.segment1 = xxxx

      AND mta.TRANSACTION_DATE>='30-DEC-2013'

      AND mta.TRANSACTION_DATE<='02-APR-2014';

       

      Any help is greatly appreciated.  Thanks!

        • 1. Re: Schedule and Export SQL Results to CSV using SQL Developer
          thatJeffSmith-Oracle

          Not really possible today unless you're willing to use an Early Adopter software solution.

           

          If so, then use SQLcl.

           

          Write a batch script to call sqlcl, connect, and run your query, spooling the results to the file you want.

          • 2. Re: Schedule and Export SQL Results to CSV using SQL Developer
            1871773

            Thanks Jeff.  I will look into it.

             

            Quick question from a beginner:

             

            - Should I use a PL block to dump results of a query into a table vs running sql statements 'real-time'?  Or is there not much difference for an ad-hoc query?

            • 3. Re: Schedule and Export SQL Results to CSV using SQL Developer
              thatJeffSmith-Oracle

              if you can do the job with just SQL, then just use SQL

               

              this job can be done completely with SQL

              • 4. Re: Schedule and Export SQL Results to CSV using SQL Developer

                - Should I use a PL block to dump results of a query into a table vs running sql statements 'real-time'?  Or is there not much difference for an ad-hoc query?

                YOU are the only one that knows your full requirements, what DB you are using, how active that DB is and what impact your ad-hoc queries might have on the database. A single poorly written query (e.g. one using cartesian joins by mistake) can bring a database to its knees.

                 

                The 'difference' is that you can use a scheduler job to run the query automatically at prescheduled times. That job can save the results (or multiple results) to a table or can use a procedure to write them to a file.

                 

                Whichever method you choose PLEASE don't write code like this:

                AND mta.TRANSACTION_DATE>='30-DEC-2013'

                AND mta.TRANSACTION_DATE<='02-APR-2014';

                You should NEVER rely on implicit conversions of strings to dates. Those depend on the NLS settings for the session and/or client and are prone to error. ALWAYS explicitly provide the correct values. For the above example you would use the TO_DATE function.

                 

                Also since Oracle DATE values ALWAYS include a time component that second line above means that you will include data for April 2 if the time happens to be 00:00:00 and that is likely NOT what you want.