dynamic PLSQL (DBMS Output)

[747576]

Hi,

I need some help with a bit of PLSQL, which dynamically produces a script using DBMS Output in toad. I eventually will run this as a view using execute immediate. The code is below, the dynamic plsql will build a statement using a table called NEWTABLE, I feed the dynamic script certain coulumns from this table. The columns are numeric, the statement produced will sum the columns to produce another field. My problem is that I always get an extra '+' sign at the end of the summing part of the statement (see the results).

Can someone help.

Code to create table:

CREATE TABLE NEWTABLE(ID NUMERIC(10), OVALUE1 NUMERIC(10), OVALUE2 NUMERIC(10), OVALUE3 NUMERIC(10), OVALUE4 NUMERIC(10));

INSERT INTO NEWTABLE(ID,OVALUE1,OVALUE2,OVALUE3,OVALUE4) VALUES('1','96','45','56','101');
INSERT INTO NEWTABLE(ID,OVALUE1,OVALUE2,OVALUE3,OVALUE4) VALUES('2','98','25','51','74');
INSERT INTO NEWTABLE(ID,OVALUE1,OVALUE2,OVALUE3,OVALUE4) VALUES('3','97','58','56','14');

procedure used to output statement using DBMS Output

create or replace procedure p( p_string in varchar2 ) 
is 
   l_string long default p_string; 
begin 
   loop 
     exit when l_string is null; 
     dbms_output.put_line( substr( l_string, 1, 250 ) ); 
     l_string := substr( l_string, 251 ); 
   end loop; 
end;

dynamic pl/sql in TOAD

DECLARE

TYPE OvalueList IS TABLE OF VARCHAR2(40);
OvalueName OvalueList;
tkey int;
stm1 varchar2(32700);
stm2 varchar2(32700);
stm3 varchar2(32700);
--stm4 varchar2(32700);



BEGIN

OvalueName := OvalueList('OVALUE1','OVALUE2','OVALUE3');

if length(OvalueName(OvalueName.first)) > 0 then
   OvalueName := OvalueName;
else
    OvalueName := OvalueList('NoValue');
end if;


tkey := OvalueName.first;


       stm1 := 'SELECT ID, ';
       
       LOOP
       
       stm2 := stm2||' NVL("'||UPPER(OvalueName(tkey))||'",0) AS "'||UPPER(OvalueName(tkey))||'", ';
       
       
       
      EXIT WHEN tkey = OvalueName.last; 
      tkey := OvalueName.next(tkey);       
      END LOOP;
      
tkey := OvalueName.first;

       LOOP
       
       stm2 := stm2||' "'||UPPER(OvalueName(tkey))||'" +';
       
       
       
      EXIT WHEN tkey = OvalueName.last; 
      tkey := OvalueName.next(tkey);       
      END LOOP;
          
       stm3 := stm3||'AS "SUM" FROM NEWTABLE';
     
p(stm1);
p(stm2);
p(stm3);
--p(stm4);

  
END;

result:

SELECT ID, 
 NVL("OVALUE1",0) AS "OVALUE1",  NVL("OVALUE2",0) AS "OVALUE2",  NVL("OVALUE3",0) AS "OVALUE3",  "OVALUE1" + "OVALUE2" + "OVALUE3" +
AS "SUM" FROM NEWTABLE

 

[SantaMufasa]

First of all, 747576, in Oracle the double quotes surrounging the column names are poor form (especially when creating objects).

To get rid of the dangling "+", this code should be a quality replacement for your code snippet, above:

...
stm3 := [b]rtrim(stm3,'+')[/b]||'AS "SUM" FROM NEWTABLE';

Let us know how that works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[747576]

Hi that works good, thank you.

when you say I shouldn't use double quotes around the field names do you mean for example:

NVL("OVALUE",0)

should be:

NVL(OVALUE,0)

 

[SantaMufasa]

Correct. The only time that double quotes are necessary is when someone has, again in poor form, done a "CREATE TABLE..." statement in which they have enclosed either the table name or column names within double quotes and one or more characters in the object name uses characters beyond the normally legal object-naming character set.

If one names an Oracle object/column using double quotes and mixed-case characters, then the object must forevermore use double quotes and the same mixed-case configuration...What a hassle -- that is why Oracle professionals consider the use of double quotes to be poor form.

If, conversely, you do not use double quotes to enclose Oracle names, then you can refer to that name in any combination of character cases.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]