Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL with more than one table

Re: Dynamic SQL with more than one table

From: Omen <bf109e_at_teleline.es>
Date: 15 Jun 2002 03:18:31 -0700
Message-ID: <25edf32d.0206150218.593ecc36@posting.google.com>


Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<aeclsi01vua_at_drn.newsguy.com>...
> 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))

Hello Thomas:
Here the stack is:
ORA-06502: PL/SQL: error: error de conversión de carácter a número numérico o de valor
ORA-06512: en "CDATA1.PMED", línea 63
ORA-06512: en línea 2

My Oracle is a 8.0.6 Linux/Spanish version, but the error codes are the same. The "línea 63" (63th line) is ret:= DBMS_SQL.EXECUTE(strsql);

Thanks a lot.
>
> >
> >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 Sat Jun 15 2002 - 05:18:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US