Query output to file on a Schedule

tbhluehorn

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,

Gary Graham-Oracle

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

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

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.

Gary Graham-Oracle

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

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

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

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

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.