Re: ampersand problem in dynamic sql and PL/SQL tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 06 Aug 1998 14:00:39 GMT
Message-ID: <35cab65d.4219457@192.86.155.100>

A copy of this was sent to Kal Khatib <kkhatib_at_cisco.com> (if that email address didn't require changing) On Wed, 05 Aug 1998 15:11:37 -0700, you wrote:

>In a package..
>I'm retrieving data using dynamic sql, then stroing it in a PL/SQL
>table.
>If a column contains the ampersand (&) I get an error
>ORA-06502: PL/SQL: numeric or value error
>

I think you are getting the error elsewhere -- it just happens to HAPPEN on a string with an & but the & is *not* the cause.

You didn't include the type definitions for the record your pl/sql table type is built on, nor the pl/sql table type itself. This are probably the issue -- not an ampersand.

For example, I just ran:

SQL> create table testing_table ( x varchar2(25) ); Table created.

SQL> insert into testing_table values ( '&&&&&&&&&&&&&&&&&&&&&&' );
SQL> insert into testing_table values ( '&some string' );

SQL> insert into testing_table values ( 'some other string&' ); SQL> commit;
Commit complete.

SQL> create or replace procedure testing 2 as

  3          c1                      integer;
  4          sql_stmt      varchar2(255) default 'select x from testing_table';
  5          v_field_c1      varchar2(25);
  6  
  6          type myrec is record ( field1 varchar2(25) );
  7          type plsql_table_type is table of myrec index by binary_integer;
  8          plsql_table plsql_table_type;
  9          v_row           number default 0;
 10          v_dummy         number;
 11  begin
 12          c1 := dbms_sql.open_cursor;
 13          dbms_sql.parse(c1, sql_stmt, dbms_sql.NATIVE);
 14          dbms_sql.define_column (c1, 1, v_field_c1, 2000);
 15          v_dummy := dbms_sql.execute(c1);
 16  
 16          v_row := 1;
 17          WHILE (dbms_sql.fetch_rows(c1) > 0)
 18          LOOP
 19                  dbms_sql.column_value(c1, 1, v_field_c1);
 20                  plsql_table(v_row).field1 := v_field_c1;
 21                  v_row := v_row + 1;
 22          END LOOP;
 23  
 23          for i in 1 .. plsql_table.count loop
 24                  dbms_output.put_line( plsql_table(i).field1 );
 25          end loop;

26 end;
27 /
Procedure created.

SQL> set serveroutput on
SQL> exec testing

&&&&&&&&&&&&&&&&&&&&&&

&some string
some other string&

So, &'s have nothing to do with it. If I change the line:

6 type myrec is record ( field1 varchar2(25) );

6 type myrec is record ( field1 varchar2(5) ); -- not 25 but 5

I then get:

SQL> set serveroutput on
SQL> exec testing
begin testing; end;

*
ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TKYTE.TESTING", line 20
ORA-06512: at line 1

I have a feeling that your field1 field might not be big enough to hold the string that happens to have an & in it....

>c1 := dbms_sql.open_cursor;
>dbms_sql.parse(c1, sql_stmt, dbms_sql.NATIVE);
>dbms_sql.define_column (c1, 1, v_field_c1, 2000);
>v_dummy := dbms_sql.execute(c1);
>
>v_row := 1;
>WHILE (dbms_sql.fetch_rows(c1) > 0)
>LOOP
> dbms_sql.column_value(c1, 1, v_field_c1);
> plsql_table(v_row).field1 := v_field_c1; <--chokes if v_field_c1 has
>an '&' in it.
> v_row := v_row + 1;
>END LOOP;
>
>How do I get around this problem without having to string substitute in
>v_field_c1?
>
>I tried setting scan off in the beginning of the package.. doesn't work.
>
>I would greatly appreciate any help.
>
>Kal
>
>ps. please copy me when responding to group.
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Aug 06 1998 - 09:00:39 CDT