Is there profiler in Oracle like SQL profiler in the SQL server?

Tried to use apximp in sqlcl 17.3. Would not work as is - after debug saw that workspace id not correctly obtained, always null. Running apex app export file fnnn.sql always exit with error. After removing semicolon after "end" in all begin..end in this.sql (lines 14-26) the fnnn.sql runs until ..done. But in apex application builder and in sqlcl apex command, new created app still not there.

A short piece in the read me on how to run your example scripts would be great for those of us new to sqlcl. Not knowing how to run scripts in sqlcl, I finally found an example in an old closed issue.
Example:
script apx
apx workspace

I didn't realize "script apx" was just the load command and didn't enter the second line. I expected something to happen and was left wondering what happened.

Hi! I tried to implement in my Application both of the Methods described in the link below.

https://github.com/oracle/oracle-db-tools/blob/d7c8b9d5ff10033bbf2e8750e87046ace834160a/sqlcl/java/src/ParseScriptRunOneAtATime.java
https://github.com/oracle/oracle-db-tools/blob/d7c8b9d5ff10033bbf2e8750e87046ace834160a/sqlcl/java/src/RunMyScript.java

There is a problem in both of them for Big SQL Scripts, my script is 6,5 Mb big it's APEX Application script.
I tried both of the APEX Types of Scripts splitted and not splitted
it's executing only half of the script in just break in the middle without errors. But only half of the script was executed.

Can someone tell me where there is a problem hier?

PS. It's not a problem that Script is too big, i assuming that this Java bibliotheque can't be used for import APEX Application.

@krisrice in https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/examples add a README with a table listing:

• File name
• Description

Is it possible to rename this repository to oracle-db-tools? Most GitHub repositories follow the lower-case-dash-separator standard

It appears that several standard SQLcl script commands don't work from Java. For example:

• ORA-00922: missing or invalid option
  set null '^'
  set verify off
• ORA-00900: invalid SQL statement
  define myparam = '&1'

These work fine when running SQLcl on the command line.

I'm using SQLcl 21.1.1 and this site's pattern from "sqlcl/java/README.md"
ScriptExecutor sqlcl = new ScriptExecutor(conn);
sqlcl.setStmt("@/mySqlclScript.sql");
sqlcl.run();

We have an extensive library of interconnected SQLPlus scripts that we've automated by calling sqlplus.exe from Java.
We'd like to replace that with java based SQLcl, ideally without a major rewrite of our scripts.

Questions:

• Should all the SQLcl capabilities be available when calling from Java?
  2.1 Alphabetic List of SQLcl Commands
  https://docs.oracle.com/en/database/oracle/sql-developer-command-line/21.1/sqcug/working-sqlcl.html#GUID-DB0D123B-AFA1-4C5A-AB2A-A7B59863C9B9

  • If so, how can I get null, verify, define, etc to work?

• If not, is there documentation on which commands do NOT work from Java?
  Are there work-arounds?
  e.g. DEFINE is necessary when making calls to other @@childScripts.sql... how can that be done from Java?

• Is there JavaDoc for SQLcl?
  For example, this command doesn't appear to set the base directory - setStmt() appears to require absolute path
  sqlcl.setDirectory(sqlDirPath);

• Let me know if there's a different forum I should use for these questions.

Thanks!
Bob

I have faced an issue when tried to create an alias to save typing working with scripts. I looks like the bind variables are not passed to script if they are defined in alias.
It's very simple to reproduce with the args.js as example.
Check how it should work:

DEV>script args one two
0:args
1:one
2:two

create an alias:

DEV>alias test_args=script args :p1 :p2;
DEV>alias list test_args
test_args
---------
script args :p1 :p2

and run the script though the alias:

DEV>test_args one two

Command=test_args
0:args
1::p1
2::p2

@krisrice Having issues with apxexp.js missing a couple of pages on every export. Bug?

script apxexp.js
apxexp  -applicationid 114 -replace

