PL/SQL cursor trouble

From: Schillinger <rz34_at_hp850.rz.uni-karlsruhe.de>
Date: 23 Mar 1993 10:22:36 GMT
Message-ID: <1omodc$85v_at_nz12.rz.uni-karlsruhe.de>

Background: Oracle v.6.0.34.2.1

	    PL/SQL v. 1.0.34.0.1
	    HP/UX 9000 Series 800

I'm working on my first PL/SQL script which should merely produce a listing of data joined from several tables. I've been using all the handbooks, but can't find the solution to a probably simple problem:

I'm not getting any output on my screen.

I assume this is because I'm not telling Oracle to give me any, but how DO I tell it to do so???

Because I want a listing of all data, I've created a cursor, "name_list_cursor", and use the FETCH... INTO statement in a loop, as described in the PL/SQL handbook. I think I need something else in the loop to display the list, but what??

(TERMOUT is turned off at the beginning, but turned on again before the column formatting.)

I tried SPOOL filename; but it didn't like that (gave me the message that it was expecting ":=" or some other symbol before "filename").

Following is the script (it's not terribly long).

Oh! Another question, could I be getting trouble from my "Group by" and/or "Order by" in my cursor? I wasn't sure if that was allowed, but I found nothing to the contrary. (The "Group By" is the reason for the Max() around all the columns in the cursor's Select statement, by the way.)

Does anyone have any advice or ideas??
Thanks in advance,
Karen Schillinger
Schillinger_at_rz.uni-karlsruhe.de

---

My Script:

---

set termout off;
break on today;
column today new_value datevar;
set pause off;
select to_char(sysdate,'dd.mm.yy') today from dual;
set pause on;
set pagesize 22; /* Set for terminal.*/ set linesize 80;
set newpage 0;
clear columns;
clear breaks;
set termout on;
column verw_nr format a8 heading "Verw.Nr." column nachname||vorname alias name format a25 heading Name; column maschine format a1 heading "-";
column maschine format a1 heading D

column maschine format a1 heading H;
column maschine format a1 heading I;
column maschine format a1 heading V;
column maschine format a1 heading F;

column maschine format a2 heading "VM";
column maschine format a1 heading M;
column matrikel format a30 heading "Matr. Nr. bzw. Institut"; column user_id format a6 heading Userid; ttitle left 'USER LIST (ALPHABETIC)'-
col 40 datevar-
right 'PAGE: ' format 999 SQL.PNO skip 0 /* Variable Declaration */
DECLARE
    cursor name_list_cursor is

       select max(decode(matrikel,null,i.inst_nr||substr(u.user_id,3,4),
		  f.fak_nr||s.end_verw_nr)) vn,
	      max(substr(nachname,1,21))||' '||max(substr(vorname,1,10)) name,
              max(decode(maschine,'-keine-','-')) m1,
              max(decode(maschine,'D ????','D')) m2,
              max(decode(maschine,'HP-WORK','H')) m3,
              max(decode(maschine,'IBM3090-300S','I')) m4,
              max(decode(maschine,'SNI 600/20','V')) m5,
              max(decode(maschine,'V-FACIL','F')) m6,
              max(decode(maschine,'VM','VM')) m7,
              max(decode(maschine,'Maspar','M')) m8,
              max(decode(matrikel,null,substr(inst_name_1,1,30),matrikel)) matr,
              max(substr(u.user_id,1,6)) id
       from person p, maschine m, instit i, userid u, user_masch um,
	    stud_vn_code s, fakult f
       where u.benutz_pnr = p.pers_nr and
             u.inst_nr = i.inst_nr and
             u.user_id = um.user_id and
             um.masch_id = m.masch_id and
	     substr(u.user_id,3,4) = s.end_user_id and
	     i.fak_nr = f.fak_nr
        group by u.user_id
        order by max(nachname),max(vorname),max(u.user_id);
/*                                                                        */    
    name_rec  name_list_cursor%ROWTYPE;
/*                                                                        */
begin
     open name_list_cursor;
     LOOP
	 FETCH name_list_cursor into name_rec;
	 exit when name_list_cursor%NOTFOUND;
     END LOOP;
     close name_list_cursor;

end;
/ Received on Tue Mar 23 1993 - 11:22:36 CET