PL/SQL file writing with generic input

Question

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.

Answer 1

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;