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.

In code:
Create dummy data (T1), and copy to new table (T2)

Code:

---

CREATE TABLE T1(
    pkeyFieldName NUMBER,
    firstFieldName NUMBER,
    secondFieldName NUMBER
);
INSERT INTO T1 VALUES(100, 1, 3);
INSERT INTO T1 VALUES(101, 2, 4);
CREATE TABLE T2 AS SELECT * FROM T1;

---

Clear data and alter the datatype of "firstFieldName"

Code:

---

UPDATE T2
SET firstFieldName = NULL;
ALTER TABLE T2
MODIFY ( firstFieldName  VARCHAR2(100) );

---

Loop through each record and transfer the (new form of) firstFieldName from T1 to T2

Code:

---

DECLARE
        pkeyF T1.pkeyFieldName%TYPE;
        T1F T1.firstFieldName%TYPE;
        T2F T2.firstFieldName%TYPE;

        CURSOR T1Cursor IS
                  SELECT pkeyFieldName, firstFieldName FROM T1;

BEGIN
        OPEN T1Cursor;
        LOOP
                  /* 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! */
                UPDATE T2
                SET firstFieldName = T2F
                WHERE T2.pkeyFieldName = pkeyF;
        END LOOP;
    CLOSE T1Cursor;
END;

---

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:

Code:

---

CREATE TABLE tempFieldNames( f VARCHAR(100) );
INSERT INTO tempFieldNames VALUES('firstFieldName');
INSERT INTO tempFieldNames VALUES('secondFieldName');
INSERT INTO tempFieldNames VALUES('thirdFieldName');
/* ... etc ... */

---

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.

Any help would be great.
Thanks,
Sven.

That's too complicated.

After creating and populating table T1...

1) create empty table T2 with the datatypes you want - no CTAS
2) insert into T2 select +++DoYourDataTransformationHere+++ from T1
3) commit

You are done!

Quote:

---

Originally Posted by PAVB

That's too complicated.

After creating and populating table T1...

1) create empty table T2 with the datatypes you want - no CTAS
2) insert into T2 select +++DoYourDataTransformationHere+++ from T1
3) commit

You are done!

---

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.

Thanks,
Sven.

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

Quote:

---

Originally Posted by davey23uk

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.

Thanks,
Sven.

Quote:

---

Originally Posted by svendersar

... I am trying to avoid typing out the same chunk of code (that does the transformation) for each one of these columns
...

---

Create a FUNCTION which does the transformation...
;)

Quote:

---

Originally Posted by LKBrwn_DBA

Create a FUNCTION which does the transformation...
;)

---

Thanks for the tip, and now for the heart of my question:

How do I create a function (or procedure) like this (given in pseudo-code because I don't know how to actually do it):

Code:

---

FUNCTION updateT2Field(fieldName IN VARCHAR2, pKey IN NUMBER)
        oldVal := SELECT T1.(fieldName) FROM T1 WHERE T1.pkeyFieldName = pKey;
        newVal := oldVal||oldVal||oldVal; /* Or whatever transformation... */
        UPDATE T2 SET T2.(fieldName) = newVal WHERE T2.pkeyFieldName = pKey;
END FUNCTION

---

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.

Thanks,
Sven.

Quote:

---

Originally Posted by svendersar

If I could write a function like above that takes (as a parameter) the fieldName ...

---

You are still thinking cursors, procedural code. Don't hurt yourself.

Add the function as described by LKBrwn_DBA to my suggestion and you will end up with a compact, nice piece of code.

Here is an example script of what you can do:

Code:

---

DROP TABLE T1;
CREATE TABLE T1(
    PK NUMBER,
    F0 NUMBER,
    F1 NUMBER,
    F2 NUMBER,
    F3 NUMBER,
    F4 NUMBER,
    F5 NUMBER,
    F6 NUMBER,
    F7 NUMBER,
    F8 NUMBER,
    F9 NUMBER
);
INSERT INTO T1 VALUES(100, 1, 2, 3, 4, 5, 6, 7, 8, 9,10);
INSERT INTO T1 VALUES(101,11,12,13,14,15,16,17,18,19,20);
INSERT INTO T1 VALUES(102,21,22,23,24,25,26,27,28,29,30);
INSERT INTO T1 VALUES(103,31,32,33,34,35,36,37,38,39,40);
INSERT INTO T1 VALUES(104,41,42,43,44,45,46,47,48,49,50);
INSERT INTO T1 VALUES(105,51,52,53,54,55,56,57,58,59,60);
INSERT INTO T1 VALUES(106,61,62,63,64,65,66,67,68,69,70);
INSERT INTO T1 VALUES(107,71,72,73,74,75,76,77,78,79,80);
COMMIT;

DROP TABLE T2;
SET pages 0 trims on term off ver off feed off echo off embed on recsep off head off
SPO cr_t2.sql
SELECT  DECODE (column_id,
                1,  'CREATE TABLE T2 ('|| CHR (10)
                  || ' '|| column_name || ' NUMBER' || CHR (10),
                    ','|| column_name || ' VARCHAR2(16)' || CHR (10)
                )
    FROM user_tab_columns
  WHERE table_name = 'T1'
ORDER BY column_id;
PROMPT );
PROMPT /
SPO off
SET pages 99 term on ver on feed on echo on head on
@cr_t2

INSERT INTO T2 (PK) SELECT PK FROM T1;
COMMIT;

CREATE OR REPLACE FUNCTION T1_CHG (P_VAL NUMBER)
RETURN VARCHAR2 IS
BEGIN
  -- Your transformation here --
  RETURN p_val||','||p_val||','||p_val;
END;
/
SET pages 0 trims on term off ver off feed off echo off embed on recsep off head off
SPO up_t2.sql
SELECT 'UPDATE T2 SET  '|| column_name || ' = (SELECT T1_CHG('||column_name
    ||') FROM T1 WHERE T1.PK = T2.PK);'
    FROM user_tab_columns
  WHERE table_name = 'T1' and column_id > 1
ORDER BY column_id;
SPO off
SET pages 99 term on ver on feed on echo on head on
@up_t2

---

;)

Quote:

---

Originally Posted by LKBrwn_DBA

Here is an example script of what you can do:
;)

---

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.

Cheers,
Sven.

Perhaps you can avoid the 'UPDATE' part of the example script and do as PAVB suggested and generate INSERT statements instead!!!.

Good luck!
:p