Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Use source Period Row in Import Format

Moatasem SheblNov 13 2017 — edited Nov 14 2017

Hello Leaders,

I want to add column in my import format to upload the period as is in my excel sheet, i mean i need the FDMEE to ignore the period which i selected in Data load rule POV and upload the data to the period which exist at my excel source file ,

Note : I am using the driver expression by account dimension and i am at PBCS

Thank You

This post has been answered by JohnGoodwin on Nov 13 2017

Jump to Answer

Glen Conway

Here is an older discussion that covers how to control which Java SQLcl uses:

Java limitation:lower versions of SQLCL available?

Edit:

Be aware that sql.bat is no longer available in release 18.1_x

sql.bat not found in sqlcl-18.1.0 download

1 person found this helpful

user5346147

Answer

I found the same issue and looking at the sql.exe in NotePad++ there are some options including --l14-debug-all.

if you run the exe with the debug option it shows the path that it looks for a JRE.

(looks two levels up from sqlcl/bin directory ../../jdk/jre/java.exe)

I found by putting unpacking a 1.8 (171) jdk in the folder structure at the top level the sqlcl fires into life.

<PATH> / sqlcl / bin

<PATH> /sqlcl / lib

<PATH> / jdk / jre / *

Shame this debug isn't publicised.

Hope this resolves your issue.

Marked as Answer by zaheerf · Sep 27 2020

5 people found this helpful

B.Delmée

I have the same problem. I am not using the stand-alone sqlcl but the version bundled with sqldeveloper.

Using WI10 64bits with a 32 bit JDK 18-151.My JAVA_HOME is set, and java.exe is on my PATH.

The sqlcl.bat from sqldeveloper 4.1 runs fine, but the newer exe does not.

user5346147 I am not sure what work-around you are suggesting

I have also tried the bat file suggested in this thread, but it does not work for me.

edit: i've downloaded the stand-alone 17.3 sqlcl and fixed its broken bat file. That works.

Conclusion: please, please give us back a working, tested bat or cmd script on windows.

Glen Conway

Probably few have the patience to continue editing that fixed 17.3 version of sql.bat with my additional instructions in .

Thinking that the intent of the SQL Developer team was to deprecate sql.bat, I had not saved a copy. Now I am not so sure, perhaps it will be officially resurrected.

Here it is:

