plsql parsing the output from text file and insert into table

Question

I'm trying to parse the text from the external file and insert it into the table. After getting the string into the variable v I'm trying to split the string using dual but not sure how to do that ?

what are various alternatives that i can follow to solve this and best way to achieve this ?

Below I tried but giving the error as "wrong number or types of arguments in call to 'PUT_LINE'", i understand that putline cannot display the row type then the question is what should i declare the variable declaration for capturing the output from the dual statement ?

Also my question is as dual on describing showing as varchar2(1), then how should it would be hold into some varchar ?

please explain ?

set serveroutput on;
create or replace directory USER_DIR as 'e:\projects\sql';
declare
  v varchar2(200);
  f utl_file.file_type;
  element varchar2(200);
begin
  f := utl_file.fopen('USER_DIR','test.txt','R');
  if utl_file.is_open(f) then 
    loop
      begin
        utl_file.get_line(f,v);
        dbms_output.put_line(v);
        for element in (select regexp_substr(v,'[^\t]+',1,level)  from dual connect by regexp_substr(v,'[^\t]+',1,level) is not null)
          loop
            begin
              dbms_output.put_line(element);
            end;
          end loop;
      exception when no_data_found then exit;
      end;
      end loop;
  end if;
  utl_file.fclose(f);
end;
/
set serveroutput off;

Is this an assignment? If not, have you considered using an external table instead? — Alex Poole
– Alex Poole, Commented May 17, 2016 at 18:02

Alex Poole · Accepted Answer · 2016-05-17 18:15:10Z

You need to give the generated expression a column alias, and then refer to that name as a record field:

    for elements in (select regexp_substr(v,'[^\t]+',1,level) as element
        from dual connect by regexp_substr(v,'[^\t]+',1,level) is not null)
      loop
        begin
          dbms_output.put_line(elements.element);
        end;
      end loop;

The element variable you are explicitly declaring is not used; the loop variable name overrides it.

The dual table has a single 1-char column called dummy, and always has exactly one row. You aren't using that column so its size doesn't matter. You're selecting an expression from the single row, so that limits how many results you get.

This construct could use any single-row table, it's just traditional to use dual because it's always there as a known quantity, and the optimizer also knows it can he handled specially so it can be more efficient than using your own table.

Collectives™ on Stack Overflow

plsql parsing the output from text file and insert into table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related