Re: Dynamic SQL with more than one table

From: Omen <bf109e_at_teleline.es>
Date: 14 Jun 2002 01:01:14 -0700
Message-ID: <25edf32d.0206140001.7d1bafbe@posting.google.com>

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.

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) Received on Fri Jun 14 2002 - 03:01:14 CDT