Difference between sqlplus and sqldeveloper

oracle-sqldeveloperoracle10gsqlplus

I have a sql script file that creates tables and inserts data.

The server runs on Oracle 10g.

When I use sqldeveloper (remote computer) and I run my script everything is perfect. But when I go on the server and I run the same script on the same database, schema, user with sqlplus some error appears (integrity, unique …) ?

What's the problem, why didn't I have problem on sqldeveloper but did have some on sqlplus? What can cause that?

Best Answer

Ok i found the problems:

The first was the comment (/* comment */ are not supported or -- comment at the end line)
I had some anonymous block on my script and I put some / at the begin (by error) and the end. But on sqlplus, the / at the begin, re-execute the previous query.
After removing the comment i got blank line on some create table and sqlplus have problem with blank line inside the create table.

Related Solutions

Sql – When do I need to use a semicolon vs a slash in Oracle SQL

I know this is an old thread, but I just stumbled upon it and I feel this has not been explained completely.

There is a huge difference in SQL*Plus between the meaning of a / and a ; because they work differently.

The ; ends a SQL statement, whereas the / executes whatever is in the current "buffer". So when you use a ; and a / the statement is actually executed twice.

You can easily see that using a / after running a statement:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:37:20 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> drop table foo;

Table dropped.

SQL> /
drop table foo
           *
ERROR at line 1:
ORA-00942: table or view does not exist

In this case one actually notices the error.

But assuming there is a SQL script like this:

drop table foo;
/

And this is run from within SQL*Plus then this will be very confusing:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:38:05 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> @drop

Table dropped.

drop table foo
           *
ERROR at line 1:
ORA-00942: table or view does not exist

The / is mainly required in order to run statements that have embedded ; like CREATE PROCEDURE,CREATE FUNCTION,CREATE PACKAGE statements and for any BEGIN...END blocks.

Sql – Problem running oracle script from command line using sqlplus

You need to either put an exit at the end of the script, or run it as sqlplus username/password@server/dbname < Setup.sql (i.e. redirected input, < instead of @). You can check if that's the issue by just typing 'exit' in the hanging session.

If it is really hanging, have you committed or rolled back the execution from Developer?

Best Answer

Related Solutions

Sql – When do I need to use a semicolon vs a slash in Oracle SQL

Sql – Problem running oracle script from command line using sqlplus

Related Topic