I have a stored procedure that currently outputs a zip file containing a CSV, this works without a problem.
CREATE OR REPLACE PROCEDURE generate_fbdi_sp (
p_id_proceso_oic IN VARCHAR2,
p_source IN VARCHAR2,
p_file_reference IN VARCHAR2,
out_result OUT VARCHAR2,
out_message OUT VARCHAR2
) IS
--Query when all categories were found
CURSOR c_csv_fbdi IS
SELECT
status_code
|| ','
|| ledger_id
|| ','
|| effective_date_of_transaction
|| ','
|| journal_source
|| ','
|| journal_category
|| ','
|| currency_code
|| ','
|| journal_entry_creation_date
|| CHR(10) AS linea
FROM
int_dat_journals_fbdi_layout
WHERE
journal_source = p_source
AND id_proceso_oic = p_id_proceso_oic;
--variables
v_csv_line CLOB;
g_zipped_blob BLOB;
v_blob_csv_line BLOB;
BEGIN
--Iterate lines to create CSV File into CLOB variable 'v_csv_line'
FOR curr_line IN c_csv_fbdi LOOP
v_csv_line := v_csv_line || curr_line.linea;
END LOOP;
--Convert csv clob variable 'v_csv_line' to blob into variable 'v_blob_csv_line'
SELECT
clob_to_blob_fn(v_csv_line)
INTO v_blob_csv_line
FROM
dual;
--Zip FBDI file
as_zip.add1file(g_zipped_blob, 'GlInterface_'
|| replace(p_source, ' ', '_')
|| '_'
|| p_id_proceso_oic
|| '.csv', v_blob_csv_line);
--Close zip file
as_zip.finish_zip(g_zipped_blob);
--Insert csv file
INSERT INTO journals_fbdi (
file_name,
file_content,
id_proceso_oic,
file_name_cv027,
source
) VALUES (
'FBDI_'
|| replace(p_source, ' ', '_')
|| '_'
|| p_id_proceso_oic
|| '.zip',
g_zipped_blob,
p_id_proceso_oic,
p_file_reference,
p_source
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
out_result := 'ERROR: ' || sqlerrm;
out_message := dbms_utility.format_error_backtrace;
END;
Currently, the procedure takes all the records from the table (it can be five thousand records in a single file, for example) and generates the CSV. But now they asked me to change it to generate a file every thousand records.
How could this goal be achieved?
Below is your code modified to handle your task. It is untested, but you should get the general idea. I hope I was able to help.
CREATE OR REPLACE PROCEDURE generate_fbdi_sp (
p_id_proceso_oic IN VARCHAR2,
p_source IN VARCHAR2,
p_file_reference IN VARCHAR2,
out_result OUT VARCHAR2,
out_message OUT VARCHAR2)
IS
--Query when all categories were found
CURSOR c_csv_fbdi IS
SELECT status_code
|| ','
|| ledger_id
|| ','
|| effective_date_of_transaction
|| ','
|| journal_source
|| ','
|| journal_category
|| ','
|| currency_code
|| ','
|| journal_entry_creation_date
|| CHR (10) AS linea
FROM int_dat_journals_fbdi_layout
WHERE journal_source = p_source
AND id_proceso_oic = p_id_proceso_oic;
--variables
v_csv_line CLOB;
g_zipped_blob BLOB;
-- v_blob_csv_line BLOB;
TYPE clob_rows IS TABLE OF CLOB;
v_indx NUMBER := 1; -- index used for the collection of clob rows
v_csv_line_rows clob_rows; -- your clob collection
v_record_counter NUMBER := 0;
v_record_limit CONSTANT NUMBER := 1000; -- You don't need this but it helps if you want to quickly change the requirement later.
BEGIN
--Iterate lines to create CSV File into CLOB variable 'v_csv_line'
FOR curr_line IN c_csv_fbdi
LOOP
v_record_counter := v_record_counter + 1;
v_csv_line := v_csv_line || curr_line.linea;
IF v_record_counter = v_record_limit -- You could just call the file creation here and not deal with a row collection
THEN
v_csv_line_rows.extend(1);
v_csv_line_rows (v_indx) := v_csv_line;
v_indx := v_indx + 1;
v_csv_line := NULL;
v_record_counter := 0;
END IF;
END LOOP;
-- Adding this to catch the last set of rows. Say you had 1200, the above would have only captured the first 1000. This gets the stranglers.
IF v_record_counter > 0
THEN
v_csv_line_rows (v_indx) := v_csv_line;
END IF;
-- I am using a new block to keep this all in one procedure, but I would break this whole thing up into differnt fncs and procedures with their own purpose like gathering the data, converting to blob and then out putting the file.
-- For now, this should give you an idea of what to do and then you can worry about organizing it better later.
DECLARE
l_counter NUMBER;
l_blob_csv_line BLOB;
l_file_name VARCHAR2 (32767);
BEGIN
l_clounter := v_csv_line_rows.FIRST;
WHILE (l_clounter IS NOT NULL)
LOOP
--Convert csv clob variable 'v_csv_line' to blob into variable 'v_blob_csv_line'
l_blob_csv_line := clob_to_blob_fn (v_csv_line_rows(l_counter));
l_file_name :=
'GlInterface_'
|| REPLACE (p_source, ' ', '_')
|| '_'
|| p_id_proceso_oic
|| '_'
|| l_clounter -- Adding the counter to file name for uniqueness.
|| '.csv';
--Zip FBDI file
as_zip.add1file (g_zipped_blob, l_file_name, l_blob_csv_line);
l_clounter := v_csv_line_rows (l_clounter).NEXT; -- When you hit the last record, this returns NULL which is what stops the loop.
END LOOP;
END;
--Close zip file
as_zip.finish_zip (g_zipped_blob);
--Insert csv file
INSERT INTO journals_fbdi (file_name,
file_content,
id_proceso_oic,
file_name_cv027,
source)
VALUES (
'FBDI_'
|| REPLACE (p_source, ' ', '_')
|| '_'
|| p_id_proceso_oic
|| '.zip',
g_zipped_blob,
p_id_proceso_oic,
p_file_reference,
p_source);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
out_result := 'ERROR: ' || SQLERRM;
out_message := DBMS_UTILITY.format_error_backtrace;
END;