Search code examples
oracle-databaseplsqltypesprocedure

Creating a table with procedure parameter values PLSQL

My task is to transfer a list of values ​​​​from excel to Oracle and create a table based on them. As I see the solution: I need a procedure with input parameters - comma-separated values. For example: parameter1 := 'aaa','bbb','ccc' etc. no more than 100 values. and a second parameter with the same number of values ​​parameter2 := 'xxx','yyy','jjj'... As a result, there should be a table in Oracle with two columns and values ​​assigned to the input parameters. I know you can create a type table, something like this:

CREATE TABLE Tab1 (col VARCHAR2(3));
 
CREATE TYPE t_Tab1 IS TABLE OF VARCHAR2(3) ; 
 
DECLARE 
 
v_tbl t_Tab1 := t_Tab1 ('aaa','bbb','ccc');
 
BEGIN
  
INSERT INTO Tab1
SELECT VALUE (t)
FROM TABLE (v_tbl) t;
 
COMMIT;
END;

But I can't figure out how to add a second column and accept values ​​from procedure parameters. Perhaps there is a more convenient option, thanks for the help.

Solution

    1. Create the table with as many columns as you need (i,e., matching the Excel). Depending on the data quality, you may create all columns as VARCHAR2(4000), even it looks like a date or number. Most Excel files have non-numeric data in date or number columns

    2. If there are few rows (say < 10000), you can use Excel formula to generate insert statements, something like ="INSERT INTO MYTABLE VALUES ('" & B1 & "', '" & B2 & ... & "');"

    3. If there are many rows (say hundreds of thousand), you can save as CSV and use SQL Loader to load the data. Since Oracle 12c there is an express mode (12.2 doc) which makes this an easy task, the command is something like

      sqlldr hr TABLE=mytable CSV=WITH_EMBEDDED DATA=mydata.csv

    It is very likely that some rows fail for step 2 or 3. Therefore, be prepared for the need to truncate the table and fine tune.