5

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?

Ruskin
  • 5,721
  • 4
  • 45
  • 62
La Chamelle
  • 2,927
  • 4
  • 36
  • 54

5 Answers5

4

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.
La Chamelle
  • 2,927
  • 4
  • 36
  • 54
  • 1
    I had the same problem. I also had some statements ending in a semi-colon and then followed by a slash (\). SQLPlus would re-run the statement, but SQL Developer would not. – Adrian Brown Sep 12 '12 at 03:05
  • 1
    it can also help adding a comment to each table, so that the / generated by SQLDeveloper in the export, will be preceding a "COMMENT ON TABLE" statement rather than a CREATE TABLE statement, so no error is generated when the command is executed the second time. Any way it's all very sad. – Pierluigi Vernetto Sep 01 '14 at 08:47
  • check also http://stackoverflow.com/questions/1079949/sql-the-semicolon-or-the-slash . you can mitigate the issue using SQLDeveloper 4 and playing with the "terminator" option – Pierluigi Vernetto Sep 01 '14 at 09:13
1

Problems I have encountered in a similar position:

  • Any BEGIN ... END; outer blocks must be followed with a / line.
  • Semicolons cannot have anything after them
    • this is good;
    • this is bad; -- comment
    • this is bad;;
  • Sql commands (select, insert etc) cannot have empty lines in the middle
    • SELECT 'this is good' FROM Dual
    • SELECT 'this is bad' [empty line with no text] FROM Dual

Thanks for the heads-up on the /* multi-line comments */ - I've added that to my test script.

Ruskin
  • 5,721
  • 4
  • 45
  • 62
1

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 ...) ?

It would seem like your script handles creation and insertion, but not deletion of existing data. Without having a look at your script, there's not way to tell this, but I can simulate the same with my test data.

test.sql:

create table parts ( id number primary key, 
                     description varchar2(240)
                   )
/

insert into parts values(1,'Keyboard');
insert into parts values(2,'Mouse');
insert into parts values(3,'Monitor');

Run the script in SQL Developer:

Run in SQL*Plus - same user, schema, database:

SQL> @/home/oracle/Desktop/test.sql
create table parts ( id number primary key,
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


insert into parts values(1,'Keyboard')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C0016596) violated


insert into parts values(2,'Mouse')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C0016596) violated


insert into parts values(3,'Monitor')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C0016596) violated
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
0

Just for completeness, its quite possible that SQL Developer and sqlplus run with different nls session parameters. To check, run this command in sqlplus and SQL Developer and compare the results:

SELECT * FROM nls_session_parameters;

In case there is some difference you can set the nls_session_parameters you want via something like:

ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/RR';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD/MM/RR HH24:MI:SSXFF';
Uphill_ What '1
  • 683
  • 6
  • 15
0

Another possible difference:

There is a session parameter called 'NLS_DATE_FORMAT' that determines the format to use to display dates when selected without a TO_CHAR() call.

At my place of work, I believe the default NLS_DATE_FORMAT is set in the database to be 'DD-MM-YYYY', but the default in SQL developer seems to be 'DD-MON-YY'.

Therefore, the following will work for our SQLPlus users, but not SQL Developer users, as the date will first be automatically converted to a string using NLS_DATE_FORMAT before being parsed with the format passed into to_date().

select to_date(sysdate, 'DD-MM-YYYY') from DUAL;

If this is the case, SQLDeveloper will give the following error:

ORA-01858: a non-numeric character was found where a numeric was expected

01858. 00000 - "a non-numeric character was found where a numeric was expected"

*Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.

*Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

Maaark
  • 297
  • 6
  • 10
  • Maaark, sorry that your point is incorrect. I tried to execute the statement in SQLPlus and SQL Developer (19.1.0), no errors observed. – sandy v Aug 22 '19 at 16:17
  • You're right. I've updated the answer to reflect my current understanding of the issue. – Maaark Aug 28 '19 at 09:22