Запись файла PL/SQL с общим вводом

Передайте курсор в свою процедуру как SYS_REFCURSOR. Затем используйте DBMS_SQL.TO_CURSOR_NUMBER(); для преобразования курсора ref в курсор DBMS_SQL.

Затем используйте DBMS_SQL.DESCRIBE_COLUMNS для определения столбцов в курсоре и DBMS_SQL.DEFINE_COLUMN, DBMS_SQL.FETCH_ROWS и DBMS_SQL.VALUE для получения данных из курсора в переменные PL/SQL. Затем запишите переменные PL/SQL в выходной файл.

Вот некоторый код, который объединяет все это для вас.

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;