Re: calling dbms_redefinition via a stored procedure.

From: <djspira_at_hotmail.com>
Date: 11 Apr 2006 01:35:49 -0700
Message-ID: <1144744549.882932.160960@g10g2000cwb.googlegroups.com>

Hi,

Thanks for the reply. I'm just asking for anyone who has had experience of migrating tables to Unicode, who has primary keys that they need to convert the column type. This isn't an issue for me in Oracle 10g but it is in Oracle 9iR2.

No need to recreate the tables. Just looking for some pointers in the right direction. New to this area as I sure everyone once was as well.

If I'm posting to the wrong newsgroup then please let me know.

I have a stored procedure which is a wrapper to the dbms_redefinition functions.
eg.
Exec Esp_Update('tableA', 'tableA_temp', 'to_number(col1) col1, to_nchar(col2) col2, to_nclob(col3) col3');

CREATE OR REPLACE PROCEDURE ESP_UPDATE ( p_Current IN NVARCHAR2,

                                               p_New  IN NVARCHAR2,
                                               p_ColumnDefinition IN

NVARCHAR2) AUTHID CURRENT_USER
AS
BEGIN
  dbms_redefinition.start_redef_table(
    user,
    p_Current ,
     p_New,
    p_ColumnDefinition);

  dbms_redefinition.sync_interim_table(
    user,
    p_Current,
    p_New);

  dbms_redefinition.finish_redef_table(
    user,
    p_Current,
    p_New);
END ESP_UPDATE
/

The stored procedure gets called externally from a Win32 appplication.

The error I get when if try and convert a primary key column VARCHAR2->NVARCHAR2 is an ORA-12016:materialized view does not include all the primary key columns

If I don't convert this specific column I don't get an error (leaving the key in)

If I remove the primary key I get an error 0RA-12089: cannot online redefine table "RICH.TABLEA" with no primary key.

I was wondering if I should create a temporary column and make this the primary key (remove the others) and readd them after the data has been migrated.

Kind Regards

Rich Received on Tue Apr 11 2006 - 03:35:49 CDT