PL/SQL to find column names from given table

I will be writing multiple ( > 100) import programs for all the tables in the database.  We are going from a system that uses text files as the database, and we are going to be using those text files to create a external table in Oracle.  Then we will run my import program to scrub the data, so that it fits the new constraints, and move the data to an normal table in the database.

I already have a template for the import routine, but it will be a major pain to write all the select, update, and insert statements to put into these programs.  What I would like to do is write a helper function that I pass the tablename to, and that function will then find all the column names in the table, and spit out a select statement, insert and update.  I would also like to create variable declarations with this function to, it should be easy since my naming convention for these is

vCOLUMNNAME     TABLENAME.COLUMNNAME%TYPE;

Anyway, I hope my explanation kind of tells you what I need to do.  I just need help finding all the column names from a table and then someway to manipulate then.  I was thinking of dumping the output in a large string into a TEMP table so that I can copy and paste into my template routine.


ANY help would be appreciated, especially a SQL statement to find all the column names in a table.  Thanks
LVL 1
InternAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

InternAuthor Commented:
One of the main reasons I ask this is that it will get VERY tedious and time consuming writing the select statements and updates for some of our tables that have over 40 columns
0
slightwv (䄆 Netminder) Commented:
Column names/data types/ etc... can be found in: user_tab_coumns
0
konektorCommented:
u can define variable as :

vRow your_table.rowtype;

u can access variables as vRow.your_tab_column_name1, ... then.

or see list of columns in dba_tab_columns, all_tab_columns, user_tab_columns system views
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

InternAuthor Commented:
I think this will give me a place to start.  What I am going to start doing is selecting the column names into a cursor and loop through each name.  During each instance of the loop then I will add the name into multiple strings.  Each string will be formatted for sepearte operations (i.e. select, insert, update, etc).  Then at the end of the process I will insert these strings into a table so that I can copy and paste them into my program.

Does this sound like the best way to do this?
0
slightwv (䄆 Netminder) Commented:
I'm not sure I fully understand the complete requirements so I can't comment on the 'best' way.....

It seems like a lot of work to just get data into the DB.  Depending on your level of 'scrubbing' that needs done, I'm wondering if the same thing can be acheived through SQL*Loader.....
0
InternAuthor Commented:
Actually that can not be used, since our old system is 13 years old and will accept no connections from SQL Loader.  SQL Loader can not deal with the kinds of files that we are dealing with.  We have to ftp the files from the current server and then get them into the Oracle DB.  There really is alot of Scrubbing that has to take place to get the data over to the Oracle system.  Tables are not even close to 1to1, and many fields need to be changed, and checked against certain constraints that I think can only be handled in PL/SQL.
0
alexfrlCommented:
Use the view "all_tab_columns"

select table_name,owner,column_name from all_tab_columns
where [owner='<your schema>' (optional)]
    and table_name = '<your table name>'
0
alexfrlCommented:
To help you with the procedure i would like to ask you answer me to the following questions:

a) Are you going to use the same "external" table data as data origin for each one of your "regular" tables?

If so:

b) How would you know what command to execute - "update, delete or insert" - for each transferred row?

c) How would you define the order of columns for each table? - you should be corellated to the order in your "external" table

    for instance:

   1)you have a table of your regular table names with a number of primary key columns: totally 2 simple fields !
        create table <your table names table>(table_name varchar2(50), number_of_key_columns number)

   2)you define your external table as:
        create table <ext table>(table_name,column1,column2,...,columnN)
where N is a maximal number of one table columns among your "regular" tables.

   3) you build your insert statement for some regular table "tableK" with 3 primary key columns
by selecting its column names and attributing to column1 till columnJ of <ext table> like this:

    insert into <tableK>
      (
         <column1>, -- 1st 3 columns are for primary key
         ...
         <columnJ>
      )
    select column1,
             ...
             columnJ
       from <ext table>
     where table_name = '<tableK>'
         and not exists
            ( select 1
                 from <tableK>
               where <column1> = t.column1
                   and <column2> = t.column2
                   and <column3> = t.column3
           );

   -- THERE IS NO PROBLEM TO BUILD DYNAMICALLY THIS INSERT STATEMENT or some CURSOR with row-by-row inserting

But the main question is HOW you can know to IMPORT FILE rows into your "external" table in the NECESSARY ORDER ?

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
InternAuthor Commented:
We are creating an external table for EVERY table in the oracle database.  This import routine will check to see if there are any rows in the external table, if not then it will do nothing.  If there are then we will import the rows, and then delete the file associated with that table.  This file will be created in our old system and then FTP'd to the Oracle server.  This will accomplish one-way synchronization.  The first step in the project is to first create reports off of the data, then later we will start on transactions and we will use a similar system to accomplish 2-way synchronization.

So every table has its own external table so dynamic SQL is not needed, we needed to do this because each table has very specific data manipulation and scrubbing needs.  My programs will reside in a set of packages and be fired off in the correct order to deal with child records, and foriegn keys in the correct way.  It is hard to describe the current system and why we have to do this, but here goes:

The current system is a Thouroughbred program running on OpenVMS, this program is what runs our current business and I am in the process of rewriting it useing Forms, Reports, Discoverer, and Portal.  These two systems will have to coexist for about 2 years until we can cut the cord on the old system.  The data in this old system does not have the constraints, and error checking that are required to stop data corruption, so all the data scrubbing and manipulation is needed.  I think the approach we have taken is the best one for the circumstance.  Anyway I just thought I would try to explain the situation a little better, I will choose a comment as the answer today.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.