Bug???

Hi
We are parsing a LOT of documents using perl scripts and them loading them into the database. We are running into a problem when we have a SQL statement that looks like this:

insert into textbooks
values
(33, 1, 1, ltrim(rtrim('

    Intermediate Accounting, Chasteen, Flaherty, and O''Conner;     Random House current edition or comparable intermediate     accounting textbook.

')), NULL, NULL, NULL, NULL);
commit;

The problem is that even though the large text insert is within single quotes, the semicolon at the end of a line is causing an error:

ERROR:
ORA-01756: quoted string not properly terminated

unknown command beginning "Random Hou..." - rest of line ignored.
unknown command beginning "accounting..." - rest of line ignored.
unknown command beginning "')), NULL,..." - rest of line ignored.

Commit complete.

We did some testing which I have added below and as you can see, anytime there is a semicolon at the end of a line, whether it is within a larger quoted text or not, terminates the insert. It will, however, allow a semicolon within a quoted string as long as there is a char following it. Anyway, is there a delimiter or some other type flag that can be used to insert this? We cannot just yank out all the semicolons. We can do a replace in the perl scripts to put delimiters in but we cannot take them out.

Connected to:
Oracle8 Release 8.0.4.3.0 - Production
PL/SQL Release 8.0.4.3.0 - Production

SQL> insert into textbooks
  2 values
  3 (99, 1, 1, ltrim(rtrim('
  4 Intermediate Accounting, Chasteen, Flaherty, and O''Conner; ERROR:
ORA-01756: quoted string not properly terminated

SQL> insert into textbooks
  2 values
  3 (99, 1, 1, 'O''Conner; test',null,null,null,null);

1 row created.

SQL> insert into textbooks
  2 values
  3 (99,1,1,'o''conner; Random'
  4 ,null,null,null,null);

1 row created.

SQL> insert into textbooks
  2 values
  3 (99,1,1,abc;
(99,1,1,abc

          *
ERROR at line 3:
ORA-00917: missing comma

SQL> insert into textbooks
  2 values
  3 (99,1,1,'abc
  4 ;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> insert into textbooks
  2 values
  3 (99,1,1,'abc;def',null,null,null,null);

1 row created.

SQL> insert into textbooks
  2 values
  3 (99,1,1, 'abc\;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> insert into textbooks
  2 values
  3 (99,1,1, 'abc;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> insert into textbooks values
  2 (99,1,1,"test;
ERROR:
ORA-01740: missing double quote in identifier

SQL> insert into textbooks values
  2 (100,1,1,'test'';'
  3 ,1,null,null,null);

1 row created.

Thanks
Darren Received on Fri Sep 17 1999 - 17:44:11 CDT