• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

General import text file procedure!

Hello Gurus!!!
I need to make a procedure that will import text files to several Oracle tables. The tables have different structures but all this process will be made by one Oracle form with Oracle Form Builder 6i. My problem is that I have to pass the table names and the field names as variables within the cursor and I'm very confused with these processes. Is there any idea to achieve this procedure making? It's pleasantly welcomed a template of all this procedure In order to do it as quickly as possible!
Thank you in advance!!!
0
vfafou
Asked:
vfafou
1 Solution
 
flow01Commented:
When the structures are different the problem wil be on passing the data.
You will  have to use dynamic build sql/pl-sql and can do so with  the FORMS_DDL build-in in your form or
with DBMS_SQL or  the 'execute immediate statement' on your server . What version of the oracle-database are you using ?

as an idea:

procedure insert_dyn_row(p_tabel  varchar2, p_varlist varchar2, p_nr_of_var number, p_var1 varchar2, p_var2 varchar2,  ....)
is
  v_statement varchar2(4000);
begin
    -- build up a list of column values in the statement
   for i1 in 1 .. p_nr_of_var loop
      if      i1 = 1 then v_statement := p_var1;
      elsif i1 = 2 then v_statement := v_statement || ' , ' || p_var2;
      ...
     end if;
  end loop;
  -- create the insert statement
  v_statement :=  'insert into ' || p_table ||  '(' || p_varlist || ') values (' || v_statement || ');'
  -- execute the statement
  FORMS_DDL('BEGIN '  || v_statement || 'END;'

END;
 
 
0
 
vfafouAuthor Commented:
Thank you very much flow01! I solved my problem with your solution! I'm sorry for my late but I have had a health problem.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now