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

Thomas,
first, I appreciate your help.
your feeling was right. The field1 in the pl/sql table definition was not big enough to hold the
values. The way I defined the pl/sql table is...

CURSOR c_testing IS

                SELECT  '                   '       item,    <-- too small for
values
                        '                   '       status, <-- too small for values

                        0                                           count,
                        0                                           revenue
                FROM    DUAL;

TYPE testing_table IS TABLE OF c_testing%ROWTYPE
                INDEX BY BINARY_INTEGER;

        plsql_table     testing_table;

I changed select columns 1 and 2 to a bigger string, and the problem went away.

This leads me to another question I posted but haven't gotten a response on. The question is how do I declare a PL/SQL table type corresponding to dynamic sql statement?

The way I did it above is by declaring a cursor that returns the same data types of the dynamic sql statement concerned, then declaring a TYPE corresponding to that table..etc.
Is there a better more direct way?

I greatly appreciate your help.
Thanks
Kal.

Thomas Kyte wrote:

> 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) );
>
> to
>
> 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 - 11:37:07 CDT