General import text file procedure!

Posted on 2009-02-16
Last Modified: 2013-12-07
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!!!
Question by:vfafou
    LVL 20

    Accepted Solution

    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,  ....)
      v_statement varchar2(4000);
        -- 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;'


    Author Closing Comment

    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.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now