I have got strange errors in sqldeveloper console. I don't know how it is important, formating code seems to be working OK.

I have downloaded latest version of plsql-formatter-settings-main.zip (readme date is 18.7.2021 16:34).

Console output:

Bind var 'offset' not found: null
Symbol 'analytic_clause[6,12)' not found
Symbol 'JSON_OBJECT' not found
incrementalAlignments callback: null
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at oracle.dbtools.arbori.Program.javaCallback(Program.java:1344)
        at oracle.dbtools.arbori.Program.eval(Program.java:1243)
        at oracle.dbtools.app.Format.format(Format.java:375)
        at oracle.dbtools.raptor.config.FormatSQLOptionsPanel.updatePreview(FormatSQLOptionsPanel.java:654)
        at oracle.dbtools.raptor.config.FormatSQLAdvancedOptions.valueChanged(FormatSQLAdvancedOptions.java:370)
        at javax.swing.JTree.fireValueChanged(JTree.java:2934)
        at javax.swing.JTree$TreeSelectionRedirector.valueChanged(JTree.java:3398)
        at javax.swing.tree.DefaultTreeSelectionModel.fireValueChanged(DefaultTreeSelectionModel.java:635)
        at javax.swing.tree.DefaultTreeSelectionModel.notifyPathChange(DefaultTreeSelectionModel.java:1093)
        at javax.swing.tree.DefaultTreeSelectionModel.setSelectionPaths(DefaultTreeSelectionModel.java:294)
        at javax.swing.tree.DefaultTreeSelectionModel.setSelectionPath(DefaultTreeSelectionModel.java:188)
        at javax.swing.JTree.setSelectionPath(JTree.java:1641)
        at oracle.dbtools.raptor.config.FormatSQLAdvancedOptions.<init>(FormatSQLAdvancedOptions.java:165)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at javax.ide.util.MetaClass.newInstance(MetaClass.java:145)
        at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122)
        at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65)
        at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NullPointerException
        at oracle.dbtools.app.Format.incrementalAlignments(Format.java:818)
        ... 32 more
incrementalAlignments callback: null
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at oracle.dbtools.arbori.Program.javaCallback(Program.java:1344)
        at oracle.dbtools.arbori.Program.eval(Program.java:1243)
        at oracle.dbtools.app.Format.format(Format.java:375)
        at oracle.dbtools.format.FormatCommand.formatCode(FormatCommand.java:94)
        at oracle.dbtools.format.FormatCommand$1.doWork(FormatCommand.java:54)
        at oracle.dbtools.format.FormatCommand$1.doWork(FormatCommand.java:49)
        at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NullPointerException
        at oracle.dbtools.app.Format.incrementalAlignments(Format.java:818)
        ... 18 more
Bind var 'offset' not found: null

Hi,

I tried to import our apex application with the apximp.js script. It works fine if I do not export the translation.
When I embed the translation with the application export the import script stops at the following line (see picture below). I already tried to change the affected SQL region but no success. What's the issue here? Any ideas?

Thanks

Hi

we are using a common 'header' for almost all our SQL scripts, such as:

DEFINE myjob = '';
WHENEVER SQLERROR EXIT 1
WHENEVER OSERROR EXIT 2
col myuser noprint new_value myuser
col mydate noprint new_value mydate
col mydb noprint new_value mydb
SELECT UPPER(USER) myuser FROM dual;
SELECT TO_CHAR(sysdate,'YYYYMMDDYHH24MISS') mydate FROM dual;
SELECT sys_context('USERENV','DB_NAME') mydb FROM dual;
SET FEEDBACK ON TIME ON TIMING ON ECHO ON HEADING ON VERIFY ON
spool Log/&myjob.&mydb.&myuser._&mydate..log

What would be a good approach to automatically run it, whenever we execute an SQL script? Is there something like login.sql only that is applied to SQL scripts? Or can I create a javascript that first run this script, and then the one I actually want to execute?

thanks a lot for your help
/Juergen

