Wednesday, June 24, 2009

Convert all column data type from VARCHAR2 to NVARCHAR2 and vice versa in Oracle 11g

I found a problem about the encoding and some data size issues. So I need to convert all column in all table in my database from VARCHAR2 to NVARCHAR2 with the same size. Definitely I can convert the data type column by column manually but it takes time and not a smart way. So this is the way.

I logged in as SYS and run this sql statement.

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MODIFY('||COLUMN_NAME||' NVARCHAR2('||DATA_LENGTH||'));' AS GEN_SQL_STATEMENTfrom   SYS.DBA_tab_columnswhere  owner = 'ownername' and DATA_TYPE = 'VARCHAR2';

The result is the rows of ALTER statement filled with the table name, column name and new data type.
Then I copied all rows and concatenate them with some editor. and then run them as a sql script.

If you want to convert from NVARCHAR2 to VARCHAR2, just switch 'VARCHAR2' and NVARCHAR2' in above sql statement. You can modify above script as well to suit your propose.

Finally, you cannot convert the column which is not empty otherwise the error will occur.

Tuesday, June 2, 2009

IE6 crashes on JQuery append() function

This is a problem only on IE6 as I know. (IE7 and Firefox3 are tested.) This is my JQuery code that causes IE6 crashed.

var table = $("#contact_list");table.empty();$("group",data).each(function(){var group_name = $(this).attr("name");table.append("<tr><td>"+group_name+"</td></tr>");$("user",$(this)).each(function(){var username = $(this).text();table.append("<tr><td>"+username+"</td></tr>");});});

When I saw IE6 crashed, I tried to figure out what was the problem and found it was an append function. So I tried to implement the code in many ways and I saw something.

It will crashes if the html string as an argument of the function is not a properly dom object or closed tag. For example

var box = $("#contact_list");box.append('<table>');//do'</table>');

So I solved this problem by define an variable for collecting all the html string which I want to append and append it by append() in the end. And the code works fine on IE6.