Re: PL/SQL v. SQL*PLUS v. SQL*REPORTWRITER

From: Christian Mondrup <reccmo_at_uts.uni-c.dk>
Date: Thu, 26 Aug 1993 13:17:18 GMT
Message-ID: <1993Aug26.131718.29984_at_uts.uni-c.dk>


In <25hji0INNkr8_at_gap.caltech.edu> dbikle_at_cco.caltech.edu (Daniel B. Bikle) writes:

>secrist_at_kxovax.enet.dec.com (Strong datatypes for weak minds.) writes:

>>What is the high-level difference between PL/SQL, SQL*PLUS, and
>>SQL*REPORTWRITER ? SQL*PLUS seems like it could be a pretty
>>good reporter writer on its own from my cursory reading.
 

>PL/SQL has no i/o capability (V1)
...

When I am using PL/SQL(v1) as a reporting tool, which I frequently do, I combine my PL/SQL loops with a temporary output table. Example:

  CREATE TABLE plsql_out (line CHAR(255), line_no NUMBER);

  DECLARE
    <some cursor stuff>
    line_out CHAR(255);
    line_out_no NUMBER := 0;
    <other variables>
  BEGIN
    <open cursor>
    LOOP

      <fetch from cursor, select from tables etc.>
      ...
      line_out := <some value>;
      line_out_no := line_out_no + 1;
      INSERT INTO plsql_out VALUES(line_out, line_out_no);
      ...

    END LOOP;
    <close cursor>
  END;
  .
  run

  set term off
  set pagesize 0
  spool out.rep

  SELECT line FROM plsql_out ORDER BY line_no;

  spool off

  host <some tool to trim trailing blanks>

  DROP TABLE plsql_out;

I use that technique for making dynamic sql, some master detail reports etc.

-- 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+      Christian Mondrup                                                      +
+      UNI-C Aarhus, Danish Computing Centre for Research and Education.      +
+      Olof Palmes Alle 38, DK 8200 Aarhus N.                                 +
+                                                                             +
+      Phone:   +45 86 78 44 44                                               +
+      Telefax: +45 86 78 44 55                                               +
+      E-Mail:  Christian.Mondrup_at_uni-c.dk                                    +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Received on Thu Aug 26 1993 - 15:17:18 CEST

Original text of this message