Re: Dynamic SQL with more than one table

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 13 Jun 2002 06:01:14 -0700
Message-ID: <aea52q02i8g@drn.newsguy.com>

In article <25edf32d.0206130230.39b557f5_at_posting.google.com>, bf109e_at_teleline.es says...
>
>Hi Group!
> I need to create a stored procedure which adds columns to a table
>in runtime. I use DBMS_SQL package, but I stopped because I've a
>problem. The problem is:
> I'ts possible to open a cursor with DBMS_SQL.EXECUTE when the FROM
>clause has more than one table? I.E.
> SQL clause to parse with DBMS_SQL.PARSE:
> 'SELECT N.IDNIVEL3, AVG(P.PVP) PMED FROM NIVEL3 N, PRECIOS P WHERE
> N.IDNIVEL3 = P.IDNIVEL3 AND
> N.ENS = ' ||WENS
>
> WENS is a variable value wich is obtained from a static cursor.
>
>
> Column definitions:
> DBMS_SQL.DEFINE_COLUMN(cid, 1, IDNIVEL3);
> DBMS_SQL.DEFINE_COLUMN(cid, 2, PMED);
>
> ...where IDNIVEL3 and PMED are NUMBER
>
> When I execute the procedure the error "ORA-06502: PL/SQL: error:
>error de conversión de carácter a número numérico o de valor" is
>displayed at the DBMS_SQL.EXECUTE line.
>
>Thanks in advance.
>Juanfra.

What this would have to do with more then one table -- i don't know -- but you need to use bind variables (for performance, for correctness). You are having some sort of numeric to character string conversion error. Make sure to compare strings to strings, numbers to numbers and so on.

Here is an example:

scott_at_ORA817DEV.US.ORACLE.COM> declare

  2      l_cursor  integer;
  3      l_dname   varchar2(25) := 'SALES';
  4      l_ename   varchar2(40);
  5      l_status  number;
  6  begin
  7      l_cursor := dbms_sql.open_cursor;
  8      dbms_sql.parse( l_cursor, 'select ename, dname
  9                                   from emp, dept
 10                                  where emp.deptno = dept.deptno
 11                                    and dname = :dname',
 12                      dbms_sql.native );
 13  
 14      dbms_sql.bind_variable( l_cursor, ':dname', l_dname );
 15      dbms_sql.define_column( l_Cursor, 1, l_ename, 40 );
 16      dbms_sql.define_column( l_Cursor, 2, l_dname, 25 );
 17  
 18      l_status := dbms_sql.execute(l_Cursor);
 19  
 20      while ( dbms_sql.fetch_rows(l_Cursor) > 0 )
 21      loop
 22         dbms_sql.column_value( l_Cursor, 1, l_ename );
 23         dbms_sql.column_value( l_Cursor, 2, l_dname );
 24  
 25         dbms_output.put_line( l_ename || ',' || l_dname );
 26      end loop;
 27  
 28      dbms_sql.close_cursor( l_cursor );

 29 end;
 30 /
y,SALES
WARD,SALES
MARTIN,SALES
BLAKE,SALES
TURNER,SALES
JAMES,SALES PL/SQL procedure successfully completed.

that shows it works. If you have 8i or up, you should just use:

scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> declare
  2 type rc is ref cursor;
  3

  4      l_cursor rc;
  5      l_dname   varchar2(25) := 'SALES';
  6      l_ename   varchar2(40);

  7 begin
  8

  9      open l_cursor for
 10         'select ename, dname
 11            from emp, dept
 12           where emp.deptno = dept.deptno
 13             and dname = :dname' using l_dname;
 14  
 15      loop
 16          fetch l_cursor into l_ename, l_dname;
 17          exit when l_cursor%notfound;
 18         dbms_output.put_line( l_ename || ',' || l_dname );
 19      end loop;
 20      close l_cursor;

 21 end;
 22 /
y,SALES
WARD,SALES
MARTIN,SALES
BLAKE,SALES
TURNER,SALES
JAMES,SALES PL/SQL procedure successfully completed.

as it is easier to code. MOST IMPORTANT POINT TO TAKE AWAY FROM THIS: use bind variables.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jun 13 2002 - 08:01:14 CDT