Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 23, 2016 - 8:00 am UTC

Last updated: November 26, 2016 - 3:35 am UTC

Version: 12.1.2.0

Viewed 10K+ times! This question is

You Asked

I am trying to rename Oracle constraint name, the below code does not works. Please can you suggest a workaround or a correction.

DROP TABLE B;

DROP TABLE a;

CREATE TABLE a (a clob);

CREATE TABLE B (a number, CONSTRAINT PK_1 PRIMARY KEY(a));

DECLARE
v_handle NUMBER;
v_transform_handle NUMBER;
v_table_query CLOB;
p_source_schema varchar2(30) := 'MUDR_MIG355';
p_source_tablename varchar2(30) := 'B';
BEGIN
v_handle := DBMS_METADATA.OPEN(OBJECT_TYPE => 'TABLE');

DBMS_METADATA.SET_FILTER (v_handle, 'SCHEMA', p_source_schema);

DBMS_METADATA.SET_FILTER (v_handle, 'NAME', p_source_tablename);

v_transform_handle := DBMS_METADATA.ADD_TRANSFORM (v_handle, 'MODIFY');

DBMS_METADATA.SET_REMAP_PARAM (v_transform_handle, 'REMAP_NAME', 'PK_1', 'WOOW');

v_table_query := DBMS_METADATA.FETCH_CLOB (v_handle);

insert into a values (v_table_query);
commit;

END;
/


select * from a

and Connor said...

Why not just rename it ?

SQL> create table t ( x int );

Table created.

SQL> alter table t add constraint PK primary key  ( x ) ;

Table altered.

SQL> alter table t rename constraint PK to PK1;

Table altered.



Why all the dbms_metadata stuff ?


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the response!!

A reader, November 24, 2016 - 4:48 am UTC

Thanks for the response!!
We are replicating a table in the same schema and we want all the attributes to be the same, hence the issue.

One more question -

DB 1
-----

CREATE TABLE test (partition_name VARCHAR2(100));


DB 2
-----

CREATE DATABASE LINK from DB2 to DB1; (let's name ot DB2TODB1)

CREATE SYNOYM test for test@DB2TODB1);

INSERT INTO test
SELECT partition_name FROM user_partitions;

ORA-02070: database does not support in this context


Please suggest how to resolve the same. I am using cursors as of now.
Connor McDonald
November 26, 2016 - 3:35 am UTC

This is a known restriction. See MOS note 744219.1 for details.

Workaround 1:

- do it the "other way", ie the remote database does

insert into my_local_table
select ... from table@remote

Workaround 2:

- use PLSQL. Bulk collect the rows into an array, and the insert them

SQL> declare
  2    type ty is table of varchar2(100);
  3    r ty;
  4  begin
  5
  6  SELECT u.partition_name bulk collect into r FROM user_tab_partitions u;
  7
  8  for i in 1 .. r.count loop
  9    INSERT INTO test t values ( r(i));
 10  end loop;
 11
 12  end;
 13  /

PL/SQL procedure successfully completed.



More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.