Re: Dynamic SQL with more than one table

In article <25edf32d.0206140001.7d1bafbe_at_posting.google.com>, bf109e_at_teleline.es says...
>
>First of all, thank you for you response Thomas.
> The bad news: I still having the same problem. Certainly, I haven't
>used binded variables but I've modified the source code and the
>problem remains.
> The sample code I've written is too simple. You can see behind a
>bit of my source code.

and the error is?? (need the entire error stack and error NUMBER (code))

>
>CREATE OR REPLACE PROCEDURE PMED_L3(PSEMPCAR NUMBER,
> PSEMUCAR NUMBER,
> SSEMPCAR NUMBER,
> SSEMUCAR NUMBER)
>IS
>WIDENSENIA ENSENIAS.IDENSENIA%TYPE;
>WENSENIA ENSENIAS.ENSENIA%TYPE;
>cid INTEGER;
>cid2 INTEGER;
>ret INTEGER;
>strsql VARCHAR2(1000);
>IDNIVEL3 NUMBER;
>PMED NUMBER;
>
>CURSOR C_ENSENIAS IS
>SELECT IDENSENIA, ENSENIA FROM ENS_INF ORDER BY ENSENIA;
>
>BEGIN
> --Next lines work fine
> --Dropping final table
> cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cid, 'DROP TABLE CDATA1.FINAL_INF' , dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(cid);
>
> --Re-creating final table
> cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cid, 'CREATE TABLE CDATA1.FINAL_INF (IDNIVEL3
>NUMBER(3), NIVEL3 VARCHAR2(15))' , dbms_sql.v7);
> ret := DBMS_SQL.EXECUTE(cid);
> DBMS_SQL.CLOSE_CURSOR(cid);
>
> --Inserting nivel3 codes and literals in final table
> INSERT INTO FINAL_INF (IDNIVEL3, NIVEL3)
> SELECT IDNIVEL3, NIVEL3 FROM NIVEL3;
>
> --Dynamic creation of columns for every WENSENIA
> OPEN C_ENSENIAS;
> LOOP
> FETCH C_ENSENIAS INTO
> WIDENSENIA,
> WENSENIA;
> EXIT WHEN C_ENSENIAS%NOTFOUND;
>
> --No problems here. Wensenia based columns are created
> cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cid, 'ALTER TABLE DATA1.FINAL_INF ADD
>('||WENSENIA||'_P NUMBER(4,2))' , dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(cid);
>
> --Construction of a cursor with more than one table
> --PSEMPCAR and PSEMUCAR are input parameters
> --widensenia is a binded variable
> --N.NIVEL3 is a NUMBER(3)
> --O.PVP is a NUMBER(9,3)
> strsql := 'SELECT N.IDNIVEL3, ROUND(AVG(O.PVP),2) AS PMED FROM
>OFERTAS O, CARTELES C, ARTBAS A, TIPPROD T, FAMILIAS F, NIVEL3 N WHERE
>';
> strsql := strsql || 'O.IDMARCA = A.IDMARCA AND O.IDARTBAS =
>A.IDARTBAS AND A.IDTIPPROD = T.IDTIPPROD AND ';
> strsql := strsql || 'T.IDFAMILIA = F.IDFAMILIA AND F.IDNIVEL3 =
>N.IDNIVEL3 AND C.IDCARTEL = O.IDCARTEL AND ';
> strsql := strsql || 'C.IDCARTEL BETWEEN '||PSEMPCAR||' AND
>'||PSEMUCAR||' AND ';
> strsql := strsql || 'C.IDENSENIA = :widensenia GROUP BY
>N.IDNIVEL3';
> cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cid, strsql, dbms_sql.v7);
>
>
> DBMS_SQL.BIND_VARIABLE(cid, ':widensenia', WIDENSENIA);
> DBMS_SQL.DEFINE_COLUMN(cid, 1, IDNIVEL3);
> DBMS_SQL.DEFINE_COLUMN(cid, 2, PMED);
>
> --I've got then problem with the next line (in runtime, but the
>procedure is succesfully compiled)
> ret:= DBMS_SQL.EXECUTE(strsql);
>
>.............MORE CODE
>
>Well, I don't know what's wrong here. Anybody can help me?
>
>Thanks a lot in advance. (and sorry about my English, but I'm a
>English student only for five months)

--
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