CODEWITHSUNDEEP
sqloracle-databasesql-insertsqlplusquotes
Kevin Cruijssen
Kevin Cruijssen

Reputation: 9336

SQL Plus : insert into numeric values with single quotes around them gives error

A colleague of mine has created an export with INSERT INTO queries using Oracle SQL Developer. This export has put single quotes around every value, not just VARCHARs.

So let's say we have a simple table containing these columns (among others):

SOME_TEXT      VARCHAR2(256 BYTE)
SOME_NUMBER    NUMBER(15,2)

The export has an INSERT INTO like this:

Insert into MY_TABLE (SOME_TEXT,SOME_NUMBER) values ('test text','123,45');

You may note two things about the decimal value ('123,45'):

The second is easy to fix, I can insert it in Oracle SQL Developer like this:

-- dot as thousand separator, comma as decimal point
alter SESSION set NLS_NUMERIC_CHARACTERS = '.,';

Insert into MY_TABLE (SOME_TEXT,SOME_NUMBER) values ('test text','123,45');
-- All other insert queries

alter SESSION set NLS_NUMERIC_CHARACTERS = ',.';

Which works fine.
However, some of the .sql files are very big, and can't be opened in SQL Developer. So instead I want to use SQL Plus to execute the .sql files containing the insert-statements.

However, even when NLS_NUMERIC_CHARACTERS is changed, I'm currently getting the following error in SQL Plus due to the single quotes around the numeric value:

SQL> @"C:\my\path\test_insert_statement.sql"
values ('test text','123,45')
                    *
ERROR at line 2:
ORA-01722: invalid number

I see two possible solutions, but I don't know if either is possible:

  1. Is there a way to allow single quotes around numeric values in SQL Plus for Oracle databases?
  2. Or is there a way to export a database without single quotes for numeric values in Oracle SQL Developer (then I can let my colleague generate another .sql file)?

Upvotes: 0

Views: 2809

Answers (1)

atokpas
atokpas

Reputation: 3351

You can use single quotes around a numeric value. The error has occurred due to the decimal point character. All you need to change the decimal point character which can be done, as you have shown, as given below.

SQL> create table tbl1(
SOME_TEXT      VARCHAR2(256 BYTE),
SOME_NUMBER    NUMBER(15,2)
);  2    3    4  

Table created.

SQL> alter SESSION set NLS_NUMERIC_CHARACTERS = ',.';

Session altered.

SQL> Insert into tbl1 (SOME_TEXT,SOME_NUMBER) values ('test text','123,45');

1 row created.

SQL> select * from tbl1;

SOME_TEXT        SOME_NUMBER
-------------    -----------
test text        123,45

Upvotes: 3

Related Questions