If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Changing column datatype

Hi all,

In english:
I want to copy a table, and then update the datatype and contents of a set of columns from that table from (for example's sake) NUMBER to VARCHAR, with some simple manipulation of that data.

Now, the above is all well and good, and executes without error. But the above works on only one column (firstFieldName), whereas I want to somehow have this run on a whole set of column names.
In the above example, I could simply copy the code and replace "firstFieldName" with "secondFieldName", but my real-world case has a large number of field names, and I want to simply loop over them and perform the same operation.
In a best-case-scenario, I would like to simply add something like the following to my script:

From there, I would like to loop through each of the records of the tempFieldNames table, and run the second and third step of my above code, simply replacing "firstFieldName" with the current value of f at that iteration.

Is this a simple task? What's the next step for me to wrap my code so far in a loop that somehow gets a fieldname from a (changing) variable.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Thank you for your reply. I'm afraid you may have missed a large part of my problem however.
In my simple example, I only transformed a single column from T1 to T2. In my real world example, I will actually have many, many, many columns. I am trying to avoid typing out the same chunk of code (that does the transformation) for each one of these columns (with only the column name changed in each location where the column in question is referenced)

Also, in the simple example, the transformation was indeed quite simple. In my real world example, it is actually a change from a generic (nested array) representation of a sequence of numbers to an SDO_GEOMETRY point with the same internal data. To fill in the section labelled "DoYourDataTransformationHere" would actually be a large and unwieldy chunk of plain SQL.
I have actually already written (in reasonably clean PLSQL) the data transformation (and data insertion into T2) for the first column that I would like to transform. I am considering simply copy/pasting this chunk of PLSQL once for each of my columns, and using a text-replacement in each chunk to replace my first column name with one of the new column names.
Certainly not the cleanest solution, but I guess it would work.

well you should post the full story in your questions then, how are people supposed to help if they dont have the full picture

Thanks for the suggestion. For clarity, I provided a simple example with executable code. Hopefully you notice that my original question hasn't actually changed despite describing the real world application in more detail. I just didn't want to bombard any potential helpers with too many less-relevant details.

If I could write a function like above that takes (as a parameter) the fieldName that it modifies, then I could simply call this function once per field that I want to modify. The reason that I would like to functionify this process is that in my real world example, I have many, many fields (all with a different name) that I'd like to modify.
I'm slowly beginning to think that what I'm trying to do is not possible (or at least not desirable for some reason), but I'd be very happy to be proven wrong.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

You, sir, have been extremely helpful. That's exactly what I was trying to do, but didn't know how to go about doing. A few of the calls (SPO, and some of the SET params) are new to me, but with a little research into those I will be able to understand (and therefore use effectively) this technique.