1

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 (" || cpp_data || ")
        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?

ellie-lumen
  • 283
  • 3
  • 12
  • What kind of database do you use ? I see a odd mix between Oracle (the errors are Oracle PL/SQL) and Microsoft Sql Server syntax. – Albert Godfrind Aug 08 '20 at 05:07
  • You list 12 CSV files, and your pseudocode calls 4 procedures for each one (so you will make 48 calls in total) - is that right? Also, is the list of filenames fixed, or will it vary? If it varies, there are several techniques for getting a list of filenames in an OS directory. – William Robertson Aug 08 '20 at 13:05
  • 1
    As external table syntax can use a list of filenames, do you need to loop at all? – William Robertson Aug 08 '20 at 13:07
  • I think the main issues are at `DEFAULT DIRECTORY (" || cpp_data || ")` and `badfile (' || cpp_data || '\badflie_cpp.bad')`. If you look at [this example](https://oracle-base.com/articles/18c/inline-external-tables-18c), it uses the directory logical name (e.g. `all_the_data`) directly. – William Robertson Aug 08 '20 at 13:12
  • Q2: The link showing use of `EXEC sp_configure 'show advanced options', 1` is for Microsoft SQL Server. There is no equivalent in Oracle. Q3: Yes, you can commit in PL/SQL. – William Robertson Aug 08 '20 at 13:48
  • @Albert I use Oracle. Yes, it occured to me that I was trying to use instructions for T-SQL. Further research has revealed that you can't get a list of file names in PL/SQL. I think it would be easier to just merge all the csv files that are meant to go into the same table using python and then just insert one file per table. That way i wouldn't need to loop through the files. It wouldn't be that much work to just run a statement per table manually. – ellie-lumen Aug 08 '20 at 15:34
  • @ William the list of filenames will be fixed once i settle on their names.I listed 12 just for an example. IRL I will have 40 excel sheets, and each excel sheet will get turned into 5 csv files that will go into 5 tables. So that would be 200 calls lol. I think it would be easier to just combine all 40 csv files that are meant to go into table 1 into ONE csv file. I'll post an update of my solution so that this question isn't useless. But can you please tell me how i can get a list of files using PL/SQL? I thought that was impossible. – ellie-lumen Aug 08 '20 at 15:38
  • How about this? You create a script on the server that lists the files, and you use it in the `preprocessor` clause of an external table. https://oraclefrontovik.com/2013/08/06/listing-files-from-a-linux-directory-from-within-oracle-sql-or-plsql-in-11g/ – William Robertson Aug 08 '20 at 18:03
  • @WilliamRobertson - the OP probably needs a Windows version. https://blogs.oracle.com/oraclemagazine/preprocess-external-tables – Michael Kutz Aug 08 '20 at 18:38
  • Ellie-lumen - You are aware of the various Table Functions that can read Excel files directly. Right? – Michael Kutz Aug 08 '20 at 18:42
  • Windows version: http://www.oracle-developer.net/display.php?id=513 – William Robertson Aug 08 '20 at 18:49

0 Answers0