Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trouble with inserting a string containing a single quote

404759Sep 24 2003 — edited Oct 8 2003
Using php with Oracle

If I do the following two lines before sending my $Query string through the parse function

$name = "Dominick's";
$Query = "INSERT INTO customers (name) values ('$name')";

it gives me the following error:

Warning: Ora_Parse failed (ORA-00917: missing comma -- while processing OCI function OPARSE)

If I try and force the single quote to be surrounded by double quotes and therefore not be confused:

$name = "Dominick's";
Query = "INSERT INTO customers (name) values (\"$name\")";

Trying that yields the following error:

Warning: Ora_Parse failed (ORA-01741: illegal zero-length identifier -- while processing OCI function OPARSE)

Help

Jeff

Comments

BluShadow

Specify the first part of the string literal with a TO_CLOB function, so the rest of the string is considered CLOB rather than VARCHAR2...

DECLARE

K CLOB;

BEGIN

DELETE FROM ABCD;

COMMIT;

  FOR I IN 1..17

  LOOP

K:= K||TO_CLOB('A')||' cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th

A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th

A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th

A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th

A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th

A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th

A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th

A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th'||I;

END LOOP;

INSERT INTO ABCD VALUES(k);

dbms_output.put_line(length(k));

COMMIT;

END;

You may also need to split up your string literal in to pieces and concatenate them with ||, as there can be limits on the length of literals.

Solomon Yakobson

Not enough:

SQL> DECLARE

  2  K CLOB;

  3  BEGIN

  4    FOR I IN 1..15

  5    LOOP

  6  K:= K||TO_CLOB('A')||' cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the re

sult set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values

from the result set into fields in th

  7  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

  8  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

  9  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

10  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

11  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

12  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

13  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th'||I;

14  END LOOP;

15  dbms_output.put_line(length(k));

16  END;

17  /

33246

PL/SQL procedure successfully completed.

SQL> DECLARE

  2  K CLOB;

  3  BEGIN

  4    FOR I IN 1..16

  5    LOOP

  6  K:= K||TO_CLOB('A')||' cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the re

sult set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values

from the result set into fields in th

  7  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

  8  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

  9  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

10  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

11  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

12  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

13  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th'||I;

14  END LOOP;

15  dbms_output.put_line(length(k));

16  END;

17  /

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 6

