Ora-12008

[aferrarese]

Hi, I have a problem. If I try to refresh a snapshot using
dynamic sql in a procedure I found this error, but if I drop and create the object it works.
What it depends for you?
Andrea.

 

[karluk]

Could you post your dynamic sql? It's hard to be certain what's happening without seeing your code. However, a guess would be that your dynamic sql is modifying your snapshot definition. It seems likely that would cause a refresh to fail, since the snapshot no longer has the same definition it had when the procedure was created.

 

[aferrarese]

Hi Karluk, thanks for the answer.
Here is the code:

CREATE OR REPLACE PROCEDURE CDWDBA.prc_refresh1 is
sql_stmt VARCHAR2(200);
begin
PKG_METADATI.prc_start_mvdds('TADDS_M_CLIENTE_PRODOTTO');
dbms_mview.refresh('TADDS_M_CLIENTE_PRODOTTO', 'C');
sql_stmt := 'alter snapshot TADDS_M_CLIENTE_PRODOTTO compile';
EXECUTE IMMEDIATE sql_stmt;
end;

This procedure finished with error 12008.

'TADDS_M_CLIENTE_PRODOTTO' is a snapshot that read from 3 materialized view:
CREATE SNAPSHOT CDWDBA.TADDS_M_CLIENTE_PRODOTTO
PCTFREE 30
PCTUSED 70
MAXTRANS 255
REFRESH COMPLETE
AS select
a.data_validita data_caricamento,
to_char(a.data_validita,'MON/YYYY') mese,
a.cliente_id,
a.prodotto_id,
a.societa_id,
a.aum,
nvl(b.raccolta,0) raccolta,
nvl(c.commissioni,0) commissioni
from vidds_aum a,
vidds_raccolta_netta b,
vidds_commissione c
where a.data_validita = b.data_validita(+)
and a.cliente_id = b.cliente_id(+)
and a.prodotto_id = b.prodotto_id(+)
and a.societa_id = b.societa_id (+)
and a.data_validita = c.data_validita(+)
and a.cliente_id = c.cliente_id(+)
and a.prodotto_id = c.prodotto_id(+)
and a.societa_id = c.societa_id (+)

What do you think?