0

While running the insert script in sql*plus, space is removing. Can you please help us is there any plus command available in Oracle to not remove space at end of the line.

Ex: After the text 'Ameerpet,' we have space in the insert script. I have run this command through sql*plus it was success but in output space got removed.

insert into emp values(1,'<p style="font-weight: bold; margin-top: 10px;">Mithrivanam, Ameerpet, 
Hyderabad, Telangana, 500018</p>');

Output:

<p style="font-weight: bold; margin-top: 10px;">Mithrivanam, Ameerpet,
Hyderabad, Telangana, 500018</p>
1

If you really want to have that specific space and line break, you should not rely on enviroment settings, but explicitly specify it:

insert into emp values(1,'<p style="font-weight: bold; margin-top: 10px;">Mithrivanam, Ameerpet, ' || chr(10) ||
'Hyderabad, Telangana, 500018</p>');
| improve this answer | |
0

Well there must be an 'Enter' after 'Ameerpet, ' which has divided the query into two lines thats causing the problem.
Use the following

insert into tbltest values(1,'Mithrivanam, Ameerpet, Hyderabad, Telangana, 00018

');

Instead of

insert into emp values(1,'Mithrivanam, Ameerpet, 
Hyderabad, Telangana, 500018

');

Test scenario:
I have inserted one with an Enter and another without enter(one line) and got the following output.

SQL> create table tbltest(id number, code varchar2(200));

Table created.


SQL> desc tbltest;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER
 CODE                           VARCHAR2(200)


SQL> insert into tbltest values(1,'<p style="font-weight: bold; margin-top: 10px;">Mithrivanam,  Ameerpet, 
Hyderabad, Telangana, 500018</p>'); 2 <<<here, this '2' indicates we have got two lines.

1 row created.

SQL> select * from tbltest;

<p style="font-weight: bold; margin-top: 10px;">Mithrivanam,  Ameerpet,
Hyderabad, Telangana, 500018</p>


    ID
----------
CODE
--------------------------------------------------------------------------------
     1
<p style="font-weight: bold; margin-top: 10px;">Mithrivanam,  Ameerpet,<<<here not space
Hyderabad, Telangana, 500018</p>

SQL> insert into tbltest values(1,'<p style="font-weight: bold; margin-top: 10px;">Mithrivanam,  Ameerpet,   Hyderabad, Telangana, 00018</p>');

1 row created.

SQL> select * from tbltest;

    ID
----------
CODE
--------------------------------------------------------------------------------
     1
<p style="font-weight: bold; margin-top: 10px;">Mithrivanam,  Ameerpet,   Hydera
bad, Telangana, 00018</p>

Solution: Don't break that line of text(string of html tags) into two lines.

| improve this answer | |
  • Thanks JSapkota for the response. We will not change the data type, currently the data type of the column is clob. Can you please let us know if any alternative/ – Surendra Mar 2 '16 at 13:19
  • @Surendra and i have tested it with clob its working perfectly. Just dont press enter after 'Ameerpet, ' meaning make that html code of one line. OR if you need the space explicitly with the line breakbreak Balazs Papp's answer would be better. – JSapkota Mar 2 '16 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.