Convert CLOB to VARCHAR2 in MV [message #304959] |
Fri, 07 March 2008 05:52  |
amit.pandey
Messages: 64 Registered: August 2006 Location: Bangalore, India
|
Member |
|
|
Hi All,
I've to create a MV, the source table is on remote site, also In MV I've to convert a CLOB column exist in source table to varchar2(4000).The MV refresh method must be fast as it is a huge table.
For Local Site when I give this command it fails to access LOB values from remote site. I got ORA-22992
Create Materialized View Log
WITH ROWID;
create materialized view INST_PROD
BUILD IMMEDIATE
REFRESH ON COMMIT
START WITH SYSDATE NEXT SYSDATE+1
WITH ROWID
AS
SELECT update_id, substr(detail,1,4000) detail, prod_name from INS.INST_PROD@INSDB
I've created a view on source table, here I convert CLOB to Varchar2(4000), then when I tried to create a MV log at local site on that view, It throws an error as I'm try to create MV LOG on a view it fails.
[QUOTE]At remote site[/QUOTE]
create or replace view INST_PROD_VIEW
AS select update_id, substr(detail,1,4000) detail, prod_name from INST_PROD;
[QUOTE]At Local Site[/QUOTE]
SQL> create materialized view log on INST_PROD_VIEW@INSDB with rowid;
create materialized view log on INST_PROD_VIEW@INSDB with rowid
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> DESC INST_PROD_VIEW@INSDB
Name Null? Type
----------------------------------------- ------- -------------
UPDATE_ID NUMBER(4)
DETAIL VARCHAR2(4000)
PROD_NAME VARCHAR2(200)
Create an MV on a view
If any body is having any solution, kindly let me know,
Many Thanks
Amit
[Updated on: Fri, 07 March 2008 06:08] Report message to a moderator
|
|
|
Re: Convert CLOB to VARCHAR2 in MV [message #304963 is a reply to message #304959] |
Fri, 07 March 2008 06:02  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
ORA-22992: cannot use LOB locators selected from remote tables
*Cause: A remote LOB column cannot be referenced.
*Action: Remove references to LOBs in remote tables.
Create a local MV with the same definition of the current one and then snapshot this local MV in your remote site.
Regards
Michel
|
|
|