How to insert quotes and double quotes in Oracle8i table 2

[dejanj]

Dear All,

Does anyone know how to insert " (double quotes) and ' (single quotes ) into oracle8i table.

For example i 'v got a table :
CREATE TABLE "GCC"."SGCCSUSR"("ENCRYPTED" VARCHAR2(132) NOT NULL);

..the value i need to insert is

18571!"#$% !"AEOLR% !"Dhrou(Hlvxz'vcpi%&'(#$%&'("#$%&'("#$%&'(Dhrou(Ndwy&uioh$%&'(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/


How do i do this?

Regards
Dejan

 

[sem]

Double quotes chr(34) is an ordinary character. Single quote should be either doubled ('O''Briens') or used as chr(39) ('O'||chr(39)||'Briens')

Regards, Dima

 

[SantaMufasa]

Dejanj,

As Dima mentions, you don't need to worry about double quotes (&quot. Just type them into your string. Single quotes in Oracle literals are a nuisance. As an alternative to Dima's excellent method, choose some unused character in your encryption string to replace all the single quotes. Then translate the unused character into a single quote. In my example, below, I chose the "^" character to act, temporarily, as a single quote:

Insert into GCC.SGCCSUSR values
(translate('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz^vcpi%&^(#$%&^("#$%&^("#$%&^(Dhrou(Ndwy&uioh$%&^(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/','^',chr(39));

Try it and let us know how you like this method.

Dave
Sandy, Utah, USA @ 07:55 GMT, 00:55 Mountain Time

 

[dejanj]

Thanks guys,

Much appreciated for the tip. I am trying to create a script in SQL PLus for Oracle for that insert and adding ' to single quotes works but then Oracle SQL Plus complains saying that column is not long enough for the value to be inserted.

I am trying to use EXP feature once i create a record for the table and make user use IMP

Not sure yet...

Regards
Dejan

 

[sem]

This may mean that you add more quotes than you need

Can you provide the code you used? Single quotes must be double in string literals, not in strings as such. So, if you want to insert O'Briens to varchar2 field, the statement should be

insert into names values ('O''Briens')

Though, if some variable, say :name, already holds this value (regardless on how it was initially assigned), you shouldn't change it:

insert into names values name)

insert into names select name from old_names

Regards, Dima

 

[dejanj]

Dima, SantaMufasa

Thanks for your replies. Really appreciate it.

I found out this strange behaviour!!
THis is Oracle8i. I am using Win2000 as Oracle Client and Oracle8i database is on another PC on the network.


WHen i use SQL PLus worksheet to insert the record in table

INSERT
INTO GCC.SGCCSUSR
( ENCRYPTED )
VALUES
('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz''vcpi%&''(#$%&''("#$%&''("#$%&''(Dhrou(Ndwy&uioh$%&''(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/');

..i got error message


Enter value for uioh: old 5: ('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz''vcpi%&''(#$%&''("#$%&''("#$%&''(Dhrou(Ndwy&uioh$%&''(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/')
new 5: ('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz''vcpi%&''(#$%&''("#$%&''("#$%&''(Dhrou(NdwyOEM_sqlplus_input_finished$%&''(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/')
INSERT
*
ERROR at line 1:
ORA-01401: inserted value too large for column

But when i log in DBA studio and then type in exactly the same command using Free SQL mode

INSERT
INTO GCC.SGCCSUSR
( ENCRYPTED )
VALUES
('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz''vcpi%&''(#$%&''("#$%&''("#$%&''(Dhrou(Ndwy&uioh$%&''(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/')

..i got Table is updated sucessfully.


As you can see i used extra ' to add at the end of each ' in string.

What is happening?
Any idea. Can you guys test and tell me what do you get.

Thanks

Dejan

 

[SantaMufasa]

Dejan,

Your error resulted from the "&" character that appears in your encryption string. The "&" character is the default symbol for the "define literal" functionality in SQL*Plus. The way you can avoid the problem is to issue this command at the SQL*Plus prompt before trying your INSERT:

SET DEFINE x

...where x is some obscure character that will never appear in your encrypted insert string.

Let us know if that resolves this particular problem for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:25 (03Dec03) GMT, 01:25 (03Dec03) Mountain Time)

 

[dejanj]

Thanks Mufasa,

I did in SQL pLus

set define '^';
INSERT
INTO GCC.SGCCSUSR
( ENCRYPTED )
VALUES
('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz''vcpi%&''(#$%&''("#$%&''("#$%&''(Dhrou(Ndwy&uioh$%&''(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/');

..and i got 1 row created but when i inspect in DBA Studio
record was not there!!

I tried from my Development environment to run the remote view on Oracle GCC.SGCCSUSR table and the result was no records.

I don't know what i am doing wrong.
Thanks for your help.

regards
Dejan

 

[SantaMufasa]

Dejan,

Since you didn't mention it, I thought I'd better ask: Did you issue a "COMMIT;" statement following your insert?

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:16 (04Dec03) GMT, 22:16 (03Dec03) Mountain Time)

 

[sem]

You may also

SET DEFINE OFF

if it's impossible to find "free" character

Regards, Dima

http://seminihin.narod.ru

 

[dejanj]

Thanks Dima , Mufasa

Since in SQL PLus SET COMMIT is by default OFF i needed it.
so

set define '^';
INSERT
INTO GCC.SGCCSUSR
( ENCRYPTED )
VALUES
('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz''vcpi%&''(#$%&''("#$%&''("#$%&''(Dhrou(Ndwy&uioh$%&''(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/');
commit;

does the job no problem.

Regards
Dejan