Re: escape characters in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 09 Jun 1999 19:29:31 GMT
Message-ID: <3769c05b.113473416_at_newshost.us.oracle.com>


A copy of this was sent to polywog <polywog_at_drexel.edu> (if that email address didn't require changing) On Wed, 09 Jun 1999 15:04:14 -0400, you wrote:

>How do i escape characters in PL/SQL. For example when i declare this:
>
> SQL_WHERE_APREXCL VarChar2(32) := 'and aprcatg_pidm = aprexcl_pidm(+) ';
>
>it errors at this:
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at line 85
>
>I have narrowed it down to the '(' and '+' and ')' how do i escape these symbols?
>I have tried \ and '''' and ''' to no avail. Some help would be appreciated. =)
>
>ciao, elmo

[Quoted] Its not the (+) characters - it is the length of the string when you put the (+) on it!

your constant is greater then 32 bytes -- it doesn't fit in sql_where_aprexcl, thats where the value error is coming from...

  1 declare
  2 SQL_WHERE_APREXCL VarChar2(32) := 'and aprcatg_pidm = aprexcl_pidm(+) ';   3 begin
  4 null;
  5* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 2

SQL> 2
  2* SQL_WHERE_APREXCL VarChar2(32) := 'and aprcatg_pidm = aprexcl_pidm(+) ';
SQL> c/32/42
  2* SQL_WHERE_APREXCL VarChar2(42) := 'and aprcatg_pidm = aprexcl_pidm(+) ';
SQL> / PL/SQL procedure successfully completed.

by changing the length to 42 -- it works...

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

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 Wed Jun 09 1999 - 21:29:31 CEST

Original text of this message