@echo off
setlocal
REM ########################################################################
REM #  (@)sql.bat
REM #
REM #  Copyright 2014 by Oracle Corporation,
REM #  500 Oracle Parkway, Redwood Shores, California, 94065, U.S.A.
REM #  All rights reserved.
REM #
REM #  This software is the confidential and proprietary information
REM #  of Oracle Corporation.
REM #
REM # NAME    sql.bat
REM #
REM # DESC     This script starts SqlCli.
REM #
REM # AUTHOR bamcgill
REM #
REM # MODIFIED    
REM #    bamcgill    21/03/2014    Created
REM #   bamcgill    17/07/2014  Simplified classpaths    
REM #   bamcgill    11/12/2014  Renamed script and contents
REM #   bamcgill    16/01/2015  Renamed script and contents
REM #   bamcgill    05/02/2015  Added headless to STD_OPTS to allow use of internal X server.
REM #   totierne    16/10/2015  Put classpath on the end - to allow timesten jars
REM #   bamcgill    08/06/2016  Allow JAVA_HOME to be set in env and add to path
REM #   bamcgill    09/06/2016  Allow JDBC to be set properly 8 thru 6
REM #   bamcgill    19/06/2016  Rework JAVA_HOME switches
REM #   doneill     07/04/2017  Allow CUSTOM_JDBC environment variable to used
REM #   bamcgill    16/10/2017  Enumerating all jars in SQL_HOME\lib
REM ########################################################################
REM # SQL_HOME=.
REM Switch codepage to UTF
chcp 65001  >nul 2>&1
SET SQL_HOME=%~dp0..
REM SET DEBUG=-agentlib:jdwp=transport=dt_socket,server=y,suspend=y,address=8000
REM If java home is not set, this is an opportunity to set it.
IF NOT DEFINED JAVA_HOME (
  REM SET JAVA_HOME="C:\work\sqldeveloper\jdk\jre"
  REM SET PATH="%JAVA_HOME%\bin;%PATH%"
) ELSE (
   REM if java home is set, add it to the path
   REM SET PATH="%JAVA_HOME%\bin;%PATH%"
)
REM @echo on
SET CPFILE=%SQL_HOME%\lib\antlr-runtime.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jackson-databind.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\osdt_cert.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\commons-codec.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\javax.json.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\oraclepki.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\osdt_core.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\commons-logging.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jline.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-collation.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\stringtemplate.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\httpclient.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jsch.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-mapping.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\xdb6.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\httpcore.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\ojdbc8.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-servlet.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\xmlparserv2-sans-jaxp-service.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\httpmime.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools-common.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-utility.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jackson-annotations.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools.http.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jackson-core.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools-jdbcrest.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orajsoda.jar;%CPFILE%                    
REM <!-- OH dependencies we will want to take onboard -->
SET CPFILE=%SQL_HOME%\modules\oracle.xdk\xmlparserv2.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\rdbms\jlib\xdb6.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jlib\orai18n-mapping.jar ;%CPFILE%
SET CPFILE=%SQL_HOME%\jlib\orai18n.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc6.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc7.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc8.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jlib\orai18n-utility.jar;%CPFILE%
REM <!-- When we ship embedded in SQLDeveloper, heres where we look -->
SET CPFILE=%SQL_HOME%\sqldeveloper\sqlcl\lib\*;%CPFILE%
SET CPFILE=%SQLCL_CLASSPATH%;%CPFILE%
REM Use internal simple X for awt in
SET STD_ARGS=-Djava.awt.headless=true -Xss10M
if "%ORACLE_HOME%" == "" (
      SET CPFILE=%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%) ELSE (SET CPFILE=%ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\ojdbc8.jar;%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%
      )
REM Example: SET CUSTOM_JDBC="C:\thirdparty_jdbc_driver.jar"
IF NOT "%CUSTOM_JDBC%" == "" (
      SET CPFILE=%CUSTOM_JDBC%;%CPFILE%
)
REM SET CPFILE=%ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\jdbc\lib\ojdbc7.jar;%ORACLE_HOME%\jdbc\lib\ojdbc6.jar;%CPFILE%
REM Have you downloaded a specific jre and dropped it into SQL_HOME as jre
IF NOT EXIST "%SQL_HOME%\jre" GOTO OK
  SET JAVA_HOME=%SQL_HOME%\jre\
  SET PATH=%JAVA_HOME%bin;%PATH%
:OK
IF NOT DEFINED JAVA_HOME (
  java %JAVA_OPTS% %STD_ARGS% %DEBUG% -cp "%CPFILE%" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli %*
  GOTO EXIT
)
REM if java home is set, then this is where we use it.
"%JAVA_HOME%\bin\java" %JAVA_OPTS% %STD_ARGS% %DEBUG% -cp "%CPFILE%" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli %*
:EXIT
endlocal

1 person found this helpful

Glen Conway

The @"user5346147" must be referring to this post in

Glen Conway

Here are two small changes to the version above.

1. Fix an error in a jar file name: xmlparserv2-sans-jaxp-service.jar should be xmlparserv2-sans-jaxp-services.jar

2. Add -Dfile.encoding=UTF-8 to STD_ARGS variable to help deal with a character encoding issue discussed in

Some have also suggested the code page (chcp 65001) should revert back to whatever it was before sql.bat starts, but I am leaving that alone.

The corrected version is:

