PL/SQL to find column names from given table

Posted on 2004-11-10
Last Modified: 2010-02-08
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


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
Question by:Intern
    LVL 1

    Author Comment

    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
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Column names/data types/ etc... can be found in: user_tab_coumns
    LVL 9

    Expert Comment

    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
    LVL 1

    Author Comment

    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?
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.....
    LVL 1

    Author Comment

    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.
    LVL 5

    Expert Comment

    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>'
    LVL 5

    Accepted Solution

    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
        select column1,
           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


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

    LVL 1

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Query tuning in Batch programming. 3 32
    surrogate key in database world 6 60
    substr - oracle 5 49
    Oracle PL/SQL syntax 4 29
    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    779 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

    13 Experts available now in Live!

    Get 1:1 Help Now