Re: Bug??? -- not

A copy of this was sent to Darren Brock <brock_at_governet.net> (if that email address didn't require changing) On Fri, 17 Sep 1999 16:44:11 -0600, you wrote:

>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;
>

it is the way the interactive sql environment "sqlplus" interprets your strings.

See the sqlterminator setting. Here is an example that shows how it works:

tkyte_at_8.0> create table t ( x varchar2(25) );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> insert into t values
  2 ( ' How now ;
ERROR:
ORA-01756: quoted string not properly terminated

tkyte_at_8.0> brown cow ' );
unknown command beginning "brown cow ..." - rest of line ignored.

tkyte_at_8.0> 
tkyte_at_8.0> set sqlterminator  }
tkyte_at_8.0> insert into t values
  2  ( ' How now ;
  3     brown cow ' )}

1 row created.

set the sqlterminator to something that is not in the string.

OTOH: I wouldn't be processing "lots" of documents into sqlplus insert statements. why not use perl apis to oracle to process and insert using bind variables (you might find it works about 10 times faster then insert ( 'constant') and you won't be bothered with sqlplus'isms....)

>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

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 17 1999 - 18:21:20 CDT