@echo off
setlocal
REM ########################################################################
REM #  (@)sql.bat
REM #
REM #  Copyright 2014 by Oracle Corporation,
REM #  500 Oracle Parkway, Redwood Shores, California, 94065, U.S.A.
REM #  All rights reserved.
REM #
REM #  This software is the confidential and proprietary information
REM #  of Oracle Corporation.
REM #
REM # NAME    sql.bat
REM #
REM # DESC    This script starts SqlCli.
REM #
REM # AUTHOR bamcgill
REM #
REM # MODIFIED 
REM #    bamcgill    21/03/2014    Created
REM #  bamcgill    17/07/2014  Simplified classpaths 
REM #  bamcgill    11/12/2014  Renamed script and contents
REM #  bamcgill    16/01/2015  Renamed script and contents
REM #  bamcgill    05/02/2015  Added headless to STD_OPTS to allow use of internal X server.
REM #  totierne    16/10/2015  Put classpath on the end - to allow timesten jars
REM #  bamcgill    08/06/2016  Allow JAVA_HOME to be set in env and add to path
REM #  bamcgill    09/06/2016  Allow JDBC to be set properly 8 thru 6
REM #  bamcgill    19/06/2016  Rework JAVA_HOME switches
REM #  doneill    07/04/2017  Allow CUSTOM_JDBC environment variable to used
REM #  bamcgill    16/10/2017  Enumerating all jars in SQL_HOME\lib
REM ########################################################################
REM # SQL_HOME=.
REM Switch codepage to UTF
chcp 65001  >nul 2>&1
SET SQL_HOME=%~dp0..
REM SET DEBUG=-agentlib:jdwp=transport=dt_socket,server=y,suspend=y,address=8000
REM If java home is not set, this is an opportunity to set it.
IF NOT DEFINED JAVA_HOME (
  REM SET JAVA_HOME="C:\work\sqldeveloper\jdk\jre"
  REM SET PATH="%JAVA_HOME%\bin;%PATH%"
) ELSE (
  REM if java home is set, add it to the path
  REM SET PATH="%JAVA_HOME%\bin;%PATH%"
)
REM @echo on
SET CPFILE=%SQL_HOME%\lib\antlr-runtime.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jackson-databind.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\osdt_cert.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\commons-codec.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\javax.json.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\oraclepki.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\osdt_core.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\commons-logging.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jline.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-collation.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\stringtemplate.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\httpclient.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jsch.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-mapping.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\xdb6.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\httpcore.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\ojdbc8.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-servlet.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\xmlparserv2-sans-jaxp-services.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\httpmime.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools-common.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n-utility.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jackson-annotations.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools.http.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orai18n.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\jackson-core.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\dbtools-jdbcrest.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\lib\orajsoda.jar;%CPFILE%                 
REM <!-- OH dependencies we will want to take onboard -->
SET CPFILE=%SQL_HOME%\modules\oracle.xdk\xmlparserv2.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\rdbms\jlib\xdb6.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jlib\orai18n-mapping.jar ;%CPFILE%
SET CPFILE=%SQL_HOME%\jlib\orai18n.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc6.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc7.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc8.jar;%CPFILE%
SET CPFILE=%SQL_HOME%\jlib\orai18n-utility.jar;%CPFILE%
REM <!-- When we ship embedded in SQLDeveloper, heres where we look -->
SET CPFILE=%SQL_HOME%\sqldeveloper\sqlcl\lib\*;%CPFILE%
SET CPFILE=%SQLCL_CLASSPATH%;%CPFILE%
REM Use internal simple X for awt in
SET STD_ARGS=-Djava.awt.headless=true -Dfile.encoding=UTF-8 -Xss10M
if "%ORACLE_HOME%" == "" (
      SET CPFILE=%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%) ELSE (SET CPFILE=%ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\ojdbc8.jar;%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%
      )
