Changing column datatype
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Changing column datatype

  1. #1
    Join Date
    Aug 2009
    Posts
    5

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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!
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

  3. #3
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by PAVB View Post
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well you should post the full story in your questions then, how are people supposed to help if they dont have the full picture

  5. #5
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by davey23uk View Post
    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.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,469

    Cool Function...ality?

    Quote Originally Posted by svendersar View Post
    ... 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...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by LKBrwn_DBA View Post
    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.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by svendersar View Post
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

  9. #9
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,469

    Cool A picture is worth 1K words...

    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
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  10. #10
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by LKBrwn_DBA View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width