Sql*Plus Does Not Execute SQL Scripts That SQL Developer Does

SQL*Plus does not execute SQL scripts that SQL Developer does

Remove the empty lines.

In sqlplus an empty line means stop previous statement and start a new one.

or you can set blank lines:

set sqlbl on

SQL*Plus and SQL Developer

"The funny thing is when I paste either one of the two scripts in SQL Developer it executes the script perfectly fine."

You have shown only one script. However the one you have posted lacks a trailing /. SQLPlus uses a slash to indicate the end of an PL/SQL executable statement. So, assuming this is not just a posting error, you have two scripts without slashes at the end of the PL/SQL statement. This means the SQLPlus engine can't separate them, and that could cause SQL Plus to hurl an ora-00900 exception.

Of course if you were running these scripts manually you would see SQL Plus hang, waiting for you to type in the terminating slash. I presume the Java error stack means you're running the scripts through some form of build runner.

IDEs such as SQL Developer will run an individual script without a trailing slash because they handle it; by highlighting some code and pressing [F8] they can figure out the statement and simulate the trailing slash.

declare works with sqlplus and sql developer but why not with sql navigator?

It is not declare, but variable.

SQL Plus is Oracle's command line tool that allows you to access an Oracle database. SQL Developer is Oracle's GUI tool; as such, it is capable of running number of SQL Plus commands.

However, that's not the case with other tools - they work well with pure SQL or PL/SQL, but not with SQL*Plus commands.

Therefore, remove the first two lines and execute the rest. It should work, though - not the way you'd want it to.

Does SQLDeveloper support executing scripts?

To run scripts in SQL Developer:

@"\Path\Scriptname.sql"

(You only need the quotes if there are any spaces)

You can set a default Path: Tools menu > Preferences > Database > Worksheet > Select default path to look for scripts

Oracle SQL Developer: run script, continue on error

SqlDeveloper by default stops when it finds an error. You can change that behaviour using this:

whenever sqlerror continue;
drop sequence my_seq;  -- sequence may not exist yet, in case of error continue
-- then we want to stop in error
whenever sqlerror exit failure;
create sequence my_seq
  start with 1
increment by 1
    minvalue 1
    maxvalue 9999999999
       cache 100
       cycle;

Example

Explanation

• whenever sqlerror continue instructs to the script to continue no matter what error might happen
• whenever sqlerror exit failure instructs to the script to stop and exit the execution in case of an error.

Reference to the command and options available

whenever sqlerror

executing a .sql file in sql plus terminal

If your filename is myQueries.sql, just type

SQL>@/path/to/my/query/myQueries.sql
SQL>/

---

Related Topics