REM Example: SET CUSTOM_JDBC="C:\thirdparty_jdbc_driver.jar"
IF NOT "%CUSTOM_JDBC%" == "" (
      SET CPFILE=%CUSTOM_JDBC%;%CPFILE%
)
REM SET CPFILE=%ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\jdbc\lib\ojdbc7.jar;%ORACLE_HOME%\jdbc\lib\ojdbc6.jar;%CPFILE%
REM Have you downloaded a specific jre and dropped it into SQL_HOME as jre
IF NOT EXIST "%SQL_HOME%\jre" GOTO OK
  SET JAVA_HOME=%SQL_HOME%\jre\
  SET PATH=%JAVA_HOME%bin;%PATH%
:OK
IF NOT DEFINED JAVA_HOME (
  java %JAVA_OPTS% %STD_ARGS% %DEBUG% -cp "%CPFILE%" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli %*
  GOTO EXIT
)
REM if java home is set, then this is where we use it.
"%JAVA_HOME%\bin\java" %JAVA_OPTS% %STD_ARGS% %DEBUG% -cp "%CPFILE%" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli %*
:EXIT
endlocal

1 person found this helpful

zaheerf

After downloading sqlcl-18.1 zip file, I unzipped it into C:\sqlcl-18.1.0 and copied jdk folder from SQL Developer location and pasted into folder C:\sqlcl-18.1.0. Then I went into the folder C:\sqlcl-18.1.0\sqlcl\bin and launched sql.exe, it worked for me.

1 person found this helpful

Stephan Visser

I ran into the same problem on one of our Windows Server 2012 R2 VMs and discovered that this has to do with the Java settings in the registry.
On this particular server I never installed a JRE properly. I just copied over the needed versions of the JRE for specific use cases and put them in a single folder.

When I wanted to use the latest SQLcl I got the same error (This application requires a Java Runtime Environment 1.8.0_150) even after setting JAVA_HOME and PATH correctly in the calling batch file and the environment. Then I found the difference between the two registries so exported the keys below and after importing them on the server SQLcl started to work fine. Make sure to change the versions and the paths in the registry keys below according to your situation if you should decide to go for this solution. Needless to say I will take no responsibility if your server crashes (or produces Klingon output) after dropping this into the registry. (For me it worked fine. Your mileage may vary ;-))

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft]

[HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment]

"CurrentVersion"="1.8"

[HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\1.8]

"RuntimeLib"="E:\\Java\\jre1.8.0_162\\bin\\server\\jvm.dll"

"JavaHome"="E:\\Java\\jre1.8.0_162"

"MicroVersion"="0"

[HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\1.8.0_162]

"JavaHome"="E:\\Java\\jre1.8.0_162"

"MicroVersion"="0"

"RuntimeLib"="E:\\Java\\jre1.8.0_162\\bin\\server\\jvm.dll"

Piotr Wrzosek

Thanks for the info. Some corrections/additions:

The parameter to show debug info is --l4j-debug-all, output is sent to launch4j.log file.

sql.exe from SQLcl 19.2 looks for bundled JRE in the following directory (relative to sql.exe)

Bundled JRE: ..\..\jdk\jre

which is one level higher (outside sqlcl dir).

1 person found this helpful

Glen Conway

Thanks, @"PiotrW", for pointing out the correct Launch4j debug parameter. However, I especially want to thank you for mentioning that SQLcl (sql.exe) is checking for a bundled JRE at ..\..\jdk\jre relative to the location of sql.exe. That is how sqldeveloper.exe has (always?) worked, and is how sql.exe now works as of the 18.1 & up releases. Back in the 17.4 and lower releases, though, it looked in ..\jre. That has long been a point of confusion for me -- I latched onto that "rule", but should have let it go long ago!

Others have noted this new JRE rule, as did the poster with the correct answer in the current discussion, but without additional explanation. I believe the change also corresponds with dropping the sql.bat file.

Cheers

1 person found this helpful

user9942483

My solution:

installed jdk 21 and set JAVA_HOME Path to the jdk21 directory in sql at the very beginning

1 - 11

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 12 2017

Added on Nov 13 2017

#business-intelligence-applications, #financial-data-management, #performance-management-applications

4 comments

371 views

Analytics Software

Use source Period Row in Import Format

Comments

Post Details