Re: pl/sql magic help request

From: Mark Griffiths <mgriffiths_at_easynet.co.uk>
Date: 1998/03/18
Message-ID: <351055b8.4230463@news.easynet.co.uk>#1/1

On Thu, 12 Mar 1998 09:11:48 -0800, Bill Kincaid <wkincaid_at_mindspring.com> wrote:

>Hi:
>
>O 7.2 / Forms 4.5
>
>I am having trouble preparing for an export to multiple text files.
>From a table called letters (some of the fields are listed below),
>
>< Snip ><

Why bother with an intermediate table? Have you tried simply having two cursors based on the same table???

The first (or outer) cursor would do the selection of the distinct records to generate the filenames;

The second (or inner) would select the actual records for output.

By the way, check out the use of Cursor FOR LOOPS. They will help you out no end in your syntax because you do not need to explicitly issue OPEN, FETCH or CLOSE statements. What is more, you don't need to define local variables explicitly in your DECLARE section either.

You would see something like this (taken from your code...)

DECLARE
  CURSOR c1 IS
    SELECT client_no, type

        FROM letters
      GROUP BY client_no, type;
  CURSOR c2 (p_client_no  VARCHAR2, p_type VARCHAR2) IS
     SELECT client_no, type, ....
        FROM letters
     WHERE client_no = p_client_no
           AND type = p_type;

BEGIN
  FOR c1_rec IN c1
  LOOP

      /*
      || Extract unique filename from c1_rec.client_no || c1_rec.type
      || and open the file for writing.
      */
      FOR c2_rec IN c2 (c1_rec.client_no, c1_rec.type)
      LOOP
          /*
          || Write the values to the file (using UTL_FILE??)
          || referencing the column values selected in cursor C2 by 
          || prefixing them with "c2_rec."; e.g. c2_rec.client_no.
          */
      END LOOP;
      /*
      || Close the file just written to.
      */

  END LOOP;
END;
/

What do you think??

Looks a bit neater, avoids a whole lot of extra code writing and does all your cursor manipulation for you automatically??

If you need some further guidance on PL/SQL, try Steven Feuerstein's excellent book on PL/SQL Programming from O'Reilly Associates. It's one of my most used books. The O'Reilly Web site has several pages dedicated to Oracle publications. Their address is

        http://www.ora.com/

Hope this helps you out.

Regards

Mark Griffiths
Freelance Oracle Consultant Received on Wed Mar 18 1998 - 00:00:00 CST