Home » SQL & PL/SQL » SQL & PL/SQL » Convert CLOB to VARCHAR2 in MV (Oracle 9.2, Unix O/s)
Convert CLOB to VARCHAR2 in MV [message #304959] Fri, 07 March 2008 05:52 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Referential integrity between 2 tables in different schema
Next Topic: SQL QUERY - URGENT please help
Goto Forum:
  


Current Time: Sun Dec 11 00:15:56 CST 2016

Total time taken to generate the page: 0.05572 seconds