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.

Do you wish to export the data (into .csv, ie.) or to write it using UTL_FILE? — Neria Nachum, Oct 20 '15 at 18:29
The procedures ran as a concurrent request in oracle e-business suite. So I think I would need it to be written using UTL_FILE. However, the requirements are that after this "program" is run, the result is five new files in directory outbound. So as long as it exports the data not using UTL_FILE and the result is five csv files, then you are good to go. Thanks for the clarification — Clint L, Oct 20 '15 at 18:36
Here's a [good article](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:88212348059) about the subject that also provides a generic export-to-csv procedure. — Neria Nachum, Oct 20 '15 at 18:42
Thanks Neria, I read the article. It gave me a good amount of ideas and this seems to work for one table. Each file I create is created from complex cursors that take parameters and join many tables, one of them even joins 12 tables. These parameters are sent in to the cursors on the call to the package and therefore the cursor is dynamic to the point where the columns stay consistent (a problem which this article you sent me does solve ;)) but the data remains the same in varying amounts. I will update my answer with more detail, as I see now that some assumptions I made introduce limitations — Clint L, Oct 20 '15 at 19:15
To add (comment character limit) the varchar variable in the article holding the sql select, would have to take in my cursor text, then add in a parameter for each table, which differs for each cursor. So I would still need five separate procedures to take in a different number of tables and table names. Also, I have no idea how I would introduce the fields which these tables are joined on. This did solve an issue I had where column amounts were fixed, and I see how they used the dbms_describe_columns to forgo this, so I thank you for bring that new piece of knowledge to my attention. — Clint L, Oct 20 '15 at 19:22
if you are parsing a ref_cursor using dbms_sql, it doesn't matter how many joins were in the query that created the cursor - only the structure of the resultant record. Similarly, if your structure is going to be the same but it is the joins that change, look into constructing the query as a varchar2 string and then using "execute immediate" to execute it into a predefined cursor type. — Michael Broughton, Oct 20 '15 at 19:27
Hi Michael, you have me interested in ref_cursors again as your description seems to be what I need. I did a fair bit of research but as I am new it is possible I missed something simple in its concept. Do you have any example or otherwise that demonstrates the use of a ref_cursor that is used and later on is reused with different resultant structure? — Clint L, Oct 20 '15 at 19:32
Think of a sys_refcursor as a pointer type. It can point to any opened cursor of any structure, so you can use it for one cursor, close it, then re-open it with an entirely different select statement. From there, you need to be able to parse the structure to be able to access the data elements. See this for a start point: http://www.morganslibrary.org/hci/hci003.html — Michael Broughton, Oct 20 '15 at 19:46
@ClintL, I'm sorry I had to trim your post. Feel free to re-edit it/rollback the change if you wish. But be aware that stackoverflow standards tend to avoid conversational style, and favor code over text. Also, please show your existing code, you will greatly increase the attention over your question. — Sebas, Oct 20 '15 at 20:12
You could even pass the query as a VARCHAR2 parameter and then open it in the procedure. If you ref cursors can't get it done for you, maybe you could get it done with DBMS_SQL — pablomatico, Oct 21 '15 at 05:40
@Sebas No problem, I need to refresh my StackOverflow standards. — Clint L, Oct 21 '15 at 11:24

score 2 · Accepted Answer · answered Oct 28 '15 at 20:13

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;

Thank you for this answer. I learned a lot more about Ref cursors that many documents couldn't just plainly spell out. This is definitely what I was looking for. — Clint L, Feb 05 '16 at 15:04

PL/SQL file writing with generic input

1 Answers1