Dynamic Column Names in Pl/SQL 1

[mackey333]

Hello -

I am trying to get some data into a relational form and I have run into a little problem. I have a table in the database with columns foo_1, bar_1, foobar_1, foo_2, bar_2, foobar_2 etc.. all the way out to 150. In PL/SQL, I would like to do something like:

for i in 1..150 loop
if my_cursor.foo_(i) is not null then
--put foo, bar, and foobar into a relational table
end if;
end loop;

is it possible to dynamically come up with column names like this?

-Greg :-Q

 

[LKBrwnDBA]

Yes, with dynamic SQL.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jimirvine]

and foobar into a relational table

A relational table is ... a table. A relation is a mathematical construct of rows and columns i.e. a table. Therefore a relational table would be ... a table. I'm assuming that what you are actually doing is trying to normalize your table structure. Personally, I would suggest that you use the construct that you have already alluded to, to simply build the appropriate string, output tht string either to file or screen, then copy, paste and run.

 

[SantaMufasa]

Mackey,

If you are not familiar with the concept of dynamic SQL and how to apply it in your case, you are welcome to post a sample of both the original column names (not all 150 necessary...only a couple of representative names) and the target column names, along with any identifying "code" column(s) that you will use to identify which of the 150 columns the newly normalized table will be using. We can then post some "looping" code to normalize the data using dynamic SQL.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[mackey333]

So maybe a "relational table" was not the word I was looking for but I think everyone got the point I was trying to make. I am attempting to update an old table structure which someone created directly from a flat file at some point in the past. Keeping the current setup is out of the question as it is bulky and unwieldy to work with it. Currently the setup is like this....

raw_data.provider_id (primary key)
raw_data.license_number_1
raw_data.license_state_1
raw_date.primary_license_switch_1
......
raw_data.license_number_150
raw_data.license_state_150
raw_date.primary_license_switch_150

along with a massive amount of other information which I have already split out into different tables and connected them with primary/foreign keys.

So what I would like to do is put all the record sets where raw_data.license_number_xxx is not null into a table like this (I should note that an entity only requires 1 license but in the current system it is not necessarily the first entry, so they will all have to be checked.):

create table licenses (
license_id number primary key not null, (from a sequence)
provider_id number(10) references providers,
license_number number,
license_state char(2),
primary_license_switch char(1)
);

Thanks for your help!

-Greg :-Q

 

[Dagon]

I would probably take the approach of declaring a cursor along the lines of:

cursor c_licenses is
    select license_number_1, ... as license_number
      from input_licenses
    where license_number_1 is not null
   union all
   select license_number_2, ... as license_number
      from input_licenses
    where license_number_2 is not null
    ...
    union all
    select license_number_150, ... as license_number
      from input_licenses
    where license_number_150 is not null);

That effectively normalizes the table in one hit, converting the columns into rows and all your PL/SQL thereafter will work as if the table was in the format you wanted. The statement to create the UNION ALL could easily be generated using a "sql from sql" technique.

Another approach might be to write a procedure which creates a dynamic cursor selecting the appropriate column number e.g.

create or replace procedure process_license_column(col_no number) is
type t_curs is ref cursor;
v_curs t_curs;
begin
   open t_curs for 'select license_id_'||col_no||' from input_licenses';
   fetch v_curs into...

end;

You could then just call:

process_license(1);
process_license(2); ..etc

 

[mackey333]

Thanks for the advice, there should be enough info there to get me going in the right direction. I'll report back if I run into any problems.

-Greg :-Q