SQL> DECLARE

  2  K CLOB;

  3  BEGIN

  4    FOR I IN 1..16

  5    LOOP

  6  K:= K||TO_CLOB('A')||TO_CLOB(' cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values fro

m the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows o

f values from the result set into fields in th

  7  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

  8  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

  9  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

10  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

11  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

12  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th

13  A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields

in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set

into fields in th'||I);

14  END LOOP;

15  dbms_output.put_line(length(k));

16  END;

17  /

35463

PL/SQL procedure successfully completed.

SQL>

SY.

1 person found this helpful
Billy Verreynne

A CLOB is not a PL/SQL variable - the CLOB resides in the database as LOB segments, and the variable in PL/SQL is a pointer to this database LOB. The documentation uses the phrase LOB locator, for the PL/SQL variable, but it is just a friendly non-scary name for a pointer.

A LOB variable (locator or pointer) has a formal API - application programming interface - in the form of the DBMS_LOB package.

And it makes a LOT of sense to use this API directly in your PL/SQL to create and manipulate LOB locators in PL/SQL.

Explicit API calls (like DBMS_LOB.WriteAppend) to add a string to a CLOB, reads better, is understood better, and maintains better, than your attempt to add strings to a CLOB via concatenation. As this posting proves very clearly.

Do yourself a favour and use DBMS_LOB - and do your LOB processing explicitly, and properly.

1 person found this helpful
BEDE

For CLOBS use concatenation only when you are absolutely certain that it will not be longer than 32767 chars (as much as fits into a varchar2 variable in PL/SQL).

If there may be more than 32767 char, then use dbms_lob.write_append.

BluShadow 
BEDE wrote:
For CLOBS use concatenation only when you are absolutely certain that it will not be longer than 32767 chars (as much as fits into a varchar2 variable in PL/SQL). 
If there may be more than 32767 char, then use dbms_lob.write_append.

That's not necessarily true.

Whilst Billy is correct that it's probably good practice to use DBMS_LOB package for dealing with CLOBs, it isn't necessary.

The || concatenation operator is perfectly capable of concatenating CLOB values larger than 32767 bytes (note, it's bytes, not characters), just as the other string functions such as LENGTH, SUBSTR, INSTR etc. can all operate on CLOBs as well as VARCHAR2, without having to use the DBMS_LOB package.  It's been like that for a few versions of Oracle now (certainly since 10g if not before)

Anton Scheffer

Why? Concatenations works perfectly with clobs, see https://docs.oracle.com/database/121/SQLRF/operators003.htm#SQLRF51158

Anton Scheffer
Answer

Numbers can't be implicit converted to clob, so just add  || to_char(I) instead of ||I  at line 15

DECLARE
  K CLOB;
BEGIN
DELETE FROM ABCD;
COMMIT;
  FOR I IN 1..17 
  LOOP
K:= K||'A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th            A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th'||to_char(I); 
END LOOP;
INSERT INTO ABCD VALUES(k);
dbms_output.put_line(length(k));
COMMIT;
END;
/
Marked as Answer by raj.mrsr · Sep 27 2020
3 people found this helpful
mathguy

This is interesting. You are correct, of course (I just tried that and if we wrap I within TO_CLOB it works - not sure why you would use TO_CHAR and not TO_CLOB directly, although perhaps it makes no difference).

If we were doing this in SQL instead, not in PL/SQL, this wouldn't cause a problem. For example:

declare
  k clob;
begin
  for i in 1 .. 12000
  loop
    select k || i      --   concatenate in SQL
      into k
      from dual
    ;
  end loop;
  dbms_output.put_line(length(k));
end;
/
PL/SQL procedure successfully completed.
48894

Of course, this is the WRONG way to solve the problem. The RIGHT way to solve it is to wrap numbers within TO_CHAR or TO_CLOB, both in PL/SQL and in SQL - and not depend on Oracle playing Big Brother in one case but not in the other. Implicit conversions are never good.

Other than that, K was already declared CLOB. As far as the other operands are VARCHAR2 or CHAR (or other CLOB), the result of concatenating to K will always be CLOB. The only reason we need to wrap I within TO_CLOB is that it is NOT of character data type, and PL/SQL will not convert it to CLOB implicitly.

Anton Scheffer

More food for thought: als long as your input fits in a varchar2 (32767) it works, even is the result bigger.

declare
  x clob;
begin
  x := rpad( to_clob( 'y' ), 32767 ); -- works
  x := x || 1;
  dbms_output.put_line( length( x ) );
end;

declare
  x clob;
begin
  x := rpad( to_clob( 'y' ), 32768 ); -- doesn't work
  x := x || 1;
  dbms_output.put_line( length( x ) );
end;
Solomon Yakobson 
mathguy wrote:
Of course, this is the WRONG way to solve the problem. The RIGHT way to solve it is to wrap numbers within TO_CHAR or TO_CLOB, both in PL/SQL and in SQL - and not depend on Oracle playing Big Brother in one case but not in the other. Implicit conversions are never good.

Well, I'd say it is reasonable to expect from Big Brother Oracle using logic. And I don't see much logic in CLOB concatenated with number resulting in VARCHAR2 especially when this manifests itself in PL/SQL only:

SQL> declare

  2      v_clob clob;

  3  begin

  4      v_clob := lpad(to_clob('X'),32767,'X') || 1;

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> declare

  2      v_clob clob;

  3  begin

  4      v_clob := lpad(to_clob('X'),32768,'X') || 1;

  5  end;

  6  /

declare

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 4

SQL> declare

  2      v_clob clob;

  3  begin

  4      v_clob := lpad(to_clob('X'),32768,'X') || to_char(1);

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> select  lpad(to_clob('X'),32767,'X') || 1

  2    from  dual

  3  /

LPAD(TO_CLOB('X'),32767,'X')||1

--------------------------------------------------------------------------------

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select  lpad(to_clob('X'),32768,'X') || 1

  2    from  dual

  3  /

LPAD(TO_CLOB('X'),32768,'X')||1

--------------------------------------------------------------------------------

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL>

SY.

mathguy

I suppose at some level it is a matter of opinion (or preference) - I am very much in the camp that prefers strongly typed languages. "Don't concatenate a number to a string; if you need to, convert the number to string explicitly, otherwise we'll throw a data type mismatch error." Obviously that would answer the problem at hand.

On the other hand I understand the opposing argument - if we did that, instead of allowing people to concatenate numbers to strings, the cost of developing code would be greater; there would be a lot more to test and debug, and perhaps some of the less-skilled and lower-paid developers wouldn't be able to cope at all, creating problems for everyone. (Which is why I view it as a matter of "preference" rather than "this is right and that is wrong".)

But, yes, I agree:  IF Oracle is going to be Big Brother, for the reasons explained (and many more, perhaps), then at least it should be consistent with itself. Why concatenate a number to a really long CLOB without a problem in SQL, concatenate a number to a VARCHAR2 without a problem in PL/SQL as long as the result fits in VARCHAR2, but then fuss over "explicit conversion" of number, only when it is concatenated to CLOB in PL/SQL?  I am not defending Oracle's decision, I am just stating my opinion that we should always use explicit conversion (if we understand the concept, and if we don't mind taking the time to write the wrapper - spend more time here and less in fixing errors).

Anton Scheffer

In this PL/SQL example the result doesn't fit in a varchar2, it just starts with the largest varchar2. And it works!.

declare 

  x clob; 

begin 

  x := rpad( to_clob( 'y' ), 32767 ); -- works 

  x := x || 1;

  dbms_output.put_line( length( x ) );

end;

Doesn't look to logical to me indeed. Maybe Oracle tries to keep the operands for the concatenation operator as a varchar2 as long as that fits. And switch to a clob when needed, not for the result but for the operands And at that point the number can't be implicit converted to the "required" type.

Solomon Yakobson

Yes, it does. But why? In original case we have clob and number concatenation. If we check implicit conversion matrix number can't be converted to clob directly. So Oracle converts clob to varchar2, number to varchar2 and then does concatenation. As a result, when clob is converted to varchar2 we exceed varchar2 limit of 32767 and it errors out. In your example x || 1 x is converted to varchar2 and since length(x) = 32767 no exception is raised. You'll ask why concatenation result which has length of 32768 doesn't raise exception? Pure implementation issue:

SQL> declare

  2    x clob;

  3  begin

  4    x := rpad('y',32767) || rpad('y',32767);

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL>

Most likely intermediary results aren't checked - could be PL/SQL optimization, although setting plsql_optimize_level=0 still behaves same way.

SY.

Solomon Yakobson

As soon as you:

SQL> declare

  2    x clob;

  3  begin

  4    x := rpad( to_clob( 'y' ), 32768 );

  5    x := x || 1; -- doesn't work

  6    dbms_output.put_line( length( x ) );

  7  end;

  8  /

declare

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 5

SQL>

SY.

Solomon Yakobson

It appears PL/SQL intermediary VARCHAR2 operations use 64K buffer:

SQL> declare

  2    x clob;

  3  begin

  4    x := rpad('y',32767) || rpad('y',32767) || 'y';

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> declare

  2    x clob;

  3  begin

  4    x := rpad('y',32767) || rpad('y',32767) || 'yy';

  5  end;

  6  /

declare

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 4

SQL>

SY.

Anton Scheffer

It certainly looks that Oracle had left some room for improvement

Peter Gjelstrup

{off-topic}

Hi SY,

I opted out when this forum became "fancy with avatars and all" some 5-7 years ago.

So, please excuse my now noob question on trying to return: How exactly did you format your code in above answer?

This did not help me much when I tried the same: https://community.oracle.com/docs/DOC-889070#jive_content_id_How_do_I_format_code

BR

Peter

BluShadow

Peter,

The tidiest way to format code is to use the Advanced Editor (which isn't available via the inbox, only directly within the thread) and then highlight the code, choose the font "Courier New" from the list of fonts and, optionally (though I like to do this), go to the >> icon and choose "quote", which insets the code in a quote box.

NextName

{even more off-topic}

Ah, I (fondly) remember Usenet!

[Not that I was lurking in Oracle groups at that time ...]

1 - 19
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 5 2003
Added on Sep 24 2003
#php
6 comments
1,832 views