Changing column datatype
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.
Create dummy data (T1), and copy to new table (T2)
Clear data and alter the datatype of "firstFieldName"
CREATE TABLE T1(
INSERT INTO T1 VALUES(100, 1, 3);
INSERT INTO T1 VALUES(101, 2, 4);
CREATE TABLE T2 AS SELECT * FROM T1;
Loop through each record and transfer the (new form of) firstFieldName from T1 to T2
SET firstFieldName = NULL;
ALTER TABLE T2
MODIFY ( firstFieldName VARCHAR2(100) );
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.
CURSOR T1Cursor IS
SELECT pkeyFieldName, firstFieldName FROM T1;
/* Get the primary key, and T1 field to transfer */
FETCH T1Cursor INTO pkeyF, T1F;
EXIT WHEN T1Cursor%NOTFOUND;
/* Simple data manipulation from table T1 to table T2 */
T2F := T1F||T1F||T1F;
/* Transfer data into T2! */
SET firstFieldName = T2F
WHERE T2.pkeyFieldName = pkeyF;
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.
CREATE TABLE tempFieldNames( f VARCHAR(100) );
INSERT INTO tempFieldNames VALUES('firstFieldName');
INSERT INTO tempFieldNames VALUES('secondFieldName');
INSERT INTO tempFieldNames VALUES('thirdFieldName');
/* ... etc ... */
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.
Any help would be great.
Click Here to Expand Forum to Full Width