SQL & PL/SQL

Hi,

I thought that this questions must have been asked hundred of times before but even Google couldn't help me. And yes, I am a beginner, just started using PL/SQL.

For monitoring Oracle databases (version 9 to 11) I like to send the output of different SELECT statements via e-mail using PL/SQL. The output should look something like this:

column1       column2         column3
---------------------------------------------------
value1         othervalue12   value_long__________1
value2         othervalue12   value_long______2
value3         othervalue13   value_long_______3

Right now I create a cursor based on a hard coded select statement and a record derived from the rowtype of the cursor. Then I loop thru the cursor, fetch the rows into the record, build a varchar2 with the right padded column values and add the column headers.

Everything is hard coded right now, so I have to write a new procedure for every SELECT statement. However I would like to write a function with takes the select statement as a parameter and returns a varchar2 with the output formatted like above.

I managed to create a dynamic cursor but failed with a dynamic record. All I found was deriving the cursor from the rowtype of the table which then gives me a new task to filter the columns.

Also I wonder if there is any way to read out the column names from a cursor or a record and determine the maximum length of the values for each column (so I could right pad them neatly).

Or is there a better way for this task without using cursors and records?

One sample function right now is:

create or replace
function DB_TEST 
return varchar2
  as
    v_test VARCHAR2(32767) := '';

    CURSOR c_test is 
      select auftrnr, artnr, lfkw, menge, form, liefnr, kndkbz
      from table_test
      where status='x' 
      order by lfkw, auftrnr ;
    r_test c_test%rowtype;
   
begin
  open c_test;
  loop
    fetch c_test into r_test;
    exit when c_test%NOTFOUND;
    v_test:=v_test 
      ||rpad(r_test.auftrnr,18,' ')
      ||rpad(r_test.artnr,8,' ')
      ||rpad(r_test.lfkw,6,' ')
      ||rpad(r_test.menge,8,' ')
      ||rpad(r_test.form,8,' ')
      ||rpad(r_test.liefnr,8,' ')
      ||rpad(r_test.kndkbz,80,' ')
      ||chr(13)||chr(10); 

  end loop;
  if c_test%rowcount > 0 then
    v_test := 'Auftragsnummer    Artnr   LFKW  Menge   Varian. Liefer. Kunde'
      ||chr(13)||chr(10) 
      || '---------------------------------------------------------------------------------------'
      ||chr(13)||chr(10) 
      ||chr(13)||chr(10)
      || v_test
      ||chr(13)||chr(10) 
      ||chr(13)||chr(10); 
  else
    v_test := '';
  end if ;
  close c_test;
  return v_test;
 END;
 

Any help will be appreciated, also if anyone could point me to some useful documentation for me to read.




Kind regards, Chris

Edited by: Chris on 2012-mar-28 08:24

Edited by: Chris on 2012-mar-28 08:49

Edited by: Chris on 2012-mar-28 09:32