PL/SQL file writing with generic input

I recently created a PL/SQL program that creates five different pipe delimited files from related data in a database.

I could not find a way to dynamically pull different tabular data in this case cursors, into a generic procedure that would create the files. Instead I had to create five separate procedures, one for each file, that took in five different cursors, one for each file requirement record selection.

I can't help but think that there has to be a better way. I was looking into reference cursors but I don't think they are exactly what I am looking for.

How can I achieve this in PL/SQL?

I think what I am looking for is some generic type that can take any data from a cursor given any amount of records and record columns and have the ability to query itself to find what data is in it.

Solution

Pass the cursor into your procedure as a SYS_REFCURSOR. Then, use DBMS_SQL.TO_CURSOR_NUMBER(); to convert the ref cursor to a DBMS_SQL cursor.

Then, use DBMS_SQL.DESCRIBE_COLUMNS to figure out the columns in the cursor and DBMS_SQL.DEFINE_COLUMN, DBMS_SQL.FETCH_ROWS and DBMS_SQL.VALUE to get the data from the cursor into PL/SQL variables. Then, write your PL/SQL variables to your output file.

Here's some code that puts all that together for you.

DECLARE
  l_rc SYS_REFCURSOR; 

PROCEDURE dump_cursor (p_rc IN OUT SYS_REFCURSOR) IS
  -- Dump the results of p_rc to log

  l_cursor                INTEGER;
  l_column_count          INTEGER;
  l_column_descriptions   SYS.DBMS_SQL.desc_tab;
  l_status                INTEGER;
  l_column_value          VARCHAR2 (4000);
  l_column_width          NUMBER;
  l_rec_count             NUMBER := 0;
  l_line                  VARCHAR2 (4000);


  FUNCTION get_length (l_column_def IN SYS.DBMS_SQL.desc_rec)
    RETURN NUMBER IS
    l_width   NUMBER;
  BEGIN
    l_width   := l_column_def.col_max_len;
    l_width   := CASE l_column_def.col_type WHEN 12 THEN                                                      /* DATE */
                                                        20 WHEN 2 THEN                                      /* NUMBER */
                                                                      10 ELSE l_width END;
    -- Don't display more than 256 characters of any one column (this was my requirement -- your file writer probably doesn't need to do this
    l_width   := LEAST (256, GREATEST (l_width, l_column_def.col_name_len));
    RETURN l_width;
  END get_length;
BEGIN
  -- This is the date format that I want to use for dates in my output
  EXECUTE IMMEDIATE 'alter session set nls_date_format=''DD-MON-YYYY HH24:MI:SS''';

  l_cursor   := sys.DBMS_SQL.to_cursor_number (p_rc);

  -- Describe columns
  sys.DBMS_SQL.describe_columns (c => l_cursor, col_cnt => l_column_count, desc_t => l_column_descriptions);

  l_line     := '';

  FOR i IN 1 .. l_column_count LOOP
    l_column_width   := get_length (l_column_descriptions (i));

    l_line           := l_line || RPAD (l_column_descriptions (i).col_name, l_column_width);
    l_line           := l_line || ' ';
    DBMS_SQL.define_column (l_cursor,
                            i,
                            l_column_value,
                            4000);
  END LOOP;

  DBMS_OUTPUT.put_line (l_line);

  l_line     := '';

  FOR i IN 1 .. l_column_count LOOP
    l_column_width   := get_length (l_column_descriptions (i));

    l_line           := l_line || RPAD ('-', l_column_width, '-');
    l_line           := l_line || ' ';
    DBMS_SQL.define_column (l_cursor,
                            i,
                            l_column_value,
                            4000);
  END LOOP;

  DBMS_OUTPUT.put_line (l_line);

  --   l_status   := sys.DBMS_SQL.execute (l_cursor);

  WHILE (sys.DBMS_SQL.fetch_rows (l_cursor) > 0) LOOP
    l_rec_count   := l_rec_count + 1;

    l_line        := '';

    FOR i IN 1 .. l_column_count LOOP
      DBMS_SQL.COLUMN_VALUE (l_cursor, i, l_column_value);
      l_column_value   := TRANSLATE (l_column_value, CHR (10), CHR (200));
      l_column_width   := get_length (l_column_descriptions (i));

      IF l_column_value IS NULL THEN
        l_line   := l_line || RPAD (' ', l_column_width);
      ELSE
        l_line   := l_line || RPAD (l_column_value, l_column_width);
      END IF;

      l_line           := l_line || ' ';
    END LOOP;

    DBMS_OUTPUT.put_line (l_line);
  END LOOP;

  IF l_rec_count = 0 THEN
    DBMS_OUTPUT.put_line ('No data found.');
  ELSE
    DBMS_OUTPUT.put_line (l_rec_count || ' rows returned.');
  END IF;

  sys.DBMS_SQL.close_cursor (l_cursor);

  -- It would be better to store the current NLS_DATE_FORMAT on entry and restore it here, instead of assuming that it was
  -- set to DD-MON-YYYY.
  EXECUTE IMMEDIATE 'alter session set nls_date_format=''DD-MON-YYYY''';
EXCEPTION
  WHEN OTHERS THEN
    EXECUTE IMMEDIATE 'alter session set nls_date_format=''DD-MON-YYYY''';
-- Add your own handling here.

END dump_cursor;

-- Tester code, make sure server output is on
BEGIN
  OPEN l_rc FOR 'SELECT object_id, object_name, object_type FROM dba_objects WHERE rownum <= 15';
  dump_cursor(l_rc);
END;