This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,583 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

SQLcl - alias causes ScriptParser error, or user error?

Craig Elliott
Craig Elliott Member Posts: 21 Blue Ribbon
edited Apr 22, 2016 3:46PM in SQLcl

I discovered the latest version of SQLcl , so remove the older and unzipped the new. Now when I try to run my alias commands they fail.

Anyone have an ideas?  Maybe I need to reinstall instantclient 11.2?

SQLcl  4.2.0.16.049.0842 RC on OSX 10.11.3

sho jdbc

-- Database Info --

Database Product Name: Oracle

Database Product Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

Database Major Version: 11

Database Minor Version: 2

-- Driver Info --

Driver Name: Oracle JDBC driver

Driver Version: 12.1.0.2.0

Driver Major Version: 12

Driver Minor Version: 1

Driver URL: jdbc:oracle:thin:

12:43:32 25-FEB-16 [email protected] SQL    >alias list

fred     

locks      - locks <USERNAME>

mysql    

objects  

plan     

plan2    

rs       

sessions 

tables     - tables <schema> - show tables from schema

tables2    - tables <schema> - show tables from schema

tablespace

temp     

whoexec  

SQL    >alias list fred

fred

----

select :one from dual

SQL    >fred sysdate

Feb 25, 2016 12:43:54 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run

SEVERE: oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)

java.lang.NullPointerException

    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)

    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.identifyEndOfStatement(ScriptParser.java:1092)

    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.next(ScriptParser.java:251)

    at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runAliases(SQLPLUS.java:3784)

    at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:113)

    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:311)

    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:218)

    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:295)

    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:199)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:293)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:303)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:880)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:352)

SQL    >sessions

Feb 25, 2016 12:48:42 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run

SEVERE: oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)

java.lang.NullPointerException

    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)

    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.identifyEndOfStatement(ScriptParser.java:1092)

    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.next(ScriptParser.java:251)

    at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runAliases(SQLPLUS.java:3784)

    at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:113)

    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:311)

    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:218)

    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:295)

    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:199)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:293)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:303)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:880)

    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:352)

Thanks!

Craig

Best Answer

Answers

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Feb 25, 2016 3:55PM

    The fred and sessions cases both work fine on Windows for 4.2.0.16.049.0842 RC.  Does your sqlcl/aliases.xml file contain something like...

    <?xml version = '1.0' encoding = 'UTF-8'?>
    <aliases>
       <alias name="fred">
          <description/>
          <queries>
             <query>
                <sql><![CDATA[select :one from dual]]></sql>
             </query>
          </queries>
       </alias>
    </aliases>      
    
  • Craig Elliott
    Craig Elliott Member Posts: 21 Blue Ribbon
    edited Feb 26, 2016 9:29AM

    Hi Gary,

    Yes, and just to be sure, I removed the aliases.xml file and had it automagically create a new empty one by sqlcl.

    SQL    >alias list

    locks    - locks <USERNAME>

    sessions

    tables   - tables <schema> - show tables from schema

    tables2  - tables <schema> - show tables from schema

    exit

    $     cat aliases.xml

    <?xml version = '1.0' encoding = 'UTF-8'?>

    <aliases/>

    SQL    >alias fred=select :one from dual;

    SQL    >alias list

    fred   

    locks    - locks <USERNAME>

    sessions

    tables   - tables <schema> - show tables from schema

    tables2  - tables <schema> - show tables from schema

    SQL    >fred 1

    Feb 26, 2016 8:26:51 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run

    SEVERE: oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)

    java.lang.NullPointerException

        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)

        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.identifyEndOfStatement(ScriptParser.java:1092)

        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.next(ScriptParser.java:251)

        at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runAliases(SQLPLUS.java:3784)

        at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:113)

        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:311)

        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:218)

        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:295)

        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:199)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:293)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:303)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:880)

        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:352)

    exit

    $     cat aliases.xml

    <?xml version = '1.0' encoding = 'UTF-8'?>

    <aliases>

       <alias name="fred">

          <description/>

          <queries>

             <query>

                <sql><![CDATA[select :one from dual]]></sql>

             </query>

          </queries>

       </alias>

    </aliases>

    I will reinstall my Instantclient and sqlcl and see if it gets resolved.

    Craig

  • Craig Elliott
    Craig Elliott Member Posts: 21 Blue Ribbon
    edited Feb 26, 2016 12:14PM

    Update, I reinstalled InstantClient 11.2 and SQLcl, and the error persists.

    What dependencies should I be looking for to make sure nothing else in my OSX environment is causing this issue?

    Thanks!

    Craig

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Feb 26, 2016 12:54PM

    There are very old discussions where a bug in unarchiver resulted in bad SQL Developer installations with missing class files:

    Oracle SQL Developer 2.1 EA1 and 1.5.5 not working on MacOS X Snow Leopard

    but it seems doubtful that is your issue.

    If you also have SQL Developer installed, you may wish to test and see if fred sysdate produces the same error in a worksheet.

  • Craig Elliott
    Craig Elliott Member Posts: 21 Blue Ribbon
    edited Feb 26, 2016 3:11PM

    The "alias" works in sql developer :

    Oracle SQL Developer 4.1.3.20

    Version 4.1.3.20

    Build MAIN-20.78

    IDE Version: 12.2.1.0.42.151001.0541

    Product ID: oracle.sqldeveloper

    Product Version: 12.2.0.20.78

    Oracle IDE    4.1.3.20.78

    Java(TM) Platform    1.8.0_45

    I will review that other Discussions and see if it helps.

    Thanks,

    Craig

  • Craig Elliott
    Craig Elliott Member Posts: 21 Blue Ribbon
    edited Feb 26, 2016 3:42PM

    So,, I decided to roll back to the previous version, one jar file at a time, until I found the one that resolves this.

    4647551 Feb 26 09:37 oracle.dbtools-common.jar -- does not work well = SQLcl  4.2.0.16.049.0842 RC on OSX 10.11.3

    4538654 Feb  4 08:21 oracle.dbtools-common.jar -- puuuurrfect = SQLcl  4.2.0.15.349.0706 RC on OSX 10.11.3

    So what changed in those jar files to cause this issue on OSX, or what in OSX is causing this issue to manifest with the new jar file?

    Any ideas, or should I revert to older version of sqlcl?

    Thanks!

    Craig

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Feb 26, 2016 7:11PM

    Active development continues on SQLcl. Many improvements / fixes occurred between mid-December and the recent February update, and still more since then.  If the loss of functionality caused by this regression is critical for you, then reverting to the prior update is all I can recommend at this time. There are several on our team using OS X, so it is likely someone will post here with advice or questions in the coming week.

    Craig Elliott
  • Craig Elliott
    Craig Elliott Member Posts: 21 Blue Ribbon
    edited Apr 21, 2016 5:00PM Answer ✓

    UPDATE - this is resolved in the latest release: sqlcl-4.2.0.16.112.0616