This content has been marked as final. Show 6 replies
ALTER TABLE SRC_TABLE MODIFY ( LONG_COLUMN CLOB );
Edited by: Oleg Gorskin on 17.01.2010 6:53
INSERT INTO TRG_TABLE (....) SELECT ...., TO_CLOB(LONG_COLUMN) FROM SRC_TABLE;
assuming you want to convert data always from source_table to target_table knowing the column names, let's say:
You can just copy the data using SQL:
SQL> CREATE TABLE source_table(col1 VARCHAR2(10), col2 LONG); Table created. SQL> CREATE TABLE target_table(col1 VARCHAR2(10), col2 CLOB); Table created.
Or, as a procedure:
INSERT INTO target_table SELECT col1, TO_LOB(col2) FROM source_table;
Hope it helps.
CREATE OR REPLACE PROCEDURE migrate_to_clob IS BEGIN EXECUTE IMMEDIATE 'INSERT INTO target_table SELECT col1, TO_LOB(col2) FROM source_table'; END;
I don't think you can just ALTER a LONG column into a CLOB like your example #1 seems to indicate. In my experience you can hardly do anything at all with a LONG. :)
[Migrating Table Columns from LONGs to LOBs|http://www-css.fnal.gov/dsg/external/oracle_dcm/v10.1.0.2/doc/www/DOC/appdev.101/b10796/adlob_lo.htm]
I've noticed you keep posting the same question in new threads.
Like over here: procedure to parser long datatypes
Now, I thought I helped you out, but it doesn't seem to be the case?
You now know about TO_LOB etc., you can do it relatively easy in SQL and you could put that in a PL/SQL procedure (using dynamic SQL), so:
Please explain in detail what's (still) giving you trouble.
And, just like your other question:
provide us some sample data, preferrably using CREATE TABLE and INSERT statements.
I took it from here [Migrating Existing Tables from LONG to LOB|http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl08lon.htm#121550]