INSERT user entered criteria into table

Question

I have the need to take as input a string of numbers separated by a comma, these numbers are then to be Inserted into an Oracle table along with some other information.

I have the following code to take the string and separate it into it's separate numbers, and can then do an INSERT but can not figure out how to loop around the required number of times to insert each of the entered numbers into separate rows in the Oracle table.

Can anyone help please?

    DECLARE
   stores   VARCHAR2 (1000) := '&3';
   cnt number;
BEGIN

select count(*) into cnt from (
SELECT REGEXP_SUBSTR (stores,
                                      '[^,]+',
                                      1,
                                      LEVEL)
                  FROM DUAL
            CONNECT BY REGEXP_SUBSTR (stores,
                                      '[^,]+',
                                      1,
                                      LEVEL)
                          IS NOT NULL);
                       DBMS_OUTPUT.put_line ('Stores in list         : ' || cnt);


  END;
/

MT0 · Answer 1 · 2017-09-14T14:22:43.840

1

Since you are inputting numbers via a substitution variable you could do:

Oracle Setup

CREATE TABLE your_table ( your_column_name NUMBER );

PL/SQL Block:

DECLARE
  stores SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST( &3 );
BEGIN
  FORALL i IN 1 .. stores.COUNT
    INSERT INTO your_table ( your_column_name ) VALUES ( stores(i) );
END;
/

Run and enter the value 1,3,5,7.

Output:

SELECT * FROM your_table;

Outputs:

YOUR_COLUMN_NAME
----------------
               1
               3
               5
               7

If you are taking input as a string from a bind variable then you could use this function to split the string.

edited Sep 14 '17 at 14:22

answered Apr 13 '17 at 12:49

MT0

143,790
11
59
117

Sorry about this, new here. My table is using varchar to store the stores parameter and this can not be changed. – Dean Apr 13 '17 at 16:57
Nice idea though :) – William Robertson Apr 13 '17 at 17:24

score 0 · Answer 2 · answered Apr 13 '17 at 12:31

0

The structure would be along the lines of:

declare
    stores varchar2(1000) := '&3';
begin
    for r in (
        select regexp_substr(stores, '[^,]+', 1, level) as store
        from   dual
        connect by regexp_substr(stores, '[^,]+', 1, level) is not null
    )
    loop
        dbms_output.put_line(r.store);  -- Add your INSERT etc here
    end loop;
end;
/

answered Apr 13 '17 at 12:31

William Robertson

15,273
4
38
44

1

William, your code has "as store", is this a new variable? should it be declared? – Dean Apr 13 '17 at 17:13
A [cursor FOR loop](https://docs.oracle.com/database/121/LNPLS/cursor_for_loop_statement.htm) implicitly defines a record based on its cursor, so record `r` will have a field for every column in the query. In this case there is just one column, `store`. – William Robertson Apr 13 '17 at 17:23
Thank you, I didn't see that I needed to use the r, last night but have seen it this morning and now have fully working code that I can tart up and give to my users. Thanks again. – Dean Apr 14 '17 at 09:19
I always use `r` for cursor loop records (unless there is some good reason not to, such as a loop within a loop, which is pretty unusual). It kind of goes with the traditional `i` for numeric loops. – William Robertson Apr 14 '17 at 09:52

INSERT user entered criteria into table

2 Answers2