Oracle Partition in For Loop Select

Question

I need to fetch all rows in a specific partition of a table and print the values to a file.

CREATE OR REPLACE PROCEDURE
WriteRecordToFile
(
  mypartition IN VARCHAR2,
  myfilename  IN VARCHAR2,
  mydirloc    IN VARCHAR2
)
IS
  out_file      utl_file.file_type;
  chunk_size    BINARY_INTEGER := 32767;
BEGIN
  out_file := utl_file.fopen (mydirloc, myfilename, 'w', chunk_size);
  
  FOR rec IN (
    SELECT name FROM my_table PARTITION mypartition
  )
  LOOP
    utl_file.put(out_file, rec.name);
    utl_file.new_line(out_file);
  END LOOP;
  
  utl_file.fclose (out_file);
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line('Error while writing file: '|| sqlerrm);
  IF utl_file.is_open(out_file) THEN
    utl_file.fclose(out_file);
  END IF;
END;
/

However, the above stored procedure only works if there is no 'PARTITION mypartition' in the select statement. I tried to use

EXECUTE IMMEDIATE 'SELECT name FROM my_table PARTITION mypartition'

in above but it still doesn't work. What is the correct way to do query with partition in the stored procedure? I am using Oracle 19c.

Answer 1

First, it's probably a bad design to use PL/SQL and utl_file for so simple a task. It'd be better just to have a client select with a normal SQL query and dump the results locally (to the client).

However, if you must, to make your code work you'd need both to add parenthesis around the partition name and you must use dynamic SQL because you are parameterizing an object name, not merely a bind variable. There are several options for this:

While EXECUTE IMMEDIATE in combination with BULK COLLECT could be used if you want to fetch into a collection (array), at large volumes that puts too much stress on PGA memory usage. If you want a cursor that operates row-by-row instead, you cannot use EXECUTE IMMEDIATE.

You can, however, use a ref cursor (like the system-provided type sys_refcursor) with an OPEN FOR statement to implement a dynamic select. That will avoid the memory demands of a collection. It's an explicit cursor, however, not an implicit one, so you will have to explicitly FETCH from it. You will also need to create a record variable that matches the column layout of your SELECT clause (not strictly necessary if you are selecting only 1 column). See below.

(I've marked the additional/new rows with --NEW. The rest is your code)

CREATE OR REPLACE PROCEDURE
WriteRecordToFile
(
  mypartition IN VARCHAR2,
  myfilename  IN VARCHAR2,
  mydirloc    IN VARCHAR2
)
IS
  out_file      utl_file.file_type;
  chunk_size    BINARY_INTEGER := 32767;

  cur sys_refcursor; --NEW
  TYPE rectype IS RECORD (name varchar2(128)); --NEW
  rec rectype; --NEW
BEGIN
  out_file := utl_file.fopen (mydirloc, myfilename, 'w', chunk_size);
  
  OPEN cur FOR 'SELECT name FROM my_table PARTITION ('||mypartition||')';--NEW
  
  FETCH cur INTO rec;--NEW
  WHILE cur%FOUND--NEW
  LOOP
    utl_file.put(out_file, rec.name);
    utl_file.new_line(out_file);
    FETCH cur INTO rec;--NEW
  END LOOP;
  
  CLOSE cur; --NEW
  utl_file.fclose (out_file);
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line('Error while writing file: '|| sqlerrm);
  IF utl_file.is_open(out_file) THEN
    utl_file.fclose(out_file);
  END IF;
END;
/