This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,555 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

cannot load double quote(") character string

Setsu FUJITA
Setsu FUJITA Member Posts: 1 Red Ribbon
edited May 6, 2016 9:30AM in SQLcl

i have a csv file which made by sqlcl select /*csv*/ from test.

test.csv

 "FIRST","SECOND"
"""quote"" cannot load","second column"

load test test.csv command produces sql below

 INSERT INTO TEST(FIRST,SECOND)
VALUES ('""quote"" cannot load','second column');

expects

INSERT INTO TEST(FIRST,SECOND)
VALUES ('"quote" cannot load','second column');

Answers

  • Craig Elliott
    Craig Elliott Member Posts: 21 Blue Ribbon
    edited May 6, 2016 9:30AM

    This appears to be a bug.

    create table test(first varchar(40), second varchar(40));

    Table test created.


    INSERT INTO TEST(FIRST,SECOND) VALUES ('"quote" cannot load','second column');

    1 row inserted.

    commit;

    Commit complete.

    set sqlformat

    select * from test

    "quote" cannot load                      second column                         

    set sqlformat csv

    select * from test

    "FIRST","SECOND"

    """quote"" cannot load","second column"

    spool test.csv

    select * from test;

    "FIRST","SECOND"

    """quote"" cannot load","second column"

    spool off

    load test test.csv

    select * from test

    "FIRST","SECOND"

    """quote"" cannot load","second column"

    """""quote"""" cannot load","second column"

    set sqlformat

    select * from test

    "quote" cannot load                      second column                         

    ""quote"" cannot load                    second column