Hi there.

I'm reporting a bug on SqlCl. (Tested on SqlCl version 19.2.1.206.1649 ).

With the statement CREATE JAVA, SqlCl mishandles quotes symbols whenever they are contained in single-line java comments.

I.E.: consinder the following statements:

The following statement will work fine since the quotes appear in an even number

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MyTestClass AS
 public class MyTestClass {
	 public static String welcome() {
		// it's a comment
		return "Welcome World";
		// it's another comment
	 } 
}
/

On the other hand the following statement will fail since quotes appear in an odd number (just one)

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MyTestClass AS
 public class MyTestClass {
	 public static String welcome() {
		// it's a comment
		return "Welcome World";
	 } 
}
/

Here is the SqlCl output for the first snippet:

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MyTestClass AS
  2   public class MyTestClass {
  3   public static String welcome() {
  4  // it's a comment
  5  return "Welcome World";
  6  // it's another comment
  7   }
  8  }
  9  /

Java Source MYTESTCLASS creato

SQL>

Now look what happens with the same code but with an odd number of single quotes:

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MyTestClass AS
  2   public class MyTestClass {
  3   public static String welcome() {
  4  // it's a comment
  5  return "Welcome World";
  6   }
  7  }
  8  /
  9
 10
 11  /
 12
 13
 14  '
 15  /
Errore con inizio alla riga : 1 nel comando -
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MyTestClass AS
 public class MyTestClass {
 public static String welcome() {
// it's a comment
return "Welcome World";
 }
}
/


/


'
Report error -
ORA-29536: origine di formato errato: Encountered "/" at line 7, column 1.
Was expecting one of:
    <EOF>
    "#sql" ...
    "static" ...
    "abstract" ...
    "final" ...
    "private" ...
    "protected" ...
    "public" ...
    "strictfp" ...
    "class" ...
    "enum" ...
    "interface" ...
    "native" ...
    "synchronized" ...
    "transient" ...
    "volatile" ...
    ";" ...
    "@" ...
    "\\u001a" ...

29536. 00000 -  "badly formed source: %s"
*Cause:    An attempt was made to create a Java source object with text
           that could not be parsed adequately to determine the class(es)
           defined by it.
*Action:   Correct errors in source.
SQL>

Note how the terminator character on line 8 is ignored. Then on line 11 it is ignored a second time (I could have hit it an arbitrary numner of times tho). On line 14 I added another quote "closing" the one opened in the java comment. Thus on line 15 SqlCl recognizes the last terminator character and creates a statement that is clearly invalid for the DB engine.

ALSO, note that the problem ONLY happens when dealing with single line java comments. Consider the following snippet as a proof of this behavior:

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MyTestClass AS
  2   public class MyTestClass {
  3   public static String welcome() {
  4   /*
  5  // it's a comment
  6   */
  7  return "Welcome World";
  8   }
  9  }
 10  /

Java Source MYTESTCLASS creato

SQL>

Vincenzo.

Where can I find the build_spec.yaml and terraform files.
There repo where I can find it?

Thanks,
Avraham

In README.ms file the link for the download page wrongly points to:
https://github.com/oracle/oracle-db-tools/blob/master/sqlcl
even it its text is
https://www.oracle.com/tools/downloads/sqlcl-downloads.html
File should be amended.

jansi is the cause

Running bin/sql on OpenBSD:

Exception in thread "main" java.lang.UnsatisfiedLinkError: Could not load library. Reasons: [no jansi in java.library.path]
        at org.fusesource.hawtjni.runtime.Library.doLoad(Library.java:182)
        at org.fusesource.hawtjni.runtime.Library.load(Library.java:140)
        at org.fusesource.jansi.internal.CLibrary.<clinit>(CLibrary.java:42)
        at org.fusesource.jansi.AnsiConsole.wrapOutputStream(AnsiConsole.java:48)
        at org.fusesource.jansi.AnsiConsole.<clinit>(AnsiConsole.java:38)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:435)

