Forum Stats

  • 3,752,633 Users
  • 2,250,529 Discussions
  • 7,867,903 Comments

Discussions

Query output to file on a Schedule

tbhluehorn
tbhluehorn Member Posts: 95 Red Ribbon
edited Nov 21, 2017 9:12AM in SQLcl

Hello,

I use a windows  7 workstation. Can SQLcl be utilized to schedule a task that can be run hourly to export the results of sql select statement to a text or csv file?

Thank you,

Tagged:
tbhluehorn

Answers

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Nov 6, 2017 2:56PM

    Just using SQLcl, probably not.  Perhaps using nested scripts, each with a REPEAT command (sleep limit is 120 seconds only), might be a possibility.  Why not checkout Windows Task Scheduler to launch SQLcl sessions instead:

    https://en.wikipedia.org/wiki/Windows_Task_Scheduler

    tbhluehorn
  • tbhluehorn
    tbhluehorn Member Posts: 95 Red Ribbon
    edited Nov 6, 2017 3:25PM

    This sounds like where I want to go with this.   I tried downloading the lastest version of SqlCl, but now having trouble connecting it to the database I connect to via SQL Developer. Please help me connect to the database using SqLCl I work with on the same workstation using SQL Developer.    I think that would be the first step ?    Then would it be possible to schedule using task scheduler.

  • tbhluehorn
    tbhluehorn Member Posts: 95 Red Ribbon
    edited Nov 6, 2017 5:53PM

    I am replying to my own post.   I was able to connect to the oracle database using SQLCL.   I was able able to execute the script I needed which I was not able to execute in sqlplus. Now I am going to try to schedule this using windows task scheduler.   I was having trouble connecting to the database earlier, when I downloaded SqlCL and extracted it to a folder on my local drive(I couldn't connect to the databases).   I came across another post somewhere on the net which indicated one can use the SqlCL file which resides in the same install directory as sqldeveloper.  I looked in my current sql developer installation directory and found this file sql.bat in the bin directory. From here I entered my user name/password and database name as prompted and was able to execute a previously saved script.sql file which outputted to a text file using spool. pastedImage_0.png

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Nov 6, 2017 8:29PM

    Glad you worked it out, but you should be able to connect to your database using a standalone SQLcl install or the one that comes with SQL Developer.  There may be one or more differences when running sql.exe and sql.bat.  One such difference is described in another discussion, sqlcl java errors under different db homes which I repeat here for convenience...

    1. If a JRE is copied into a sqlcl\jre folder, then SQLcl uses that Java version.2. Otherwise, running sql.bat uses an explicitly set JAVA_HOME, followed by the Java version appearing first in PATH.3. Otherwise, running sql.exe first checks the Windows Registry for which Java version to use, then falls through to the rules in (2).Java version must be at least 1.7 to avoid an Unsupported major.minor version error.

    If you tried to connect using a TNS alias, then the exe will check the Windows registry for the location of tnsnames.ora whereas the bat file relies on an environment variable being set, either ORACLE_HOME or TNS_ALIAS.

    Good luck moving forward and getting your script executed via Windows Task Scheduler.

  • castorp
    castorp Member Posts: 469 Blue Ribbon
    edited Nov 21, 2017 3:51AM
    2. Otherwise, running sql.bat uses an explicitly set JAVA_HOME, followed by the Java version appearing first in PATH.3. Otherwise, running sql.exe first checks the Windows Registry for which Java version to use, then falls through to the rules in (2).Java version must be at least 1.7 to avoid an Unsupported major.minor version error.

    The "fall through to (2)" does not work for me even though I have a JAVA_HOME and java.exe is in the path:

    c:\etc\sqlcl\bin>echo %JAVA_HOME% C:\etc\Java8  c:\etc\sqlcl\bin>java -version java version "1.8.0_144" Java(TM) SE Runtime Environment (build 1.8.0_144-b01) Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)  c:\etc\sqlcl\bin>sql.exe This application requires a Java Runtime Environment 1.8.0_50  c:\etc\sqlcl\bin>

    sql.exe then just opens the browser with java.com.

    sql.bat works fine though which is a workaround for now.

    I do have Java 8 and Java 9 installed though. I am not sure which registry entry sql.exe is checking.

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Nov 21, 2017 5:11AM
    I do have Java 8 and Java 9 installed though. I am not sure which registry entry sql.exe is checking.

    When installing a new JDK, I never install the public JRE that comes with it.  This is so any Java applications for which I do not need to run with a specific JDK will use the latest JRE that Oracle's Java Update pushes out, which of course always lags the latest, greatest, possibly unstable stuff. 

    I believe the last JRE push was for Java 8 update 151, but at some point Java Update will switch to pushing out Java 9 updates.

    Possibly sql.exe finds Java 9 in the registry and is complaining about that but giving a misleading message about Java 8 update 50 being the minimum required version.  If Java 9 is copied into a sqlcl\jre folder (I tried this with SQLcl 17.3), then SQLcl (sql.exe) uses Java 9 with no complaints.  Once SQLcl opens, you can "show java" to see which Java is running.

  • castorp
    castorp Member Posts: 469 Blue Ribbon
    edited Nov 21, 2017 7:22AM
    Gary Graham-Oracle wrote:When installing a new JDK, I never install the public JRE that comes with it. This is so any Java applications for which I do not need to run with a specific JDK will use the latest JRE that Oracle's Java Update pushes out, which of course always lags the latest, greatest, possibly unstable stuff. 

    I don't do that (mainly because the "public JRE" also installed that dreaded browser applet plugin for older versions). I have only the JDKs installed, no "public JRE"

    Possibly sql.exe finds Java 9 in the registry and is complaining about that but giving a misleading message about Java 8 update 50 being the minimum required version. If Java 9 is copied into a sqlcl\jre folder (I tried this with SQLcl 17.3), then SQLcl (sql.exe) uses Java 9 with no complaints. Once SQLcl opens, you can "show java" to see which Java is running.

    I would like to avoid copying a whole JRE around if possible.

    The question is: why doesn't it fall back to using JAVA_HOME if the found Java installation isn't "acceptable"?

    Isn't there some "--java-home" or "--jre-location" parameter for sql.exe? And

    I looked at the Launch4J documentation, but apparently the JDK/JRE can't be specified for that through an INI file.

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Nov 21, 2017 9:12AM

    When the sql.exe is built by Launch4J, we pass it a chunk of XML to control certain things as described in...

    Launch4j - Cross-platform Java executable wrapper

    in the Configuration file section.  There are a couple of different files in our code base with such XML, so I am not sure which is actually passed in, but here is one version of how the JRE may be configured...

      <jre>    <path></path>    <bundledJre64Bit>false</bundledJre64Bit>    <bundledJreAsFallback>false</bundledJreAsFallback>    <minVersion>1.8.0_111</minVersion>    <maxVersion></maxVersion>    <jdkPreference>preferJre</jdkPreference>    <runtimeBits>64/32</runtimeBits>    <opt>-Djava.awt.headless=true</opt>    <opt>-Dapple.awt.UIElement=true</opt>    <opt>-Dstartup.directory=%OLD_PWD%</opt>    <opt>-Ddbtools.windows_proxy_enabled=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable%</opt>    <opt>-Ddbtools.windows_proxy_server=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer%</opt>    <opt>-Ddbtools.windows_proxy_pac=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\AutoConfigURL%</opt>    <opt>-Ddbtools.windows_proxy_override=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyOverride%</opt>  </jre>

    So you see there is no bundled JRE specified, no maximum version to search for, only a minimum version.  Not sure where Launch4J searches in the Windows registry (or elsewhere) or why it is not falling through to JAVA_HOME in your environment.  At least with sql.bat you can read the code and see what is does.