How can I write procedures that I can loop over to insert a series of files into my database?

Question

My goal:

Insert a series of csv files into my database by creating procedures for each individual table and then looping over them. My csv files will all be named very similar to this:

1_to_be_inserted_into_table_1
1_to_be_inserted_into_table_2
1_to_be_inserted_into_table_3
1_to_be_inserted_into_table_4
2_to_be_inserted_into_table_1
2_to_be_inserted_into_table_2
2_to_be_inserted_into_table_3
2_to_be_inserted_into_table_4
3_to_be_inserted_into_table_1
3_to_be_inserted_into_table_2
3_to_be_inserted_into_table_3
3_to_be_inserted_into_table_4

This is the pseudocode for the final loop where I'd like to reference all of my procedures:

CREATE OR REPLACE DIRECTORY all_the_data AS 'D:\Favorites\1. Programming\Projects\LYS_database\DATA TO INPUT';
DECLARE @file_selector INT
SET @file_selector=1
BEGIN
    FOR files IN all_the_data LOOP
    EXEC procedure_1 ((file_selector || 'to_be_inserted_into_table_1'|| '.csv')),
    EXEC procedure_2 ((file_selector || 'to_be_inserted_into_table_2'|| '.csv')),
    EXEC procedure_3 ((file_selector || 'to_be_inserted_into_table_3'|| '.csv')),
    EXEC procedure_4 ((file_selector || 'to_be_inserted_into_table_4'|| '.csv')),

SET @file_selector= file_selector+1
commit;


END;
/

QUESTION 1: What am I doing wrong with creating the procedure below? It worked perfectly fine to insert data into a table before I tried to make it a procedure.


CREATE OR REPLACE PROCEDURE INSERT_CPP 
    (file_name IN varchar2)
IS
    cpp_data VARCHAR(200) := 'D:\Favorites\1. Programming\Projects\LYS_database';
BEGIN
    insert into cpp
    SELECT * FROM EXTERNAL (
        (
      cpp VARCHAR2 (50),
      rfu1 NUMBER (6, 2),
      rfu2 NUMBER (6, 2),
      mean_rfu NUMBER (6, 2),
      charge_ph7_4 NUMBER (2),
      hydropathy NUMBER (3, 1))
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY (&quot; || cpp_data || &quot;)
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        badfile (' || cpp_data || '\badflie_cpp.bad')
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION (file_name)
    REJECT LIMIT UNLIMITED) ext
    where not exists (
        select * from cpp c
        where c.cpp = ext.cpp );

END;
/

I get an error:

5/5       PL/SQL: SQL Statement ignored
16/27     PL/SQL: ORA-00922: missing or invalid option
30/1      PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ;

QUESTION 2. Is there a way to write a FOR files IN all_the_data LOOP in SQL? I tried this solution but the code from the first step wasn't recognized as a command. I did

EXEC sp_configure 'show advanced options', 1
RECONFIGURE

and got

RECONFIGURE
Error report -
Unknown Command

QUESTION 3. Can i write commit; at the end of every loop so that if something goes wrong on the very last file it doesn't rollback everything? Will that work?

How can I write procedures that I can loop over to insert a series of files into my database?

0 Answers0