running manually

sqlcl -debug expands to:

java -Djava.awt.headless=true \
-Dapple.awt.UIElement=true \
-Djava.library.path=/usr/local/lib:/home/regular/sqlcl/lib \
-verbose \
-Xss10M \
-client \
-cp /home/user/sqlcl/lib/dbtools-sqlcl.jar:\
/home/user/sqlcl/lib/drivers/*:\
/home/user/sqlcl/lib/ext/*:\
/home/user/sqldev/sqldeveloper/rdbms/jlib/xdb6.jar:\
/home/user/sqldev/sqldeveloper/jdbc/lib/ojdbc8.jar:\
/home/user/sqldev/sqldeveloper/jlib/orai18n-utility.jar:\
/home/user/sqldev/sqldeveloper/jlib/orai18n-mapping.jar:\
/home/user/sqldev/sqldeveloper/jlib/orai18n.jar:\
/home/user/sqldev/sqldeveloper/modules/oracle.xdk/xmlparserv2.jar:\
/home/user/sqlcl/lib/javax.json.jar:\
/home/user/sqlcl/lib/javax.json-api.jar:\
/home/user/sqlcl/lib/xmlparserv2.jar:\
/home/user/sqlcl/lib/commons-logging.jar:\
/home/user/sqlcl/lib/orai18n.jar:\
/home/user/sqlcl/lib/xmlparserv2-sans-jaxp-services.jar:\
/home/user/sqlcl/lib/orai18n-collation.jar:\
/home/user/sqlcl/lib/jackson-databind.jar:\
/home/user/sqlcl/lib/xdb6.jar:\
/home/user/sqlcl/lib/jackson-core.jar:\
/home/user/sqlcl/lib/stringtemplate.jar:\
/home/user/sqlcl/lib/oraclepki.jar:\
/home/user/sqlcl/lib/jackson-annotations.jar:\
/home/user/sqlcl/lib/osdt_core.jar:\
/home/user/sqlcl/lib/httpmime.jar:\
/home/user/sqlcl/lib/osdt_cert.jar:\
/home/user/sqlcl/lib/httpcore.jar:\
/home/user/sqlcl/lib/orajsoda.jar:\
/home/user/sqlcl/lib/httpclient.jar:\
/home/user/sqlcl/lib/orai18n-utility.jar:\
/home/user/sqlcl/lib/ojdbc8.jar:\
/home/user/sqlcl/lib/dbtools-sqlcl.jar:\
/home/user/sqlcl/lib/dbtools-net.jar:\
/home/user/sqlcl/lib/dbtools-http.jar:\
/home/user/sqlcl/lib/dbtools-common.jar:\
/home/user/sqlcl/lib/orai18n-servlet.jar:\
/home/user/sqlcl/lib/jsch.jar:\
/home/user/sqlcl/lib/commons-codec.jar:\
/home/user/sqlcl/lib/jdbcrest.jar:\
/home/user/sqlcl/lib/orai18n-mapping.jar:\
/home/user/sqlcl/lib/jline.jar:\
/home/user/sqlcl/lib/antlr-runtime.jar:\
/home/user/sqlcl/lib/jansi.jar \
oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli

The whole output this command produces (stout, stderr) is here.

I have devel/jdk installed.

I can see jansi.jar and jline.jar present in lib/.

I have added -verbose and changed .. to sqldeveloper which is also installed in home directory under sqldev/, but i don't know if it is helping (if sqlcl requires sqldeveloper).

I'd like to create the following alias in SQLcl: alias congiffy=connect giffy/giffy@localhost:32122/orclpdb514.localdomain This is a shortcut for a connection that I use. The problem when running it is that :32122 is considered a bind variable and calling it gives me Error: Alias with binds: not enough binds supplied at run time.

It would be nice to have some way of escaping colons so they're not bind variables. If this could be done in the xml file for aliases I'm ok with that too.

_Note: I realize the security implications of storing connection strings with passwords as an alias. In this case, it's for an local docker DB _

cc: @krisrice

I have two separate environments - APEX and DB. One was created as a clone of the other one. Recently, I started using SQLcl to export and import page changes from Dev to Prod. I used it for one deployment, but now it fails for the second deployment.

If I try to export a page and then import it, I see the page got updated in the Admin's list of pages, but if I try to access it, the page loader hangs indefinitely. If I try to access the page from a client session, then a message is logged:
"Page template ... does not exist. Check your theme configuration!"

The page template identifier is MINIMAL_NO_NAVIGATION, part of '42. Universal Theme'.

Note: This works in sql plus.

From sqlcl:

sql> @dir1/script1.sql

script1.sql:
@@script2.sql

script2.sql:
define myVar = x
@@dir1/&myVar._y

SP2-0310: Unable to open file: "dir1/x.sql"

Hi.

Is it possible to get expSavedReports expPubReports options for APEX EXPORT command?

Appreciate for help!

On OSX at least any parameters passed via "dbtools.system_http_proxy=" simply don't work.
This includes setting environment variables such as http_proxy or using a proxy.pac file.

sql sysman/password@a_database
Jan 25, 2024 4:30:56 PM oracle.dbtools.raptor.proxy.ProxyAutoConfig findProxyForURL
INFO: findProxyForURL(http://www.internic.net) returns null.


SQLcl: Release 23.3 Production on Thu Jan 25 16:31:26 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

  USER          = sysman
  URL           = jdbc:oracle:thin:@a_database
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=Wwwwwwwwwwwwwwwwwwwwww==)
Username? (RETRYING) ('sysman/*********@a_database'?)

A manual override does work ...

"${JAVA}" -DsocksProxyHost=localhost -DsocksProxyPort=1184 ${CUSTOM_JDBC} ${CYGWIN} "${APP_VM_OPTS[@]}" -client ${SQLCL_DEBUG} -cp "${CPLIST}" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli "$@"

Hi. I didn't know where else to report this.

Your sql script in sqlcl version 21.4.1.17.1458 has a bug on line 610 that prevents me from using it. It's a simple fix. The code has $JAVA to run the java version, but the path to my java version has a space in it. This causes it to interpret the $JAVA variable as multiple arguments. Instead quotes should be added to make the line say "$JAVA". I did this fix myself and it fixed the issue.

Hi,

Is there a specific version of apex/database that the "apex export" within sqlcl or the apxexp.js in this repo works with? Or are there variants per version?

I've tried with the following configuration and the export fails with the error below.
sqlcl - Oracle SQLDeveloper Command-Line (SQLcl) version: 17.4.0.354.2224
apex - Application Express 4.2.6.00.03
db - Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SYSTEM@localhost:1521/xe > apex export 146
Exporting application 146APEX: Unable to process request. Verify specified options:
ORA-06550: line 2, column 12:
PLS-00306: wrong number or types of arguments in call to 'EXPORT_APPLICATION_TO_CLOB'
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored

Any there any pointers to have this working with the component versions listed above. We are using Oracle 11gR2 EE in production.

sqlcl version:
19.2.1.246.1707

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=xnQiEstFRNCH3EAi7zdv3g==)

The following is from the listener.ora file
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

and this is from tsanames.ora file

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=xnQiEstFRNCH3EAi7zdv3g==)

How to resolve the issue. Can anybody help resolve this.

Is there a plan for supporting vi keybindings? I have a lot of muscle-memory for vi keybindings, and I'm eager to leverage them with sqlcl. I know sqlplus doesn't support vi keybindings either, but using rlwrap they mostly work. However, sqlcl doesn't seem to support vi keybindings, nor does rlwrap help it.

https://github.com/oracle/oracle-db-tools/blob/0ba494dc82cbb555235d15f1a2b08b2508aefd92/sqlcl/java/src/RunMyScript.java#L44C5-L44C56

We should not patch the result of execution by deleting some text because we don't know if this a real text generated by script or an artefact of ScriptRunnerContext class. To suppress the force_print text at the and of each command execution report it is much better to do the following:

ctx.putProperty('script.runner.using.systemout', True)

I propose to add this line just after creation of context

		    ScriptRunnerContext ctx = new ScriptRunnerContext();
		    ctx.setBaseConnection(conn);
                    ctx.putProperty('script.runner.using.systemout', Boolean.TRUE);  /// <--- here

and remove line

results = results.replaceAll(" force_print\n", "");

This change should be applied to both sqlcl/java/src/ParseScriptRunOneAtATime.java and sqlcl/java/src/RunMyScript.java examples.

I tried customAccept.sql and I have got SQL Error: ORA-00936

SQLcl: Release 20.4 Production on Út Bře 16 09:56:05 2021

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Last Successful login time: Út Bře 16 2021 09:56:11 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

The answer to the ultimate question? 42

running: select ^ANSWER from dual

Error starting at line : 26 File @ H:\Data\sql_developer\sql\answer.sql
In command -
select ^ANSWER from dual
Error at Command Line : 26 Column : 8 File @ H:\Data\sql_developer\sql\answer.sql
Error report -
SQL Error: ORA-00936: chybí výraz
00936. 00000 - "missing expression"
*Cause:
*Action:

SQL>

When you generate a liquibase changeset for a table which ddl code has a create table and a following alter table statement, and you would like to update your target stage, the update command fails with a ORA-00922.
When you check the sql-commands liquibase executed you can see that after the create table statement a semicolon is missing to sperate the alter table and the create table statement.

SQLcl version: 17.4.0.354.2224 and 18.2 tested. The result is the same.
I used SQLcl to execute an SQL which output a column whose type is clob and contains string longer than 80. I found that the column's output in csv is OK, but only output the first 80 characters in json.

Hi,

Unfortunately I don't have access to the server running the Oracle RDBMS. But I need to copy files from/to my local server/laptop into a Directory configured in Oracle for expdp/impdp purposes. I understand I somehow can use UTL_FILE.PUT_FILE() but don't know the details. I'd guess that SQLcl scripts could somehow be used. May be you can help me with the script? Additional, since impdp/expdp writes log file into the Oracle directory, I'd like to print them locally (== copy | less)

Thanks a lot for your help
/Juergen

Dear Oracle,
In our company we use the oracle repo yum.oracle.com to install oracle instant client versions for sqlplus and tools. We would like to use sqlcl too. It is possible that you upload or integrate the sqlcl tools in oracle instant client or somewhere else that we can download sqlcl from yum.oracle.com too? Currently it is a manuell process to download the sources.

I have create script test.sql:

SHOW USER
SCRIPT
sqlcl.setStmt(
    "CONNECT test/test@db:1521/BSS;\n" +
    "SHOW USER;\n"
); 
sqlcl.run();
/
PROPMT AFTER SETSTMT;
SHOW USER;

I connect to database via sqlcl:

sqlcl adam/adam@db:1521/BSS

and run this scrip

@test.sql

Output from script

USER is "ADAM"
Connected.
USER is "TEST"
AFTER SETSTMT
USER is "NULL"

Why after 'sqlcl.run();' connection to database is closed?

I am (still) looking for a way to run a local executable from SQL. Will anything here help with that or could it be added?

I'm thrilled by the potentials shown by the java examples. However, no matter what tweaks I try to pull on ScriptRunnerContext, ScriptRunner or ScriptExecutor I do not seem to be able to enable dbms_output awareness of runner/executor classes.

In a nutshell:

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world');
END;

correctly returns the localized version of "PL/SQL procedure successfully completed" but nothing else. I would like to be able to also capture the string "hello world" in my own code.

I looked at decompiled bytecode and ScriptRunnerContext.m_dbmsOutput does not seem to be initialized anywhere in that class. However I'm not at all certain that this is the right place to look at.

Hi,

in my js script I validate the args passed. In case of missing args I'd like to print a "usage: ..." message and then exit. Both I couldn't figure out:

1. what is the name of script file I'm executing? In case it is inline, the name of file it is inlined in. If that is interactive it may be empty or something
2. How can I exit sqlcl from within a js script?

thanks a lot
Juergen

I ran into a very strange phenomenon:

sql test/[email protected]:1521/service1
Fails as expected with a I/O error (network could not establish connection)

But adding an exclamation mark to the password
sql test/[email protected]:1521/service1
fails with Invalid Oracle URL specified / Unknown host specified.

Even more strange:
sql test/[email protected]:1521/service1
fails with network adapter could not be established exception again.

So the combination of servername with dash and password with exclamation mark leads to invalid URL?

Hi,

In this bit of the parse script example:

while ( (  cmd = parser.next() ) != null ) {
    // do something fancy based on a cmd
    sr.run(cmd);
    // check success/failure of the command
    String errMsg = (String) ctx.getProperty(ctx.ERR_MESSAGE);
    if ( errMsg != null   ){
    // react to a failure
        System.out.println("**FAILURE**" + errMsg);
    }				
}

Under what circumstances does the ctx.getProperty(ctx.ERR_MESSAGE); get set. I have tried a couple of scripts that should result in an error but it is always null.

David.

Using apxexp with -skipExportDate option results in a file with all blank lines except the export date.

Line 270 is:
if ( ! ( this.options.skipDate && line.indexOf("-- Date and Time:") != 0 ) ) {
should be:
if ( ! ( this.options.skipDate && line.indexOf("-- Date and Time:") == 0 ) ) {

"BREAK ON" broken in SQLcl binary and API

Something to do with CHAR() types in some situations but not others?

Any suggestions?

(Works properly in SQLPlus)

1. See test SQL: breaktest.sql
   Run in SQLPlus and SQLcl (binary) against same schema

2. Compare outputs
   output_sqlcl.txt
   output_sqlplus.txt

3. My environment
   JDK 1.8.0_291
   SQLcl 21.1.1
   SQL*Plus: Release 12.2.0.1.0 Production

github_issue37_20210623.zip

In sqlcl I entered the following commands:

var test blob
begin select file_content into :test from MIG_PROJECT_PROJECT_FILES where rownum = 1; end;
/

This load database blob into the bind variable :test

I have created javascript file blob2file.js with the following code:

var blobvar = ctx.getVarMap().get(args[1].toUpperCase()).getValue();
var blobStream = blobvar.getBinaryStream(1);
var path = java.nio.file.FileSystems.getDefault().getPath(args[2]);
java.nio.file.Files.copy(blobStream ,path);

I run the javascript with the following command:

script test testoutput

This result in the error "javax.script.ScriptException: TypeError: blobvar.getBinaryStream is not a function in <eval> at line number 2"

So I think the getValue() dit not provide a Blob variable.

How should I fetch a bind variable from the Blob type.

Please include object grants in the DDL SQLcl command

Trying ORDS 19.1 in front of Oracle EE 12.1.0.2 with rest-sql/demo-all-in-one.html samples.
Got this in reply:
{
"env": {
"defaultTimeZone": "GMT"
},
"items": [
{
"statementId": 1,
"statementType": "sqlplus",
"statementPos": {
"startLine": 1,
"endLine": 1
},
"statementText": "exec ADHOC_PROC_SIMPLE_OUT_CURSOR(?)",
"response": [
"\nError starting at line : 1 in command -\nBEGIN ADHOC_PROC_SIMPLE_OUT_CURSOR(:adhocbind1); END;\nError report -\nParameter Type Conflict\n"
],
"errorCode": 17012,
"errorLine": 1,
"errorColumn": 0,
"errorDetails": "Parameter Type Conflict",
"result": 0,
"binds": [
{
"index": 1,
"data_type": "CURSOR",
"mode": "out",
"result": null
}
]
}
]
}