[Last Call] Learn how to a build a cloud-first strategyRegister Now


PL/SQL to find column names from given table

Posted on 2004-11-10
Medium Priority
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
  • 4
  • 2
  • 2
  • +1

Author Comment

ID: 12546736
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 78

Expert Comment

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

Expert Comment

ID: 12546908
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 12546945
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 78

Expert Comment

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

Author Comment

ID: 12547073
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.

Expert Comment

ID: 12549196
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>'

Accepted Solution

alexfrl earned 2000 total points
ID: 12563978
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 ?


Author Comment

ID: 12565642
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.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

830 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