Help required with insert statement

[cba321]

Can anyone of you kindly help me with the following insert statement.

Insert into ESP_MET_DATA VALUES ( 1165,to_date(' 1/1/2001', 'mm/dd/YYYY'),to_date(' 2/1/2001', 'mm/dd/YYYY'), 1,LTRIM(RTRIM("

Greenville-S'burg, South Carolina &quot),
LTRIM(RTRIM("Greenville-S'burg &quot),
LTRIM(RTRIM('South Carolina ')), 50.0999985, 30., 813.797546,
7.5999999, 14.2535, 4.09000015, 59.9583321)
;



I am getting error with



LTRIM(RTRIM("

Greenville-S'burg, South Carolina &quot),
LTRIM(RTRIM("Greenville-S'burg &quot),



Regards,
cba321

 

[SantaMufasa]

Cba321,

In Oracle's infinite wisdom, its implementation of SQL does not support double quotes (&quot as a string delimiter. Therefore, as you currently constitute your command, you will receive an error because of double quotes you use within the RTRIM function. Additionally, Oracle SQL will have a problem with the specific literal you have that contracts Spartanburg to S'burg...it interprets the apostrophe as a string-delimiting single quote.

Out of curiosity, what is the genesis of the leading/trailing extraneous blank characters within your insert string? (Are you passing these string values from other software? If so, can't the other software do the left and right trimming before your create the INSERT statement?

 

[cba321]

SantaMufasa,

I am using Fortran and it does not allow variable characters in its character set which it reads. Hence i will have to use LTRIM and RTRIM. I need to delete that extra space using LTRIM and RTRIM. Yes i used single quotes but it gave me problem with S'burg. In order to overcome this problem i used double quotes but now i get the error "ORA-00972: identifier is too long". I am really stuck up with this problem and any suggestion to circumvent the problem will be of great help.

Regards,
Rajesh

 

[SantaMufasa]

Rajesh,

The FORTRAN answer gives me better insight now. To resolve your problem, you need ensure that whenever a single quote/apostrophe appears literally within an Oracle SQL single-quoted string, that two singe quotes appear instead (Example: S'burg should become S''burg). This causes Oracle to leave one single quote where you coded two. The Oracle rule is: where two successive single quotes appear within a quoted string, Oracle produce one single quote.

Since it appears that FORTRAN is generating your SQL code, your best solution is to cause your FORTRAN program to output two successive single quotes whenever it encounters an apostrophe.

Let me know if this resolves your issue.

Dave

 

[HereistheAnswer]

Moreover u can use trim only in place of ltrim rtrim

 

[issahar]

May be this will help(I have no Oracle in my station to test it):
Insert into ESP_MET_DATA VALUES ( 1165,to_date(' 1/1/2001', 'mm/dd/YYYY'),to_date(' 2/1/2001', 'mm/dd/YYYY'), 1,LTRIM(RTRIM('

Greenville-S''burg, South Carolina ')),
LTRIM(RTRIM('Greenville-S''burg ')),
LTRIM(RTRIM('South Carolina ')), 50.0999985, 30., 813.797546,
7.5999999, 14.2535, 4.09000015, 59.9583321)
;


Issahar Noam Gourfinkel
senior software engineer

Warecase

http://www.warecase.com

Israel

 

[zMonk]

you can also try somesing like

Insert into ESP_MET_DATA
VALUES ( 1165,
to_date(' 1/1/2001', 'mm/dd/YYYY'),
to_date(' 2/1/2001', 'mm/dd/YYYY'),
1,
LTRIM(RTRIM(' Greenville-S'||chr(39)||'burg, South Carolina ')) ....

Where chr(39) is ' ASCII charachter , for " use chr(34) . I find it more clean...

 

[cba321]

Dear All,

Thanks to all of you for the suggestions. This was very helpful and it solved my problem.

Regards,
Rajesh