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.

• MT0: Please edit the question and include a minimal reproducible example with: the CREATE TABLE and INSERT statements for your sample data; the complete error message; and the expected output for your sample data. "It still doesn't work" is not a constructive statement as it does not tell us "what" does not work or what the issue was with the thing that is not working.

Answer 1

"Doesn't work" is pretty much useless. Oracle must have specified the error, and - if you shared it with us - it would help us help you.

Anyway: select statement looks suspicious. documentation says that syntax is

SELECT name FROM my_table PARTITION (mypartition)
 ^ ^
 | |
 parenthesis aren't optional!

so - for the starters - add parenthesis and see what happens next.

• user2940105: I see the error "Warning: Procedure created with compilation errors." even there is parenthesis there. Anyway I found it is alright if I use EXECUTE IMMEDIATE.

Answer 2

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;
/

Answer 3

You can't use parameter for a partition name for the same reasons that you can't use it for a table name.

It's also arguably poor practice to pass an internal database object name such as a table or partition name as part of a business process.

What we normally do is pass the partition key value:

create or replace procedure WriteRecordToFile
 ( inKeyValue in mytable.partkey%type -- sale date, product type etc
 , inFilename in varchar2
 , inDirLoc in varchar2 )
as
 out_file utl_file.file_type;
 chunk_size pls_integer := 32767;
begin
 out_file := utl_file.fopen(inDirLoc, inFilename, 'w', chunk_size);
 for rec in (
 select name from my_table where part_key_value = inKeyValue
 )
 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;
/