Link to home
Start Free TrialLog in
Avatar of Intern
Intern

asked on

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
Avatar of Intern
Intern

ASKER

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
Avatar of slightwv (䄆 Netminder)
Column names/data types/ etc... can be found in: user_tab_coumns
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
Avatar of Intern

ASKER

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?
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.....
Avatar of Intern

ASKER

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.
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>'
ASKER CERTIFIED SOLUTION
Avatar of alexfrl
alexfrl

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Intern

ASKER

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.