Asking replacement value by oracle SQL Developer

Question

I have an insert query like this:

INSERT INTO CONSUMER
            (CONS_ID_NO,
             LOCATION_ID,
             AREA_CODE,
             CONS_NO,
             CAT_CODE,
             KEY_ID,
             CONS_NAME,
             ADDRESS1,
             ADDRESS2,
             ADDRESS3,
             ADDRESS4,
             PHONE,
             DATE_CONNECT,
             FLAT,
             NO_OF_FLATS,
             BULK,
             OPENING_BALANCE,
             BALANCE_DATE,
             ROUTE_CODE,
             ROUTE_ORDER,
             METER_NO,
             METER_FIXATION_DATE,
             METER_OWNER,
             METER_WORKING,
             DISCONNECT,
             ALERT,
             LAST_READING,
             LAST_READING_DATE,
             LAST_READING_STATUS,
             PIC_AMT,
             PIC_KL,
             PIC_FROM,
             SC,
             MR,
             ARREARS,
             AS_ON_DATE,
             SCHEDULE_NO,
             MAIN_TYPE,
             SUB_TYPE,
             BILL_UPTO,
             CONS_SERVICE,
             CONS_SERVICE_ARREAR_CLEARED,
             PART_CONSUMER,
             BILL_ID_NO,
             READING_POSTED,
             METER_DIGITS,
             LAST_ADJUSTED_READING,
             LAST_ADJUSTED_DATE,
             LAST_ADJUSTED_STATUS)
VALUES      ('2112104945',
             '141',
             'CPW',
             '5225',
             'D',
             1946415,
             'ASHRAF PERILACODE
 & NASEEHA.K',
             'PERILACODEKP 1X/617A',
             'DHARSAN
 NAGAR',
             'PEROORKADA',
             '--',
             NULL,
             To_timestamp('2002-03-25
 00:00:00.0', 'DD-MON-RR HH.MI.SSXFF
 AM'),
             'N',
             NULL,
             NULL,
             52,
             To_timestamp('2002-03-01 00:00:00.0', 'DD-MON-RR
 HH.MI.SSXFF AM'),
             811,
             73,
             '10075256',
             To_timestamp('2010-11-12
 00:00:00.0', 'DD-MON-RR HH.MI.SSXFF
 AM'),
             'O',
             'Y',
             'N',
             'V',
             102,
             To_timestamp('2011-06-25
 00:00:00.0', 'DD-MON-RR HH.MI.SSXFF
 AM'),
             'W',
             55,
             13,
             To_timestamp('2011-06-25 00:00:00.0', 'DD-MON-RR
 HH.MI.SSXFF AM'),
             2,
             0,
             334,
             To_timestamp('2011-06-28
 00:00:00.0', 'DD-MON-RR HH.MI.SSXFF
 AM'),
             NULL,
             NULL,
             NULL,
             To_timestamp('2011-05-31 00:00:00.0', 'DD-MON-RR
 HH.MI.SSXFF
 AM'),
             NULL,
             NULL,
             'N',
             '3489554',
             'Y',
             5,
             102,
             To_timestamp('2011-06-25
 00:00:00.0', 'DD-MON-RR HH.MI.SSXFF AM'),
             'A');

On executing this query on SQLDeveloper 3 (Oracle 11g), it is asking for the replacement value for &naseeha. How can this be prevented ?

It is also giving me an error

Error report:
SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

The above given query is a part of a SQL file which I imported from my previous installation of Oracle 11g using the SQL Developer (that we get when we install the Oracle 11g, which is version 1 I think).

All the insert queries is giving the same error (Number 2).

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

Referring to How to insert a string which contains an "&", this should work:
```
SET DEFINE OFF
```

Try replacing all occurrences of

'DD-MON-RR HH.MI.SSXFF AM'

with

'yyyy-mm-dd hh24:mi:ss.ff3'

edited May 23 '17 at 12:04

Community

1
1

answered May 07 '12 at 02:36

Andrew NS Yeow

341
2
8

`to_timestamp('25-03-2002 00:00:00.0','dd-mm-yyyy hh24:mi:ss.ff3')` – Andrew NS Yeow May 07 '12 at 05:36
Will the above work? I must admit I attempted to answer your question because it looked like issues I have faced before. I do not have in-depth knowledge of this topic actually. Just trying my best. :) – Andrew NS Yeow May 07 '12 at 05:43
Thanks @sathya. I am new here. Will do this corrected way of presenting links in future. :) – Andrew NS Yeow May 07 '12 at 05:49
@AndrewNSYeow you're welcome! Good to have you on [SO] and share your thoughts and answers – Sathyajith Bhat May 07 '12 at 05:49
1

Also, if you're querying, I'd try using `to_char(,'dd-mm-yyyy')` to format the date in `dd-mm-yyyy` format – Sathyajith Bhat May 07 '12 at 05:50
1

As you can see that date format(for example in script, it is '_2011-06-25 00:00:00.0_') is `yyyy-mm-dd` in the script. Hence I had to use tip from [find and replace with reordered date format in notepad++](http://stackoverflow.com/q/4331138/784929) and change the date format. Then I used your tip and things worked. _+1_ for that. – kaushik May 08 '12 at 01:58

Asking replacement value by oracle SQL Developer

1 Answers1