Parsing string in PL/SQL and insert values into database with RESTful web service

Question

I am reading the values which I want to insert into database. I am reading them by lines. One line is something like this:

String line = "6, Ljubljana, Slovenija, 28";

Web service needs to separate values by comma and insert them into database. In PL/SQL language. How do I do that?

Where are your reading those lines from? Are they accessible all at once or they are coming from somewhere one by one? — Nick Krasnov, Jul 08 '15 at 13:04
I am reading them from ftp server and they can be accessible all at once (file by file). — Wolf, Jul 09 '15 at 05:45
If you can read an entire file, then external tables and simple `insert ... select .. from ` is the way to go, if there is no need for extra transformation of data. — Nick Krasnov, Jul 09 '15 at 06:17

score 1 · Answer 1 · answered Jul 08 '15 at 16:32

Here is some pl/sql that I have used to parse through delimited strings and then extract the individual words. You may have to mess with it a bit when using with the web service but it works fine when you are running it right in oracle.

declare
  string_line varchar2(4000);
  str_cnt number;
  parse_pos_1 number := 1;
  parse_pos_2 number;
  parsed_string varchar2(4000);
begin
  --counting the number of commas in the string so we know how many times to loop
  select regexp_count(string_line, ',') into str_cnt from dual;

  for i in 1..str_cnt + 1
  loop
      --grabbing the position of the comma
      select regexp_instr(string_line, ',', parse_pos_1) into parse_pos_2 from dual;

      --grabbing the individual words based of the comma positions using substr function
      --handling the last loop
      if i = str_cnt + 1 then
        select substr(string_line, parse_pos_1, length(string_line)+1 - parse_pos_1) into parsed_string from dual;

        execute immediate 'insert into your_table_name (your_column_name) values (' || parsed_string || ' )';
        execute immediate 'commit';
      --handles the rest
      else
        select substr(string_line, parse_pos_1, parse_pos2 - parse_pos_1) into parsed_string from dual;
        execute immediate 'insert into your_table_name (your_column_name) values (' || parsed_string || ' )';
        execute immediate 'commit';
      end if;

      parse_pos_1 := parse_pos_2+1;

   end loop;
end;

It did help me a bit, but I posted the exact answer. You will get thumb up when I get 15 rep. Thanks for the answer. — Wolf, Jul 09 '15 at 09:45

score 0 · Accepted Answer · answered Jul 09 '15 at 09:34

I found an answer to that particular question. If you have similar values to those I posted for a question, like numbers, which look something like this:

String line = "145, 899";

This string is sent via POST request (RESTful web service, APEX). Now getting the values in PL/SQL and inserting them into table looks something like this:

DECLARE
    val1 NUMBER;
    val2 NUMBER;
    str CLOB;
BEGIN
    str := string_fnc.blob_to_clob(:body);                   // we have to convert body
    val1 := TO_NUMBER(REGEXP_SUBSTR(str, '[^,]+', 1, 1));
    val2 := TO_NUMBER(REGEXP_SUBSTR(str, '[^,]+', 1, 2));
    // REGEXP_SUBSTR(source, pattern, start_position, nth_appearance)
INSERT INTO PRIMER VALUES (val1, val2);

END;

However, this is the method to insert line by line into database, so if you have large amount of rows in a file to insert, this isn't a way to do it. But here is the example which I requested. I hope it helps to someone.

Beware! The regex of the format '[^,]+' will return an unexpected value if there is a null element in the list and the element you are selecting is after that element. See here for more info: https://stackoverflow.com/questions/30192688/join-to-splitted-string-columns-in-oracle/30263317#30263317 — Gary_W, Jul 09 '15 at 14:08

Parsing string in PL/SQL and insert values into database with RESTful web service

2 Answers2